Introduction:
Extracts data out of an Excel spreadsheet and creates an XML file. Dynamic sections/tables are supported.
Technical Support:
If you need assistance in installing and configuring JetTrac ExcelToXML™, call Pro Technology Automation, Inc. at 805-527-1248 or email us at support@protechinc.com. Please note that the JetTrac ExcelToXML™ license fee does not cover configuration services and technical support so there may be an additional charge. Please ensure you read these instructions carefully before calling for technical support.
Default Functionality:
Used when an Excel spreadsheet is used to capture the data and this data needs to be extracted to XML.
How to Run JetTrac ExcelToXML:
To run this program you will need the following files in the same folder:
- JTExcelToXml.exe
- JTExcelToXml_Config.ini
- ExitHandler.dll
Executable files and dll files should not be edited for any reason.
This module looks for comments in the input Excel document with the format of “~~SectionStart=SectionLabel,RowLabel,CollumnLabel,CollumnLabel,CollumnLabel…” in the top left corner of the section of data and “~~SectionEnd=SectionLabel” at the bottom right corner of the data.
Comments can be added to individual cells by right clicking on the cell and selecting “Insert Comment”
Job Step Configuration Window in JobConfig for JetTrac ExcelToXML:
When setting up a job step using JTExcelToXML in JobConfig the only lines of the config you need to worry about are the first and the last two, the Config file, the Input Excel and Output XML. 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 data from. Eg: C:\JetTrac\…\input.xls
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 Field ServiceJobConfig page.
JetTrac ExcelToXML Config.ini File:
Sample:
CellsToExtract=CompanyName=A1,CompanyAddress=A2,ReportTitle=A3,Customer=B5,JobNumber=B6,JobDate=B7,JobContact=B8,JobLocation=B9,WorkRequest=B11,DescriptionOfWorkPerformed=B12,Recommendations=B13
Section1=Labor,A29:E34,LaborDetail,Date,Name,ST,OT,DT
Section2=PurchaseOrder,A38:E41,PurchaseOrderDetail,Date,Vendor,Qty,Cost,Sell
SectionSearch=true
Explanation:
CellsToExtract= XML Tag = Location of data in spreadsheet.
Section1= XML Parent Tag, Top left corner of section to bottom right corner, XML tags of columns, separated, by, commas
Section2= XML Parent Tag, Top left corner of section to bottom right corner, XML tags of columns, separated, by, commas
SectionSearch= Boolean value (true/false) if true will go through sections.
JetTrac ExcelToXML Command Line:
Sample command line:
“C:\JTExcelToXml.exe” “C:\JTExcelToXml_Input.xlsx” “C:\JTExcelToXML_Output.xml” “C:\JTExcelToXML_Config.ini” “C:\JTExcelToXML.log”
The command line begins with the module’s executable file followed by the input xlsx file and the desired name and destination for the output pdf file. The line continues with the the configuration ini file and the module’s log file. Each part of this command line is the fully qualified path to the file, in quotes, separated by a space (it doesn’t matter how many)
Additional Notes:
For any Excel related module to work, the system running JobController must have Microsoft 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: