JetTrac ExcelMerge

Introduction:

Merges XML data into an Excel spreadsheet based on matching field names

Technical Support:

If you need assistance in installing and configuring ExcelMerge™, call Pro Technology Automation, Inc. at 805-527-1248 or email us at support@protechinc.com. Please note that the ExcelMerge™ license fee does not cover installation, configuration, implementation or technical support so there may be an additional charge if you need those services. Please ensure you read these instructions carefully before calling for technical support.

Default Functionality:

Pre-populates data into an Excel spreadsheet from an XML file. Used when an Excel spreadsheet already exists or when the requirement is that a variable amount of data that needs to be captured. Also used when it makes more sense to have a spreadsheet as a data entry vehicle rather than a PDF form. sometimes it is much quicker to put together a spreadsheet to capture data rather than designing a PDF form.

How to Run JetTrac ExcelMerge:

To run JetTrac ExcelMerge you must have the following files in the same folder:

  • JTExcelMerge.exe
  • JTExcelMerge.ini
  • Exithandler.dll

Executable files and dll files should not be edited for any reason.

Job Step Configuration Window in JobConfig for JetTrac ExcelMerge:

excelmerge.PNG

When setting up a job step using JTExcelMerge in JobConfig the only lines of the config you need to worry about are the Config file, the Excel Template, the Input Xml and the Output Excel. Each field should be filled in with the fully qualified path to the intended file. The Excel Template is the Excel document with empty fields that need to be filled with data. If left as an asterisk * the input will be whatever file is in the data folder that triggered the job running. This works well if it’s the first step but if the input is also the output of a previous job step you would need to specify the fully qualified path of the file you want to extract the annotations from. Eg: C:\JetTrac\…\input.pdf
In JobConfig, file paths entered should not be in quotes.

As with all modules, there is a dropdown to select whether or not the job should stop completely if this step fails. If you select Yes, any error will terminate the job process and write to the log file what happened, if no, then the job will try to continue anyways. However if any steps further down the line rely on the output of a job set to not stop on error, they may not work properly.

For more specifics go to the JetTrac BYODJobConfig page.

JetTrac ExcelMerge Command Line:

Sample command line:

“C:\JTExcelMerge.exe” “C:\JTExcelMerge_Input.xml “C:\JTExcelMerge_Input.xlsx” “C:\JTExcelMerge_Output.xlsx” “C:\JTExcelMerge.ini” “C:\JTExcelMerge.log”

This command line begins with the fully qualified path to the module’s executable file, followed by the input XML containing the data you want merged into the next element which is the input xlsx file. The next part of the command line is the desired name and location of the output xlsx file, followed by the location of ExcelMerge’s config file. The command line then ends with the log file. Each element of the command line is the fully qualified path to the specified file in quotes, separated by a space.

JetTrac ExcelMerge Config.ini:

AutoMerge=true

Other Option:
CellsToMerge=Cell=XMLTag,Cell=XMLTag,…

The first line is to automatically input data from XML tags that are referenced in comments made on specific cells in the spreadsheet.

The other option is to denote what cell location (eg A1) will contain get merged into using “CellsToMerge=” This option is easier to see where the data is going but AutoMerge is more dynamic because if you add rows or columns the cell with the comment will move with the labels around it.

Whichever method you use the other should either be commented out with a # at the start of the line or not exist at all.

Additional Notes:

For any Excel related module to work, the system running JobController must have Excel installed.
If Excel is installed and these modules still aren’t working, there might be an issue with a missing Desktop folder in the system32 file tree. Follow this link for instructions: