Column Normalizer Utility

This is a spread sheet import output related utility.

If you have a Rochade data loader  that loads from an Excel Spreadsheet or a normal tab separated file, or if you have a Rochade exporter that exports to Excel or tab separated files, you sometimes have this common problem:

Suppose each Item is loaded into Rochade via a single row in the spreadsheet.  The cell containing the links for the link attributes contains multiple links. 

You want to export this data to another system, possibly SQL, but it wants only one line per link, not a line with multiple links.  The output can then be processed to be SQL insert statements or read directly by a SQL data loader. Or you can write a script to convert this output to whatever format you would like.

How do you normalize this file, where each single row representing an Item can be broken down into multiple rows for the same Item, but containing only one link per link attribute cell?

Run this Column Normalizer utility. 

The a.tsv file loads ITEM_1, which could be a table or a column.  It has a link attribute that has multiple links, in this case to Items named link1, link2, and link3.  (This is just for illustration purposes).  Item ITEM_1 has 3 links.  Item ITEM_2 has two links from its link attribute. The goal is to make each link attribute cell contain only one link per row of the spreadsheet.

Note that two Items are being loaded in the top Excel window.  Each link attribute cell has multiple links.  After running this utility, each Item's line is duplicated so that it's link attribute cell now only has one link.

If your input loader file contains the standard

->ITEM_TYPE ITEM_NAME (optional link qualifier)

lines, the output will look like:

Analysis of the Two Types

The first example has only the name of the Item the link goes to.  This is when you know the ItemType that the link points to.  This is true when there is only one link target ItemType for that link attribute.

If, however, you have defined multiple target link ItemTypes, then you will probably want to keep the ItemType name in the file.

Link qualifiers are of course optional when entered, but they may be necessary when exporting this data to another system.

Contents of Column_Normalizer.zip

It contains these files:

• Column_Normalizer.exe - the Windows executable
• a.tsv - input whose output is b.tsv. Has only link Item Name.
• b.tsv - output. Created from a.tsv.
• c.tsv - input whose output is d.tsv. Has link ItemType and Item Name
• d.tsv - output. Created from c.tsv

Command Syntax:

Open a DOS Prompt Window

If you enter only "Column_Normalizer", it will print its version and instructions.

To run the normalizer to convert the data, use this command:

Column_Normalizer infile outfile

Example:

Column_Normalizer a.tsv b.tsv

File a.tsv will be read, and b.tsv will be created.

Comments:

This utility can handle spreadsheets with 2 or 3 columns.

RoAccess and RoLoader use standard spreadsheet file formats, so this utility is compatible.

Download This Utility

Click this URL:

  http://www.roaccess.com/rochade_products/docs/Column_Normalizer.zip