Exult Standard User Guide : Introduction

Introduction

Exult is a powerful Windows application for converting XML to the following output formats:

  • Microsoft Excel spreadsheet (XLS files)
  • Microsoft Access database (MDB or ACCDB files)
  • Comma Separated Values (CSV files)
  • HTML
  • Plain Text.

Exult works primarily with data-centric XML files to extract data to an embedded database. The XML structure information is reflected in the database using primary and foreign key constraints.


Exult Standard User Guide : 1. Easy XML Conversion

1. Easy XML Conversion

Exult helps you convert or import data from one or more XML files into a Microsoft Excel Spreadsheet (XLS file), a Microsoft Access Database (MDB or ACCDB file), CSV (comma separated values), HTML or Text.

The main features:

  • Easy to use: With a familiar wizard interface, converting XML to Excel, Access, CSV or Text was never this easier!
  • Automatically figures out Table Data: The XML Structure is processed in an intuitive way to extract table data, and each table is written to a separate worksheet (or database table), thus simplifying the task of sorting through the data.
  • Maintains Table Relationships: Table Relationships (which are dictated by the XML structure) are maintained by the use of auto-generated primary and foreign keys. Makes it easy to see how the data in separate tables relate to each other.
  • Merges data from multiple XML files: Merges and extract the data from multiple XML files. Smart parsing will ensure the data will be merged according to the XML file structure.
  • Imports Attribute information too when loading XML: Attribute information within the XML is treated similarly to element information and is extracted to appropriate worksheets.
  • Select & Convert only the data you are interested in: After the XML file is loaded, you can preview the data and select the tables you want to export.
  • Supports multiple export formats: Export data to Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), Comma-Separated Values(CSV), HTML or Text.
  • Extract and Convert data on a schedule: Exult includes a command line program which makes the task of performing automated (scheduled) batch conversion of XML easy.
  • Extract your data the way you want it: Exult applies database normalization rules while parsing the XML file and extracting the data to an embedded database. The data is distributed to different tables depending on the structure of your XML. You can merge data in these these tables with a single click – with proper relationships maintained.


Exult Standard User Guide : 2. Example XML Conversion walkthroughs

2. Example XML Conversion walkthroughs

Converting XML to Excel(XLS), Access(MDB or ACCDB) or CSV was never this easy. Check out these sections for quick walk-through illustrating the conversion of XML to your prefered output format.


Exult Standard User Guide : 2.1 Convert XML to Microsoft Excel (XLS)

2.1 Convert XML to Microsoft Excel (XLS)

This walk-through demonstrates how easy it is to convert XML to Excel (XLS files). Exult works by shredding the XML to database tables and extracting each table to a separate worksheet. XML relationships are built by using primary and foreign key constraints in the database. You could select one or more tables for extracting to XLS format while previewing the data. Or, if you choose, you can combine data from two or more tables by creating a view and extracting the combined data. This is particularly useful when the data is split into different tables due to database normalization rules.

  1. The Welcome Screen
  2. Trial Version Information
  3. XML File Selection Screen
  4. XML Processing Screen
  5. The Data Preview Screen
  6. Merging Tables to create a view
  7. The Output Type Screen
  8. Excel Output Options Screen
  9. The Finish Screen


Exult Standard User Guide : 2.2 Convert XML to Microsoft Access database (MDB or ACCDB)

2.2 Convert XML to Microsoft Access database (MDB or ACCDB)

Converting XML to Microsoft Access (an MDB or ACCDB database) is very easy using the Exult XML Conversion Wizard. Exult parses the XML for data, automatically creates tables and columns, and builds relationships using primary and foreign keys. These relationships mirror the hierarchical structure of XML.

Exult also makes it very easy to merge the data from multiple XML files and export to a single Access database.

Once the shredding of the XML is complete, you can select the tables you want to export by previewing the data. Or, if you so choose, you can merge the data from two or more tables by creating a view and extracting the data from the view (also called Query in Access).

