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

Overview

Remote Utilities address book allows you to keep a list of your remote Hosts/Agents and set individual connection properties for each. You can populate your address book either by manually adding new connections or by having them added automatically through the use of a self-hosted server with the "Auto-import" option enabled. Sometimes, however, you may need to quickly populate your address book with records from a spreadsheet.

Remote Utilities uses the XML format for its address books. In this sample tutorial we will show you how to create a new address book using data from a Microsoft Excel spreadsheet.

Creating 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 select the Manage tab and click Address Book Manager:
2. In the address book manager click Add:
3. Select Local and click Next:
4. Select Create new address book file and click Finish:
5. Select where you want to save the address book file and click Save:
6. Close the address book manager. Your new address book is loaded in the main Viewer interface:
7. Go ahead, select the newly created address book on the left and add two folders and two connections. You can use whatever names you wish, those are just sample items. The result may look like this:

   It is important to add at least two records for each data type (i.e. two folders and two connections). If you don’t do this, Excel won’t know that each XML element is meant to be repeated throughout the spreadsheet.

8. Fully exit the Viewer (select File → Exit) and proceed to the next step.

Open the file in an XML editor

Locate the XML file of the address book and use an XML editor to open the file. You should see the following:

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

The <connections> tag encloses connections where each connection (marked by the <connection> tag) represents a remote computer. The <connection> tag has the parent_group_id attribute that refers to the ID of the folder where this connection is located.

Each <connection> tag encloses tags that hold various properties for that connection:

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

Adding XML map to Excel

1. Open Microsoft Excel, right-click on the ribbon toolbar and select Customize the ribbon...:
2. On the right, enable the Developer tab and click OK:
3. Navigate to the Developer tab and click Source:
4. In the XML pane click XML maps...:
5. In the resulting window, click Add..:
6. Navigate to your sample address book XML file and open it:
7. You will see a warning message from Excel. Click OK and close the XML maps dialog:
8. You will see your map added to the XML source pane:

Adding records

Now you can populate the spreadsheet with your own data.

1. Create two empty sheets in Excel and name them Folders and Connections:
2. Switch to the Folders sheet, select the groups element in the XML Source pane and drag it to the cell A1 in that sheet. The result should be as follows:
3. 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. The result should be as follows:
4. In the Developer tab click Refresh data:
5. Your spreadsheets get populated with sample data from your address book:
6. Switch to the Folders sheet and populate the table with your unique IDs and names (captions) for your folders. You can replace the sample data with your own:

   Note how the parent group ID is specified for folders A1, A2 and A3. The number "1" tells the program that these folders are sub-folders of Folder A whose unique ID is "1".

   You can use any alphanumeric string as the folder/connection ID. The only requirement is that each folder/connection must have a unique ID.

7. Switch to the Connections sheet, select a sample row and copy it:
8. Select the number of rows you need for your data:
9. Right-click on the selection and select Insert copied cells:
10. The result should be as follows:

    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. You will be able to edit the properties later in the Viewer application, both individually and in bulk. For now, just use the default values.

11. Make you that you select your entire table by dragging a tiny black selector at the bottom right corner of each table. Only the records included in the table will be exported:
12. Clear the contents of the first five columns in the table:
13. Populate the columns with your connections 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).

Exporting 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:
2. Select a destination for your address book file and click Save:
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.
4. In Viewer select the Manage tab and click Address Book Manager:
5. In the address book manager click Add:
6. Select Local and click Next:
7. Select Open existing address book file and click Finish:
8. Navigate to your saved XML file and click Open. Then close the Address Book Manager.
9. You can see that your address book has been loaded into the Viewer. The Folder A contains 5 connections:
10. The Folder A1 contains 4 connections:

Conclusion

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.