Importing Data to a List

Once a new List has been defined, it is necessary to populate the List with data imported from an existing external data source.

Data can be imported in formats such as XML, CSV (Comma Separated) or TSV (Tab Separated), with AMS encoding the data as part of the import process.

To import new data simply place the XML, CSV or TSV directly into the text box, select the format and click “Import Data”.

Importing Excel Data

Data can easily be imported from an Excel Spreadsheet by copying the spreadsheet data to the clipboard and pasting into the “Import Data” text area. When using this method, select TSV (Tab Separated) as the format.

When selecting the data in the spreadsheet to import it is necessary to include the column headers. For this reason it is best to set the column headers to be the same as the Export Labels for the matching Fields in the Template used for the the List. This will allow AMS to automatically map the data in each row to the correct Field in AMS.

If this is not possible to rename the column headings, then the mapping can be performed manually as part of the next step in the import process.

See Import List Data From Excel for an example of this process.

Importing XML Data

The following presents the definition of an XML import file and a sample data file:

The format of the XML should be that of the associated list template e.g.:

<collectionExternalLabel>
<externalLabel>
<field1>value</field1>
<field2>value</field2>
...
<fieldn>value</fieldn>
</externalLabel><externalLabel>
<field1>value</field1>
<field2>value</field2>
...
<fieldn>value</fieldn>
</externalLabel>
...
<externalLabel>
<field1>value</field1>
<field2>value</field2>
...
<fieldn>value</fieldn>
</externalLabel>
</collectionExternalLabel>

A sample stock list might look like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="stock.xsd">
    <item>
        <stockCode>609730</stockCode>
        <description>Widget A</description>
        <price>2</price>
        <reorderCode>31458</reorderCode>
        <category>Widgets</category>
    </item>
    <item>
        <stockCode>551023</stockCode>
        <description>Widget A2</description>
        <price>8.89</price>
        <reorderCode>31459</reorderCode>
        <category>Widgets</category>
    </item>
        <item>
        <stockCode>2218135</stockCode>
        <description>Cog 768</description>
        <price>5.93</price>
        <reorderCode>50135</reorderCode>
        <category>Other</category>
    </item>
</items>

Importing Via Email

An alternative to importing list data via the web is to email AMS with the list changes attached in a CSV file. The following is required for the email:

1. Send to importlist@app.ams.io
2. In the body of the email include the following values in YAML format:
   • api key: <your AMS account api key>
   • list id: <the external id of the list you want to update>
   • list name: <the external name of the list you want to update - can be used instead of list id>
   • update type: <the type of change to apply, can be add, merge or replace where replace is the default>
3. Attach the new list data as a CSV file (mime type must be text/csv). The filename must end in .csv. For update type merge the items to delete must be in a file called delete.csv

The subject is not important but the from address will be used to send an update confirmation to, if the update is successful. Plain text content is preferred but AMS will attempt to strip any HTML, also please try not to include any signatures but AMS will also attempt to remove those as well.

The format of the file for adding, merging and replacing must be the same as that used for the web update via CSV content but as no column mapping is applied the column names must match the external labels of the list item template exactly. The delete CSV file should only contain the column for the identity field.

An example of an email body would be:

api key: a111dcb4-6a07-11e4-b116-123b93f75cba
list name: MyPartsList
update type: replace