A particularly noteworthy feature about Exult is that it can handle large XML files without running into memory restrictions. This is due to the fact that Exult parses XML elements and attributes to an embedded database, so the entire XML file is never loaded into memory.

Note that Exult supports different output formats in addition to Microsoft Access, including exporting the XML data to a Microsoft Excel spreadsheet (XLS file), Comma separated values (CSV files), HTML or Text.

  1. The Welcome Screen
  2. The Trial Screen
  3. XML File Selection Screen
  4. XML Processing Screen
  5. The Data Preview Screen
  6. Merging Tables to create a view
  7. The Output Type Screen
  8. Microsoft Access Output Options Screen
  9. The Finish Screen


Exult Standard User Guide : 2.3 Convert XML to CSV (Comma Separated Values)

2.3 Convert XML to CSV (Comma Separated Values)

Exult makes converting XML to CSV very easy. It works by parsing the XML for data, automatically creating tables and columns, and identifying relationships between tables as dictated by the XML structure. These relationships are built using primary and foreign keys. Though primary and foreign keys do not have any special significance for CSV files, they are very important when loading the data into a database. By exporting the XML relationships as primary and foreign keys, Exult gives you the option of carrying these relationships over to whatever the intended target of the CSV export.

Exult also includes options to merge data from two or more tables into a single table. The merge takes into account the relationships between parent and child tables. This is very useful if you want to flatten the XML data.

After XML parsing is complete, you can preview the data and select the tables you want to export.

An important feature of Exult is that it can handle large XML files without running into memory restrictions. This is due to the fact that Exult parses the XML data to an embedded database, so the entire XML file is never loaded into memory.

Note that Exult supports different output formats in addition to CSV, including saving directly to a Microsoft Excel spreadsheet (XLS file), Microsoft Access Dataabase (MDB or ACCDB files), HTML or Text.

  1. The Welcome Screen
  2. The Trial Screen
  3. XML File Selection Screen
  4. XML Processing Screen
  5. The Data Preview Screen
  6. Merging Tables to create a view
  7. The Output Type Screen
  8. CSV and Text Output Options Screen
  9. The Finish Screen


Exult Standard User Guide : 3. Using Exult

3. Using Exult

The following sections describe in detail how to use Exult to convert XML.


Exult Standard User Guide : 3.1 The Welcome Screen

3.1 The Welcome Screen

This is the first screen of Exult. Click Next to start.

Exult Welcome Screen


Exult Standard User Guide : 3.2 The Trial Screen

3.2 The Trial Screen

This screen appears only in the trial version of Exult. The purpose of this screen is to warn you of the trial version limitations and to give you an opportunity to purchase Exult from the application itself.

You can purchase Exult by clicking on the Click to purchase Exult now link in this page. You will be directed to a secure page for placing your order.

Once your order has been processed successfully, you will receive an email with a download link to download the full version and a license key for the software.

Select one or more XML Files to open

The trial version of Exult XML Conversion Wizard has the following limitations:

  • When data is extracted, Exult replaces some extracted values with <<Trial Version>>. This is not text that was found in your XML file(s), but was added by Exult. The purpose of the trial version is to let you evaluate whether the output created by Exult fulfils your needs. And, in our opinion, inserting the <<Trial Version>> message in the output does not detract you from this evaluation.
  • Time Limited: Works for 14 days only.


Exult Standard User Guide : 3.3 The Registration Screen

3.3 The Registration Screen

Once you purchase Exult, you will receive (via email) a download link where you can download the full version of the software, and a license key to use the software. The Full version download is a Setup file which installs Exult on your computer. Please uninstall the trial version before installing the full version (though not doing this will not cause any problems).

After installing the full version and running Exult, you will be presented with a screen where you need to enter the name and license key exactly as specified in the confirmation email. Please refer to the screen below.

Enter the name in the Registered to: field and the license key in the Activation Code field. After that is done, the Next button becomes active. When you click Next, the key is applied and you should get a confirmation message about the acceptance of the key.

