4.1.5 Convert XML to CSV from the command line
Exult can convert XML data to CSV. Two methods of extracting XML to CSV are supported.
You can extract the data in all tables to a single CSV file. Each table is extracted in the following order: table name, followed by column names, followed by the data itself. You may want to use this method of extraction when you intend use an application such as Microsoft Excel to further massage the data. Use the ‘-f’ option to specify an output CSV file as shown here.
Exutlcmd.exe -t CSV -f sales.csv sales.xml
The other method of extraction is to extract each table to a separate CSV file. In this case, each file contains the column names followed by the data. This format is especially suitable for loading the XML data into a database. For this output method, you need to specify an output directory using the ‘-d’ option as shown:
Exutlcmd.exe -t CSV -d outputDir sales.xml
To merge the data from multiple XML files and extract the merged data to CSV (in a single file), use the following command:
Exultcmd.exe -t CSV -f sales.csv sales*.xml
Convert XML files in multiple directories and extract to CSV (each table to a separate CSV file):
Exultcmd.exe -t CSV -d outputDir C:\Path\DirA\*.xml C:\DirB\*.xml
Note: If you want to convert XML to CSV for the purpose of loading into a database, we have versions of Exult for SQL Server, Oracle and MySQL.
Advantages of using these products include:
- Automatic creation of tables and columns in the database. With the CSV option, you have to create the tables and columns yourself.
- Automatic creation of relationships between parent and child tables mirroring the XML structure. When you use the CSV to load into a database, you have to create these relationships by hand.
- One stop solution for loading data from multiple XML files into the database. More convenient than using CSV.
- Easy updates of XML data. When you need to refresh data from one or more XML files that have already been loaded, these products offer a much easier way of doing it. When using CSV to load the data, you are on your own.
That being said, it is sometimes more convenient to load data from the CSV. One reason is that the CSV option offers you more control over the import process, including being able to name the tables and columns whatever you want.