Import a CSV file to an XML File
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.
Requirements
Before getting started you need to ensure you have the following:
- Windows 10 or Windows Server
- Downloaded & Installed Data Synchronisation Studio (minimum version 3.0.2110)
- A CSV file with your data
Connect to your CSV File
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.
Add the Columns to the Schema Map
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.
Create an ID Column
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 create an ID column for this dataset click onto the add column button in the data source toolbar.
In the window that opens enter in a unique name for the column, set the data type and enter in a function to decorate the data.
In this example we use NEXTINT() to generate an ID for each row in the source data. 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.
Create the XML Document
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.
Export to XML from Preview
To export from the preview window you can either preview from the source window or preview from the schema map. The source preview will return all the columns in the source but will exclude calculated columns and lookups. To include additional columns or restrict the columns being returned we recommend using the schema map preview.
To preview from the source window, click onto the eye icon to open the preview window
To preview from the schema map, start by adding the columns you want to include to the schema map and then click Preview A in the toolbar of the schema map window
Both preview options will have the same quick export options. For this example we are exporting to an XML file, so click onto the Export XML Dataset button.
Create New XML Document from Menu
To a regular sync to an XML file you can create a new XML document based off the columns included in the schema map.
Start by adding the columns you want to export to the schema map and then open the Tools menu and select Create XML DataSet from the list.
This will open the XML DataSet Wizard, click onto the ellipsis to open the file explorer, navigate to your the location to save the file 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.
Connect to an Existing XML Document
If you already have an XML file that you want to connect to, click onto the target window to open the connection window.
Then expand the XML folder and select .NET XML DataSet. You then need to enter the path to your XML file, which can be done by clicking the ellipsis and selecting the file within the file explorer.
Once you are done click Connect to load the datasource.
Compare the Results and Sync
Once your schema map is configured correctly and a key column selected you can compare the differences between your source and the XML Document.
As this is the first sync there will only be additions to make, but if it is an existing file there may be additions, updates and/or 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 in the target property settings.
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.
Make sure to save your project so that you can use it again in the future. You may wish to schedule it using the Run Tool or Ouvvi.