Exult Welcome Screen


Exult Standard User Guide : 3.4 XML File Selection Screen

3.4 XML File Selection Screen

Select one or more XML Files to open

Using Exult, you can convert a single XML file or many XML files with the same ease. Clicking Add on this screen brings up a File Selection dialog box which allows you to navigate to the directory where your XML files may be stored.

File Selection Box

In the File Selection dialog box, you can select a single file and click Open. Or you can select multiple files by holding down the Ctrl or Shift keys while selecting the files.

If the files you need to process are located in different directories, you can click Add again to navigate to the other directory and add the files there.

Exult does not impose any upper limit on the number of files you can select for processing.

To de-select a file from processing, highlight the file. The Remove button becomes active; click it to remove the files from the list. Don’t worry, this does not delete the files from the hard disk.

Note that each file selected for processing is shown along with the size of the file. The screen also shows the number of files selected for processing.


Exult Standard User Guide : 3.5 XML Processing Screen

3.5 XML Processing Screen

XML Processing Screen

The processing screen shows the progress of opening (parsing) the XML file. Since Exult can process many XML files at once, the processing screen shows two progress bars: The top bar shows the progress for opening the current file and the bottom bar shows the position of the current file in the list of files.

Once each file is successfully processed, it is added to the ‘Sucessfully Processed’ list. If an error is encountered while processing a file, the file is added to the ‘Error encountered’ list. You can double-click on each file in this list to obtain the details of the error.

Note that processing is not interrupted when processing multiple XML files: The file encountering the error is added to the ‘Error encountered’ list and any changes till the error was encountered are ignored (rolled back, in database parlance).

XML Processing Screen with errors

When an error is encountered and processing of the remaining files is completed, the ‘Next’ button is grayed out. You will need to check the ‘Ignore the file(s) which had the error’ checkbox to continue. This is to draw attention to the errors. Once the checkbox is selected, you can click Next to continue onwards to the next screen.

Detailed error while processing XML

As shown the error details box shows the position of the error, so if it looks simple, you can correct the error in an editor such as Notepad or Wordpad.


Exult Standard User Guide : 3.6 The Data Preview Screen

3.6 The Data Preview Screen

The Data Preview panel is used to browse the data once the processing is completed. The top panel contains the tables created from the XML. When a table is selected here, the data is shown in tabular format in the bottom panel, along with the columns names extracted from element and attributes in the XML. Elements that are missing in the XML are shown as NULL.

To select a table for export, select the checkbox adjacent to each table in the top panel. Clicking Next without explicitly selecting any tables (or views, if any) will result in all tables selected for export.

Click the Select All button to explicitly select all the tables for export. Likewise, clicking Deselect All will de-select all tables for export.

Data Preview Screen


Exult Standard User Guide : 3.7 Merging Tables to create a view

3.7 Merging Tables to create a view

You can also merge two or more tables into a single View. This has the effect of flattening the hierarchical data and may be prefered in some circumstances. Note that the parent-child relationships are used for joining the tables.

To merge the data in two or more tables and create a view, select the tables and click Merge. A dialog pops up to prompt for the view name. A name is suggested based on the selected tables. You could accept this name or enter a different name. If you do choose to enter a name, note that only alphabetic, numeric and underscore characters are accepted in a name.

Merge Tables to create a view

Hint: The merge option is particularly useful when the parent and child tables have nearly the same number of rows. This flattens the XML data nicely linking each row in the child table with the corresponding row in the parent table. This is very useful when you need to flatten a part or all the data in the XML file (perhaps for exporting or converting to CSV).

Once the merged view is created, it is automatically selected for export and the component tables are deselected. This is so you can quickly select multiple groups of tables and create views from them.

In the current version of Exult, you cannot merge tables on different branches. This is planned for a future version.


Exult Standard User Guide : 3.8 The Output Type Screen

3.8 The Output Type Screen

