Filter & Extract XML data

Filter and Extract XML data with batch processing

In this example we demonstrate the following:

Parse one or more XML files using Swift XML Converter.
Create a query for extracting XML data to XLS.
Delete previously imported XML data and import new XML data.
Extract data from the query with the newly imported XML data.
Use the Swift XML Converter Command Line to automate this extraction.

Requires version 1.7 of Swift XML Converter.

The following description requires version 1.7 of Swift XML Converter which can be downloaded here:
Swift XML Converter 1.7

To automate the extraction and conversion you also need Swift XML Converter Command Line which can be downloaded here:
Swift XML Converter Command Line

1. Import XML files into Swift

Start Swift XML Converter.
Click File => Import XML, then Add File and navigate to the directory where your XML files are located and select them.
Click OK to import the XML into the application.

2. Create Query for Data Extraction

Open data from a desired table you.
Modify the query: hide unneccessary columns, apply filters on the data, maybe even join data from multiple tables. Adjust your query so that it has just the data you want.

3. Export the data for verification

Verify that the data export is correct by extracting into the format you require, e.g. XLS.
Open the output data in the application of your choice (for example: Excel for XLS, etc) and verify that the exported data meets your needs.

4. Create XDB template

Now is the time to create an XDB template with queries intact, but old XML data removed. This enables you to import new XML files of the same format and extract just the data you need using the query you created.
To remove data from previously imported XML files, select the Files Imported tab under Info Views. This window will have a listing of all the XML files imported into the application.
Select all the files in the Files Imported window.
Right-click for the context menu and select Delete Data. This will show the data deletion wizard which takes you through the process of deleting the import XML data.

5. Save the XDB file

You can now save the XDB file which contains all your queries but no XML data. We will use this file as a template to import new XML and extract data from previously built queries.
Click File => Save and save the XDB file to a suitable location.

6. Use Command Line to import XML

We will now use the Swift XML Converter Command Line to import new XML.
First copy the template XDB file to a new XDB file into which we will import data. This is so we can re-use the template for another batch of import-and-extract if needed.
```
copy c:\Software\XMLFiles\weather\weather.xdb temp.xdb
```
Import XML into the temporary XDB using a command of the following form. This will import all XML files in the current directory into temp.xdb. If you would rather list out individual XML files here you can replace *.xml with the file names.
```
"C:\Program Files (x86)\Novixys Software\SwiftCmd Trial 1.2\SwiftCmd.exe" importXML temp.xdb *.xml
```
The file temp.xdb now contains the queries you created previously and the XML data you just imported.

7. Extract XML data using the query

You can now extract the XML data using the previously created query.
To extract data from the XDB, use a command of the following form. The name of the query you created is specified as query-name and the output is written to the file output.xls.
```
"C:\Program Files (x86)\Novixys Software\SwiftCmd Trial 1.2\SwiftCmd.exe" exportXLS temp.xdb output.xls query-name
```
The export directive exportXLS exports data as XLS. Execute the program with a -h option for all supported formats.

8. Batch Extraction using Windows Scheduler

You can create a batch job with these commands to run the extraction automatically.

copy c:\Software\XMLFiles\weather\weather.xdb temp.xdb
"C:\Program Files (x86)\Novixys Software\SwiftCmd Trial 1.2\SwiftCmd.exe" importXML temp.xdb *.xml
"C:\Program Files (x86)\Novixys Software\SwiftCmd Trial 1.2\SwiftCmd.exe" -w exportXLS temp.xdb output.xls query-name

A modification you may want to make to the above script would be to move all processed XML files to a done directory so they are not processed next time.
```
mkdir done
move *.xml done
```
Once this script is working to your satisfaction, you can use the Windows Scheduler to run the command at a chosen time and create the output file.

Argon DataViz

Create Pivot Tables and Pivot Charts from Large Datasets.

Download
Free Trial
Buy Now
for US~~$499~~ $299/yr

Swift XML Converter

Query & extract XML data into Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), or CSV.

Download
Free Trial
Buy Now
for US~~$250~~ $195

Exult Standard

Convert XML into Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), or CSV.