Knowledge Base

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: Launch Address Book Manager
  2. In the address book manager click Add: Click Add
  3. Select Local and click Next: Select local
  4. Select Create new address book file and click Finish: Create new local address book
  5. Select where you want to save the address book file and click Save: Save address book file
  6. Close the address book manager. Your new address book is loaded in the main Viewer interface: Address book in Viewer
  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: Create sample records

    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 FileExit) 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:

Address book open in XML editor

If your XML code isn't formatted properly you can use a free XML code formatter.

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:

Connection properties

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...: Select customize ribbon
  2. On the right, enable the Developer tab and click OK: Enable developer tab
  3. Navigate to the Developer tab and click Source: Click Source
  4. In the XML pane click XML maps...: Click XML maps
  5. In the resulting window, click Add..: Add XML map
  6. Navigate to your sample address book XML file and open it: Open XML sample map
  7. You will see a warning message from Excel. Click OK and close the XML maps dialog: XML schema warning
  8. You will see your map added to the XML source pane: 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: Folders and Connections sheets
  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: Dragging folders element Adding folder columns
  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: Dragging connections element Adding connection columns
  4. In the Developer tab click Refresh data: Refresh data
  5. Your spreadsheets get populated with sample data from your address book: Refresh data - Folders Refresh data - Connections
  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: Table populated

    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: Select row
  8. Select the number of rows you need for your data: Copy row
  9. Right-click on the selection and select Insert copied cells: Paste rows
  10. 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. 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: Table selector
  12. Clear the contents of the first five columns in the table: Clear contents
  13. Populate the columns with your connections data: Connection 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: 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

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.

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.