After previewing the data and selecting the tables for export, you are taken to the Output Type Selection screen. Select from one of the following output formats:

Microsoft Excel spreadsheet(XLS file): Creates a Microsoft Excel spreadsheet from the exported XML data. Each selected table is output as a separate worksheet within the spreadsheet. The column headings appear in the first row followed by the data. All columns are exported including the primary and foreign key columns. Note, however, that foreign key constraints between parent and child tables cannot be exported to Microsoft Excel.

Microsoft Access database (MDB or ACCDB file): Creates a Microsoft Access database from the exported XML data. Each selected table is created within the Access database and foreign key constraints are properly created.

Comma Separated Values (CSV file): Creates one or more CSV files depending on tables and options selected. The column names are exported as the first line in the CSV file, followed by the data.

Text output (TXT file): Select this option for creating delimited text files. When you select the option Save output to CSV files, and select a field delimiter other than Comma, the output is generated as text files with the extension txt. You could select either Tab or Space as the delimiter, or enter any single character as the delimiter.

HTML: Creates a single HTML file with each selected table output as a single HTML table.

Selecting the Type of Output


Exult Standard User Guide : 3.9 Excel Output Options Screen

3.9 Excel Output Options Screen

When you choose to export the XML data to Microsoft Excel, you will be taken to the Excel Output Screen shown below.

Output XML to Excel

Click ‘Browse’ to open a File Browser, navigate to the directory where you want the output created, enter the name (or accept the suggested name) and click ‘Save’.

Save as Dialog

After selecting the output file location, click ‘Next’ to begin generating the output.


Exult Standard User Guide : 3.10 Microsoft Access Output Options Screen

3.10 Microsoft Access Output Options Screen

On this screen, first you need to select the version of Microsoft Access to export the XML data to.

  • Access 2007: Select this option to save the output as an ACCDB file. Note that this option will work ONLY if Microsoft Access 2007 is installed on your computer.
  • Access 2000+ versions including Access 2000, 2002 or 2003. The generated file is an MDB file which can be opened in Access 2000 and later.
  • Access 97: This is the oldest version of Access that Exult supports exporting the XML data to.
Output XML to Access

After selecting the version of Access, you need to select the location where the Access database file (MDB or ACCDB as the case may be) is to be created. Click ‘Browse’ to open a File Browser, navigate to the directory where you want the database created, enter the name (or accept the suggested name) and click ‘Save’.

Save as Dialog

After selecting the output file location, click ‘Next’ to begin generating the output.


Exult Standard User Guide : 3.11 CSV and Text Output Options Screen

3.11 CSV and Text Output Options Screen

This panel is used to select options for saving to CSV files or Text files. There are two ways of saving the CSV output: Save the data from each selected table to a separate CSV file. If you select this option, you will need to specify a folder where the CSV files are to be created. This option is recommended if you are using the CSV files to load the XML data into a database.

Output XML to CSV

You could, of course, opt to save the data for all the selected tables into a single CSV file. Select this option if you intend to use Microsoft Excel to further process the CSV data.

Output XML to a single CSV file

After selecting the output file location, click ‘Next’ to begin generating the output.


Exult Standard User Guide : 3.12 HTML Output Options Screen

3.12 HTML Output Options Screen

For exporting the selected XML data to HTML, you need to select the HTML file location by clicking Browse.

Output XML to HTML

After selecting the output file location, click ‘Next’ to begin generating the output.


Exult Standard User Guide : 3.13 Save Progress Screen

3.13 Save Progress Screen

Output XML to Excel

This screen shows you the progress of saving the output file(s). If the output is large, it could take some time. Once that is done, you can click ‘Next’.


Exult Standard User Guide : 3.14 The Finish Screen

3.14 The Finish Screen

After saving the output is complete comes the Finish Screen. It shows the number of tables that were exported and an offer to open the output file (or folder). De-select the checkbox if you do not want to open the output file. Click Finish to end.

The Finish Screen


