How do I import records from a spreadsheet into my address book?


This guide shows you how to populate Remote Utilities address book with records from a spreadsheet file.

Create a template address book file

You need to generate a sample XML code to get started. The easiest way to do this is to create a new address book with sample records:

  1. In Viewer go to the Manage tab and click Address Book Manager: Run address book manager
  2. Click Add: Click add
  3. Select Local and click Next: Select Local
  4. Select Create new address book file and click Finish: Create local book
  5. Select a destination folder, name the file and click Save: Save XML file
  6. Close the address book manager. Close address book manager
  7. Your new address book will get loaded in the main Viewer window. New address book
  8. Create two folders in the new address book, and two connections in one of the folders. These are sample records so you can use any names you want. The result may look like this: Create sample records
  9. Completely exit Viewer (FileExit) and proceed to the next section below.

Open the file in XML editor

Use any XML editor to open the sample address book file that you've just created. You should see the following:

Address book open in XML editor

Use the free XML code formatter to format the code for better readability:

Better formatted XML code

Tags and attributes explained

The <groups> tag encloses address book folders. Each folder is marked with the <group> tag with two attributes—id and parent_group_id. The id attribute is used as a unique identifier for each folder. The parent_group_id attribute is only used for subfolders and shows the id of the parent folder for that specific subfolder.

The <connections> tag encloses connections that represent remote computers. Each connection is marked with the <connection> tag. The <connection> tag has the parent_group_id attribute that refers to the unique ID of the folder where this connection is located.

In addition, each <connection> tag encloses tags that hold the properties of that connection:

Connection properties

You can close the file for now and proceed to the next step.

Add XML map to Excel

  1. Open Microsoft Excel, right-click on the ribbon and select Customize the ribbon: Customize ribbon
  2. Turn on the Developer tab and click OK: Turn on Developer tab
  3. Go to the Developer tab and click Source: Click Source
  4. In the XML pane click XML maps...: Click XML Maps
  5. Click Add: Click Add
  6. Open your sample address book XML file: Open XML file
  7. Click OK in the XML schema warning: XML schema warning
  8. Click OK in the XML maps window: XML maps window
  9. You will see your map added to the XML source pane: XML source pane
  10. Create two empty sheets in Excel and name them Folders and Connections: Folders and Connections sheets
  11. Switch to the Folders sheet, select the groups element in the XML Source pane and drag it to the cell A1 in that sheet. Dragging folders element
  12. The result should be as follows: Adding folder columns
  13. Similarly, switch to the Connections sheet, select the connections element in the XML Source pane and drag it to the cell A1 in that sheet. Dragging connections element
  14. The result should be as follows: Adding connection columns
  15. In the Developer tab click Refresh data: Refresh data
  16. The sheets will get populated with the folder and connections data from your sample address book: Refresh data - Folders Refresh data - Connections

Add records to spreadsheet

Now you can populate the spreadsheet with your own data.

  1. Switch to the Folders sheet and populate the table with unique IDs and folder names (captions). You can use any alphanumeric string as the folder ID. The only requirement is that it must be unique: Folders added

    Note how the parent group ID is specified for folders A1, A2 and A3. These are sub-folders of the Folder A whose unique ID is "1". Setting the parent_group_id is how you define folder hierarchy in your address book.

  2. Switch to the Connections sheet, select a sample row and copy it: Copy row
  3. Select as many empty rows below as the number of connections that you want to add to the address book: Copy rows
  4. Right-click on the selection and select Insert copied cells: Paste rows
  5. The result should be as follows: Paste result

    Why do I need to copy the sample data?

    Each connection item has about 90 different properties (represented by columns in the table). To avoid filling out the values manually you need to copy the default values across all your records first. You will be able to edit the properties later in the Viewer app, both individually and in bulk. For now, just use the default values.

  6. Make sure that you select the entire table by dragging a tiny black selector at the bottom right corner. Only the records included in the table will be exported: Paste result
  7. Clear the contents of the first five columns in the table: Clear contents
  8. Populate the columns with your connections data: Populate data

    Note how the parent_group_id points to the ID of the folder where the connection is to be located. In this example we put the first five connections to Folder A and the other four connections to folder A1 (a sub folder of folder A).

Export address book

Now let's export your data to XML format to be used as Remote Utilities address book.

  1. In the Developer tab click Export: Export button
  2. Select a destination for your address book file and click Save: Save address book
  3. Open the my_address_book.xml file in your XML editor and replace the first two rows with the respective rows from your sample_book.xml file. Save and close your address book file. Replace first rows
  4. In Viewer select the Manage tab and click Address Book Manager: Launch Address Book Manager
  5. In the address book manager click Add: Click Add
  6. Select Local and click Next: Select local
  7. Select Open existing address book file and click Finish: Open existing address book
  8. Navigate to your saved XML file and click Open. Then close the Address Book Manager. Open address book
  9. You can see that your address book has been loaded into the Viewer. The Folder A contains 5 connections: Folder A
  10. The Folder A1 contains 4 connections: Folder A1


Use the process described above to add as many records — folders and connections — to your address book as you like. You can create hierarchy and put records into folders by referencing them using the parent_group_id attribute.

Tags: Features

Was this article helpful?

This website uses cookies to improve user experience. By using this website you agree to our Terms of Service and Privacy Policy.