To get data from a CSV file into a simple XML document you would typically either write a small script or use another software application and get involved with schema's and so on.
However if you are already using Data Sync then you can make use of our built in features to either export to XML or create a new XML document to be used in a regular synchronisation.
Below we cover how you can sync data from a CSV file into a new XML document, how you can export data as XML, and how to connect to an existing XML file to use in your integrations. Although we specifically cover using data from a CSV file you can use any of our built in connectors to get data from your business systems. For example SharePoint, Salesforce, SQL Tables etc.
Before getting started you need to ensure you have the following:
If you do not have Data Synchronisation Studio you can get a free evaluation edition.
Start by opening Data Sync and connecting to your CSV file. You can do this either by dragging and dropping the CSV from the file explorer onto your source, or by clicking onto Connect Datasource > Text Files > CSV File and then browsing for your CSV by clicking onto the ellipsis in the FileName field.
Click Connect to load the file into your source window.
Before creating the new XML document you need to add the columns you want to include and create in the table to the schema map.
By default all the columns will be added but you can remove and re-order the columns as you need to. Make sure to include a key column that can be used to uniquely identify each record.
The new XML document nodes/columns will be created based upon what is included in the schema map.
If your data does not currently have an ID column that can be used to uniquely identify each record, then you can use calculated columns to create one. Calculated columns enable you to decorate your dataset with additional data.
To do this click onto the fx button in the data source toolbar and then enter in a unique name for the column and set the data type. You can then enter in a function to decorate the data.
In this example we use NEXTINT(). The NEXTINT() function returns a number for each row, incrementing the value by 1 each time it is called.
For more examples of the functions you can use with calculated columns please see our documentation pages.
There are two options available when creating a new XML document. If it is a one time sync then you can export the data from the preview window, alternatively if it is going to be a regular sync then you can create an XML file from the Tools menu.
To export from the preview window, click Preview A in the toolbar of the schema map window or click onto the preview button in the source window.
and then click onto the Export XML button.
To create a new XML from the tools menu, go to Tools > Create XML DataSet.
This will open the XML DataSet Wizard where you need to select where to create the file and enter in a name for the file. To do this click onto the ellipsis to open the file explorer, navigate to your destination and enter in a name for the file.
Click Finish to load the file into the target window. The columns created will be based upon the columns added to the schema window.
If you already have an XML file that you want to connect to, click onto the target window to open the connection window.
Then go to Xml > XML File and browse to your file by clicking onto the ellipsis in the XMLDocumentURL field. Once you are done click Connect to load the datasource.
Once your schema map is configured and a key column selected you can compare the differences between your CSV and the new XML Document.
As this is the first sync there will only be additions to make.
For any future runs of this project or if you are connecting to an XML file that already contains data, then you may also notice updates and deletes.
Please note that deletes are disabled by default but will still show in the results. These can be enabled by changing EnableDelete to True.
To add the records to the XML file click Synchronise and then Start to begin the sync.
The records will then be visible in the XML document.
If you want to import a CSV file to a new SQL table, please see our other blog post, or send us an email if you have any questions.