Exult Standard User Guide : 3.15 Table Relationships

3.15 Table Relationships

Exult maintains the XML structure between parent and child elements by creating the appropriate parent and child tables and using foreign key constraints on the child table to link it to the parent table. This relationships between the parent and child tables resembles a tree structure. This structural relationship can be viewed in Microsoft Access after exporting the XML to Access. A sample tree structure between parent and child tables created by Exult is shown below.

XML tree structure exported to Access


Exult Standard User Guide : 4. Using Exult from the command line

4. Using Exult from the command line

Exult can be used to perform automated batch conversion of XML to Excel (XLS), Access (MDB or ACCDB), CSV, HTML or Text. The command line program is called ‘Exultcmd.exe’ and should be installed in the same directory as ‘Exult.exe’, typically ‘C:\Program Files\Novixys Software\Exult 2.5\’.


Exult Standard User Guide : 4.1 How to convert XML from the the command line

4.1 How to convert XML from the the command line

Below are some examples of using Exult from the command line to convert XML to Excel(XLS), Access(MDB or ACCDB), or CSV.


Exult Standard User Guide : 4.1.1 Convert XML to Excel(XLS) using the command line

4.1.1 Convert XML to Excel(XLS) using the command line

The following command shows you how to convert the XML file ‘sales.xml’ to an Excel spreadsheet ‘sales.xls’.

Exultcmd.exe -t XLS -f sales.xls sales.xml

If the file is large, you may want to turn off progress reporting using the ‘-q’ option to make the execution faster.

Exultcmd.exe -q -t XLS -f sales.xls sales.xml

The above command does not overwrite ‘sales.xls’ if it exists. To overwrite the output file if it exists, use the overwrite option (‘-w’).

Exultcmd.exe -w -t XLS -f sales.xls sales.xml


Exult Standard User Guide : 4.1.2 Merge multiple XML files and convert to Excel(XLS) using the command line

4.1.2 Merge multiple XML files and convert to Excel(XLS) using the command line

Excel uses a smart merge technology to automatically merge data from multiple XML files. This comes in very handy if you have similar data in many XML files (maybe you receive this everyday), and you need to convert data in these XML files to a single Excel spreadsheet.

The conversion is very simple: just list out all the XML files you need to merge and extract to Excel. In the following example, sales data for each month is merged and extracted to an XLS file.

Exultcmd.exe -t XLS -f sales.xls -w sales-062007.xml sales-072007.xml sales-082007.xml

Soon gets tedious if you need to explicitly list out all the XML files for conversion. Just use a wildcard.

Exultcmd.exe -t XLS -f sales.xls -w sales*.xml

What if you need to merge all XML files in a directory and extract to Excel?

Exultcmd.exe -t XLS -f sales.xls -w C:\Dir\*.xml

How about merging XML files in multiple directories? Follow this example:

Exultcmd.exe -t XLS -f sales.xls -w C:\DirA\*.xml C:\DirB\*.xml


Exult Standard User Guide : 4.1.3 Extract XML data to Microsoft Access MDB using the command line

4.1.3 Extract XML data to Microsoft Access MDB using the command line

Use the following command line to extract data from an XML file to a Microsoft Access MDB database:

Exultcmd.exe -t MDB -f sales.mdb -w sales.xml

Disable progress reporting using the ‘-q’ option if the file to be converted is large:

Exultcmd.exe -q -t MDB -f sales.mdb -w sales.xml

Want to overwrite existing files (perhaps as a scheduled batch conversion)? Use the ‘-w’ option to overwrite the target file.

Exultcmd.exe -w -t MDB -f sales.mdb -w sales.xml

By default, output created by Exult is compatible with Microsoft Access 97. To target Microsoft Access 2003, for example, use the ‘-a’ option:

Exultcmd.exe -t MDB -a 2003 -f sales.mdb -w sales.xml


Exult Standard User Guide : 4.1.4 Import XML to Microsoft Access ACCDB files from the command line

