Swift XML Converter Command Line User Guide : Swift XML Converter Command Line

Swift XML Converter Command Line

Covers Swift XML Converter Command Line version 1.2 (or later).

Novixys Software, Inc. © 2008-2012. All rights reserved.


Swift XML Converter Command Line User Guide : 1. Introduction

1. Introduction

Swift XML Converter Command Line allows you to import data from one or more XML files and convert it to Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), CSV, Text or HTML. It serves as a command line interface to Swift XML Converter (GUI) and allows access to most of the functionality so that these tasks can be scripted and automated.

Swift XML Converter works primarily with data-centric XML files to extract data from XML files and export to one of the many supported formats.

Main Features

  • Import XML files: Quickly import one or more XML files into a Swift Document (*.XDB) file.
  • Load XML from URLs: Import XML from local files or remote URLs, including web(HTTP) and FTP URLs. Specify file locations or URLs on the command line or from a file.
  • Execute Queries: Queries(1) that have been defined in the Swift document can be executed and the data extracted to any supported output format.
  • Export data to any supported format: You can extract table data or query data to Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), CSV, plain text or HTML.
  • Schedule automatic imports and exports: Use the Windows Scheduler to schedule XML import jobs. Or create a nightly job to extract the data for consumption by other applications.

Importing XML

  • Automatically figures out relationships: Swift XML Converter automatically creates data tables with parent - child relationships mirroring the XML structure.
  • Merge data from multiple XML files: Merge and extract the data from multiple XML files. Smart parsing will ensure the data will be merged according to the XML file structure.
  • Optimized for large files: Large and complex XML files are easily imported without running into large memory requirements.
  • Import XML from URLs: With the built-in URL-handling module, you can directly import XML from web(HTTP) and FTP URLs without having to fetch the file using another application. If the website (or FTP site) requires user name and password for authentication, that is supported too(2).

Footnotes

(1)

Queries can only be defined using the Swift XML Converter GUI. The command line does not provide a facility to create new queries. However, queries created using the GUI are stored in the Swift document which can be executed using the command line.

(2)

HTTP Basic and Digest Authentication only. Form-based authentication not supported


Swift XML Converter Command Line User Guide : 2. Command Line Usage

2. Command Line Usage

This chapter explains how to use the Swift XML Converter Command Line. The command line program is called SwiftCmd.exe and is installed (by default) into ‘C:\Program Files\Novixys Software\SwiftCmd 1.0’ (or something similar).


Swift XML Converter Command Line User Guide : 2.1 Command Line Usage Summary

2.1 Command Line Usage Summary

Format

The general format of the command line usage is as follows:

SwiftCmd.exe [Options] <Request> <SwiftDocument> <Arguments ..>
Request

Represents the action to be performed.

SwiftDocument

Path name of the Swift document to perform the action on.

Arguments

Arguments for the request (depends on the request).

Requests

Requests are used to indicate the activity to be accomplished. Valid Requests are as follows. For more details on each request, please see the following sections.

importXML

Import one or more XML files into the Swift Document.

listXML

List the XML files imported into the Swift Document.

listQueries

List the queries defined in the Swift Document. (To create queries, please use the Swift XML Converter (GUI).)

listTables

List the tables defined in the Swift Document.

exportXLS

Export data to Microsoft Excel (XLS) format.

exportMDB

Export data to Microsoft Access (MDB or ACCDB) format.

exportCSV

Export data to CSV (comma-separated values) format (compatible with Microsoft Excel).

exportTXT

Export data to plain text (TXT) format.

exportHTML

Export data to HTML format.

Options

The following are all the options that are supported. Please note that some options are applicable only for specific requests (as noted).

-a AccessVersion

Specify the version of Microsoft Access with which you want the exported MDB or ACCDB file to be compatible with. Should be one of 2007, 2003 or 97 (default).

Applicable only with the exportMDB request.

-e Encoding

Specify output encoding when exporting to CSV, Text or HTML. Valid encodings are ANSI, UTF-8 or UTF-16.

-i InputFile

Read XML file locations (or URLs) to import by listing them in a file, one per line. Each line can be a local file path or a URL. Local file paths are processed for wildcards too.

