Columns To Rows

ColumnsToRows.exe is a command line tool that is included with SimpleIndex.

It is designed to be used as a Post-Process command to convert exported data in a database from a single row of data to multiple rows. The most common application is the extraction of line item data from documents like Invoices and Purchase Orders.

ColumnsToRows.exe will also parse JSON values stored in a database field and populate corresponding field values in the destination table.

SimpleIndex captures and exports fields in a single row of data. Data that is part of a table can be assigned to fields with the row number appended to the field name. ColumnsToRows.exe will take the data from any numbered field and create the corresponding rows in the destination table.

Let's use a simple example of a job that captures a Document ID, Document Date, and 1-5 barcodes that contain JSON encoded data. This job exports to a table we'll call "ColumnTable". The fields in ColumnTable are:

DocumentID, DocumentDate, Barcode1, Barcode2, Barcode3, Barcode4, Barcode5, Flag

The DocumentID and DocumentDate fields are captured from the header. The Flag field is used to track which records have been processed.

We want the 5 barcodes to create 5 rows of data in the destination table, which we'll call "RowTable".

The barcodes contain JSON with fields called "SKU", "Quantity", and "Price", which will be extracted from the barcode values.

The columns in RowTable are:

DocumentID, Barcode, SKU, Quantity, Price

When ColumnsToRows.exe is run as a post-process, it will:

1. Query ColumnTable for any new rows based on Flag field values of 0 or Null
2. Create up to 5 new rows in RowTable for each Barcode field that has a value (blanks are skipped)
3. Populate the common DocumentID field to create the foreign key relationship
4. Put the original JSON value of the barcode in the Barcode field in RowTable
5. Parse the JSON to extract and set the SKU, Quantity, and Price fields
6. Set the value of the Flag field to 1 to indicate the row has been processed

To configure ColumnsToRows.exe:

1. Copy ColumnsToRows.exe and ColumnsToRows.exe.config from the program folder (usually c:\Program Files (x86)\SimpleIndex) to the folder where your Config File is saved
2. Edit the ColumnsToRows.exe.config file in Notepad to set the Server, Database, RowTable and ColumnTable values
3. Configure the job to Insert records in your ColumnTable
4. Enter %CONFIGFILEFOLDER%\ColumnsToRows.exe in the Post-Process setting