4.1.4 Import XML to Microsoft Access ACCDB files from the command line

To import XML into Microsoft Access ACCDB files, you will need to have Microsoft Access 2007 installed on the computer. Note that this is not a requirement for exporting XML to other versions of Microsoft Access including Access 97, Access 2000, 2002 or 2003. The version of Microsoft Data Access Components (MDAC) present in recent computers supports exporting to these versions of Microsoft Access without Access being installed.

Use the following command line to convert an XML file to Access 2007 ACCDB files. Note that you need to use ‘-t MDB’, use ‘-a 2007’ and specify the output file as ‘file.accdb’.

Exultcmd.exe -t MDB -a 2007 -f sales.accdb -w sales.xml


Exult Standard User Guide : 4.1.5 Convert XML to CSV from the command line

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.


Exult Standard User Guide : 4.2 Command Line Reference

4.2 Command Line Reference

The typical command line invocation looks like this:

Exultcmd.exe [Options] -t <Type> File1.xml File2.xml ..

Use the ‘-t <Type>’ option to specify the output conversion type. See section Specify the Output type using the -t option.

After the options, specify one or more input XML files as shown above.

Supported options are shown below:


Exult Standard User Guide : 4.2.1 Specify the Output type using the -t option

4.2.1 Specify the Output type using the -t option

Specify the output type using the ‘-t <Type>’ option. The following are the valid types of output.

-t XLS: Convert XML to Microsoft Excel spreadsheet (XLS file).

-t MDB: Convert XML to a Microsoft Access database (MDB or ACCDB file). See section Specify the version of Microsoft Access version using the -a option for more on ACCDB files.

-t CSV: Convert XML to CSV.

-t HTML: Convert XML to HTML.

-t TEXT: Convert XML to delimited text.


Exult Standard User Guide : 4.2.2 Specify the version of Microsoft Access version using the -a option

4.2.2 Specify the version of Microsoft Access version using the -a option

Specify the version of Microsoft Access in which you want to open the saved Access database. Valid values are:

-a 2007: Save output in a Microsoft Access 2007 compatible format. Use this option to generate ACCDB files.

IMPORTANT: To save the data for Microsoft Access 2007, it must be installed on this computer.

-a 2003: Save output in a Microsoft Access 2003 compatible format.

-a 97: Save output in a Microsoft Access 97 compatible format.

The -a option can only be specified along with -t MDB.


Exult Standard User Guide : 4.2.3 Specify the Output directory using the -d option

4.2.3 Specify the Output directory using the -d option

Use the -d <OutputDir> option to specify an output directory for the created output files. Supported only for CSV or TEXT output. When this option is specified, data from each table is written to a separate file in the specified directory.


Exult Standard User Guide : 4.2.4 Specify the Output file using the -f option

4.2.4 Specify the Output file using the -f option

Specify the output file name using the -f <OutputFile option. Note that only one of -f or -d option can be used.


Exult Standard User Guide : 4.2.5 Dry-run (no execution) using the -n option

4.2.5 Dry-run (no execution) using the -n option

When the -n option is specified, no conversion is actually performed. Instead the program shows the input files that will be processed and the output files that will be created without actually doing anything.


Exult Standard User Guide : 4.2.6 Disable progress reporting using the -q option

4.2.6 Disable progress reporting using the -q option

Turns on quiet mode which suppresses progress reporting. Use this option when processing large files since progress reporting slows down the program a little bit.


Exult Standard User Guide : 4.2.7 Specify the separator for text output using the -s option

4.2.7 Specify the separator for text output using the -s option

Specify the field delimiter for text output using the -t SeparatorCharacter option (typically ‘,’, ‘:’, etc). Useful only with -t Text output type.


Exult Standard User Guide : 4.2.8 Overwrite existing files using the -w option

4.2.8 Overwrite existing files using the -w option

Tells Exultcmd.exe to overwrite output file(s) as needed. Existing files will NOT be overwritten unless this option is used.