Applicable only when importXML is being used.

-N (on|off)

Normalize (with -N on) repeated XML elements to a separate table. By default, repeated XML elements are inserted into the same table with suffixes e.g. column1, column2, etc.

Applicable only when importXML is being used.

-s SeparationChar

Specify the field delimiter character to be used with text output. Typical examples include “,” (comma), “:” (colon), “;” (semi-colon) or “|”. Must be a single character (not a string).

For specifying the tab-character, use “\t” (back-slash followed by “t”).

Applicable only with the exportTxt request.

-w

Overwrite output files as needed. Existing output files will not be overwritten unless this option is used.

Applicable only with the export requests such as exportXLS, etc.

-x

Use alternative Microsoft Excel output. With this option, each exported table (or query) is exported to a separate sheet within the Worksheet. By default, all exported tables are written into a single sheet.

Applicable only when used with exportXLS.


Swift XML Converter Command Line User Guide : 2.2 Create a Swift Document

2.2 Create a Swift Document

To create a Swift Document from the command line, use the importXML action as follows: This command will create a Swift Document called Data.xdb in the current directory if one does not already exist and import an XML file into it. This file can then be loaded into Swift XML Converter (GUI) for the purpose of defining and editing queries, browsing the data, etc.

SwiftCmd.exe importXML Data.xdb C:\Path\To\XML\file.xml


Swift XML Converter Command Line User Guide : 2.3 Import XML

2.3 Import XML

Import an XML file into a Swift Document as follows:

SwiftCmd.exe importXML Data.xdb C:\Path\To\XML\file.xml

Import multiple XML files as follows:

SwiftCmd.exe importXML Data.xdb file1.xml file2.xml ..

To specify a wildcard to select multiple XML files, use the following command line.:

SwiftCmd.exe importXML Data.xdb *.xml

Import XML from a URL be specifying the URL as follows:

SwiftCmd.exe importXML Data.xdb http://www.example.com/path/to/file.xml

If the website requires a user name and password, please specify the URL in the following format. Note that password should be specified in clear text form.

http://username:password@www.example.com/path/to/file.xml

You could also fetch files using FTP by specifying the URL as follows:

ftp://username:password@ftp.example.com/path/to/file.xml

Importing XML URLs from an input file

When you need to import files from many different locations or URLs, it is easier to store the locations in a text file rather than list them on the command line. In this case, the text file should contain one URL or path per line. For local paths, file specification using wildcards are supported.

SwiftCmd.exe -i input.txt importXML Data.xdb

The file ‘input.txt’ contains the following:

http://weather.gov/data/current_obs/KNMM.xml
http://weather.gov/data/current_obs/KPIB.xml
http://weather.gov/data/current_obs/KPQL.xml
http://weather.gov/data/current_obs/KTUP.xml
http://weather.gov/data/current_obs/KHKS.xml
http://weather.gov/data/current_obs/KOLV.xml
http://weather.gov/data/current_obs/KPGL.xml
http://weather.gov/data/current_obs/K3DU.xml

Repeated XML Elements (no normalization)

When repeated XML elements are encountered within a parent element, the data is inserted into the same table as determined by the parent element. A numbered prefix is added to the column name to distinguish the instances. For instance, consider the following XML snippet:

<address>
 <line>123 Main Street</line>
 <line>Suite 1</line>
 <city>Anytown</city>
 <state>Any State</state>
 <zip>12345</zip>
</address>

This snippet creates a table called ‘address’ with the following structure:

Table ‘address’
---------------------------------------------------------
line1           | line2   | city    | state     | zip   |
---------------------------------------------------------
123 Main Street | Suite 1 | Anytown | Any State | 12345 |
---------------------------------------------------------

Normalizing repeated XML elements

However, in some cases, there may be too many repeated elements to be inserted into the same table in this manner. Or the data might better fit into a separate table. In these cases, you can normalize the data so that repeated elements are inserted into a child table which is then linked to the parent table. From the command line, you can use the -N option to request this type of normalization.

For example, consider the following XML snippet:

<credits>
  <director>Martin Scorsese</director>
  <actor>Paul Newman</actor>
  <actor>Tom Cruise</actor>
  <actor>Mary Elizabeth Mastrantonio</actor>
  <actor>Helen Shaver</actor>
  <actor>John Turturro</actor>
  <actor>Bill Cobbs</actor>
  <actor>Robert Agins</actor>
  <actor>Forest Whitaker</actor>
</credits>

With the -N option, two tables would be created as shown which are linked using primary/foreign key relationships:

Table ‘credits’ is the parent table.

Table ‘credits’
---------------------------
PKEY | director
---------------------------
1    | Martin Scorsese
---------------------------

Table ‘actor’ is the child table and linked to its parent ‘credits’ with the FKEY field. As you can see, these 8 rows in ‘actor’ are linked to the same row in the parent table ‘actor’.

Table ‘actor’
-----------------------------------------------
PKEY | FKEY | actor
-----------------------------------------------
1    | 1    | Paul Newman
2    | 1    | Tom Cruise
3    | 1    | Mary Elizabeth Mastrantonio
4    | 1    | Helen Shaver
5    | 1    | John Turturro
6    | 1    | Bill Cobbs
7    | 1    | Robert Agins
8    | 1    | Forest Whitaker
-----------------------------------------------


Swift XML Converter Command Line User Guide : 2.4 Export XML data to Microsoft Excel

2.4 Export XML data to Microsoft Excel

For exporting XML data from a Swift Document to Microsoft Excel (XLS) format, use the exportXLS action as shown below: The following command exports all tables and queries in the Swift Document ‘Data.xdb’ to an Excel spreadsheet ‘Data.xls’.

SwiftCmd.exe exportXLS Data.xdb Data.xls

You can specify specific tables and/or queries to export by listing them out on the command line as shown.

SwiftCmd.exe exportXLS Data.xdb Data.xls Query_1 Query_2 .. Table_1 ..

Please note that if a query has the same name as an existing table, the query will be exported. To have the table of the same name to be exported instead, please prepend the table name with a ^ as follows:

SwiftCmd.exe exportXLS Data.xdb Data.xls ^Name_1 ^Name_2

By default, existing output files will not be overwritten. To overwrite the output file (‘Data.xls’), use the -w option:

SwiftCmd.exe -w exportXLS Data.xdb Data.xls

Output Type

When exporting to Microsoft Excel, Swift supports two types of output:

  • The default output type inserts data from all specified tables and/or queries into a single sheet within the workbook. For example, the following command creates such an output:
    SwiftCmd.exe -w exportXLS Data.xdb Data.xls
  • A different type of output is supported (selected with the ‘-x’ option) that creates a separate worksheet for each exported table and/or query. For some applications this option might work better. Here is how you could use it. Note the ‘-x’ option.
    SwiftCmd.exe -w -x exportXLS Data.xdb Data.xls


Swift XML Converter Command Line User Guide : 2.5 Export XML data to Microsoft Access

2.5 Export XML data to Microsoft Access

For exporting XML data from a Swift Document to Microsoft Access (MDB or ACCDB) format, use the exportMDB action as follows: This command exports all tables and queries in the Swift Document ‘Data.xdb’ to an Access database ‘Data.mdb’.

SwiftCmd.exe exportMDB Data.xdb Data.mdb

You can specify specific tables and/or queries to export by listing them out on the command line.

SwiftCmd.exe exportMDB Data.xdb Data.mdb Query_1 Query_2 .. Table_1 ..

Please note that if a query has the same name as an existing table, the query will be exported. To have the table of the same name to be exported instead, please prepend the table name with a ^ as follows:

SwiftCmd.exe exportMDB Data.xdb Data.mdb ^Name_1 ^Name_2

By default, existing output files will not be overwritten. To overwrite the output file (‘Data.mdb’), use the -w option:

SwiftCmd.exe -w exportMDB Data.xdb Data.mdb

The Access database created is compatible with Access 2003. You can specify a different version of access using the ‘-a <Version>’ as follows:

SwiftCmd.exe -a <version> exportMDB Data.xdb Data.mdb

Valid values for ‘Version’ are:

2007: For Microsoft Access 2007.

2003: For Microsoft Access 2003.

97: For Microsoft Access 97.


Swift XML Converter Command Line User Guide : 2.6 Export XML data to CSV

2.6 Export XML data to CSV

For exporting XML data from a Swift Document to CSV format, use the exportCSV action as follows: This command exports all tables and queries in the Swift Document ‘Data.xdb’ to a CSV file ‘Data.csv’.

SwiftCmd.exe exportCSV Data.xdb Data.csv

You can specify specific tables and/or queries to export by listing them out on the command line.

SwiftCmd.exe exportCSV Data.xdb Data.csv Query_1 Query_2 .. Table_1 ..

Please note that if a query has the same name as an existing table, the query will be exported. To have the table of the same name to be exported instead, please prepend the table name with a ^ as follows:

SwiftCmd.exe exportCSV Data.xdb Data.csv ^Name_1 ^Name_2

By default, existing output files will not be overwritten. To overwrite the output file (‘Data.csv’), use the -w option:

SwiftCmd.exe -w exportCSV Data.xdb Data.csv


Swift XML Converter Command Line User Guide : 2.7 Export XML data to Text

2.7 Export XML data to Text

For exporting XML data from a Swift Document to text format, use the exportTXT action as follows: This command exports all tables and queries in the Swift Document ‘Data.xdb’ to a text file ‘Data.txt’.

SwiftCmd.exe exportTXT Data.xdb Data.txt

You can specify specific tables and/or queries to export by listing them out on the command line.

SwiftCmd.exe exportTXT Data.xdb Data.txt Query_1 Query_2 .. Table_1 ..

Please note that if a query has the same name as an existing table, the query will be exported. To have the table of the same name to be exported instead, please prepend the table name with a ^ as follows:

SwiftCmd.exe exportTXT Data.xdb Data.txt ^Name_1 ^Name_2

By default, existing output files will not be overwritten. To overwrite the output file (‘Data.txt’), use the -w option:

SwiftCmd.exe -w exportTXT Data.xdb Data.txt

The default field separator used is the tab character (\t). To specify a different character, use the ‘-s’ option. For example, the command below requests a text separator character of pipe (|) . Please ensure you enclose the character in double quotes (").

SwiftCmd.exe -s "|" exportTXT Data.xdb Data.txt

To use the space character, use the following command line:

SwiftCmd.exe -s " " exportTXT Data.xdb Data.txt


Swift XML Converter Command Line User Guide : 2.8 Export XML data to HTML

2.8 Export XML data to HTML

For exporting XML data from a Swift Document to HTML, use the exportHTML action as follows: This command exports all tables and queries in the Swift Document ‘Data.xdb’ to a HTML file ‘Data.html’.

SwiftCmd.exe exportHTML Data.xdb Data.html

You can specify specific tables and/or queries to export by listing them out on the command line.

SwiftCmd.exe exportHTML Data.xdb Data.html Query_1 Query_2 .. Table_1 ..

Please note that if a query has the same name as an existing table, the query will be exported. To have the table of the same name to be exported instead, please prepend the table name with a ^ as follows:

SwiftCmd.exe exportHTML Data.xdb Data.html ^Name_1 ^Name_2

By default, existing output files will not be overwritten. To overwrite the output file (‘Data.html’), use the -w option:

SwiftCmd.exe -w exportHTML Data.xdb Data.html


Swift XML Converter Command Line User Guide : 2.9 List Imported XML Files

2.9 List Imported XML Files

To list out XML files imported into the Swift Document, use the listXML action as follows:

SwiftCmd.exe listXML Data.xdb


Swift XML Converter Command Line User Guide : 2.10 List Queries

2.10 List Queries

To list out the queries defined in the Swift Document, you can use the listQueries action. This lists out the query details including the selected columns, column filter conditions, file filters conditions, sort columns, etc.

SwiftCmd.exe listQueries Data.xdb


Swift XML Converter Command Line User Guide : 2.11 List Tables

2.11 List Tables

To list out the tables defined in the Swift Document from the XML, you can use the listTables action. This lists out the table details including the columns, XPath, etc.

SwiftCmd.exe listTables Data.xdb