LRH Enterprises

Once in a while you are given data in a format incompatible with your requirements. I was recently given an excel spreadsheet, and a format it needed to be converted to for use.  After a little analysis, I created the Convert_Me program.

It’s a multi-step process, but it gives you the flexibility to fine-tune each conversion, and create as many conversions as you like.

The steps are:

1. Convert excel file to comma delimited text (csv)
2. Determine the fields from the text file, and where they belong in the new file
3. Use that information to create a conversion control file
4. Run Convert_Me
5. Enjoy the new data

The hardest part of your job is analyzing the data and creating the control file. Here’s a sample, based on the file I was given.

descr,rtl upc,srp
DUNHILL F/C BLACK BOX FSC,027200599219,92.87
DUNHILL F/C GREEN(MN)BOX FSC,027200599271,92.87
DUNHILL F/C WHITE(LT)BOX FSC,027200599240,92.87
DUNHILL INT'L RED FSC,027200356843,92.87
DUNHILL INT'L GREEN(MN)FSC,013760000128,92.87
DUNHILL INT'L BLUE(LT) FSC,013760000166,92.87

The sample CSV data shows that the first field is the description, the second the PLU, and the third the price. It also shows that the first row is the column names from the excel file. We want to ignore that row, so in the control file we add a skipstart=1, indicating we want to skip one row at the start of processing.

[SETTINGS]
; indicates how many lines at the start of the
; input file to skip
SkipStart=1

The sample output file I was given contained a line already generated with all the defaults the client wants set on each new entry created from the csv file. We add that to the template section of the control file.

[TEMPLATE]
; Default output line for each converted row
; (shown wrapped here for clarity, do not use plus signs
; in real file)
DEFAULT="A12345678901234567 DESCRIPTION234567890010001"
+"00000000000000000                 000000000110000000"
+"000010000000000000000000000000000000000000000"

The sample is shown wrapped so it’s easier to read in this article. The line should be specified as DEFAULT=”data”, that is the default data line between double quotes.

Next, we have to define the incoming fields and where they go in the output. Each field will be merged with the default, then output to the new file.

The description goes into the output line starting at column 20 for a length of 20 characters. If the incoming description is shorter than 20 characters, we want to pad on the right side with spaces. That translates into the following control entry:

;DESCRIPTION
; First field in input file
; Insert into position 20 for length 20, left justified
; and space filled
1=20,20,L," "

The entry is added to the TEMPLATE section. The format of the control line is:

InputFieldNumber = StartingPosition,Length,Justification,FillChar,StripChars

InputFieldNumber

StartingPosition

Length

Justification

FillChar

StripChars

As you can see, all the parameters are required except StripChars. Note that if the field is LEFT justified, the fill characters, if needed, are added on the right when the input data is too short. If the input data is too long, it is cut off at the indicated length.

Let’s look at the controls for the two remaining input fields:

;PLU
; Second field in input file
; Insert into position 2 for length of 17, right justified
; and zero filled
2=2,17,R,"0"

;PRICE
; Third field in input file
; Insert into position 93 for length 8, right justified,
; zero filled, periods removed
3=93,8,R,"0","."

For the PLU, we want to fill on the left side with zeros. The price is output with an implied decimal point, so we remove the period from the incoming data using the optional StripChars field control. This assumes that the price always arrives in the input file with the ‘cents’ part always being two digits as in “1.23” and not “1.2”.

To run Convert_Me, you use a command line prompt, passing three parameters:

Convert_Me control_file_name input_file_name output_file_name

For example:

Convert_Me CONTROL_ME.INI TEST.CSV TEST.PLU

Here’s the output generated from our sample data, wrapped into three lines each for clarity:

A00000027200599219 DUNHILL F/C BLACK BO010001000
00000000000000                 00000000011000000
9287010000000000000000000000000000000000000000

A00000027200599271 DUNHILL F/C GREEN(MN010001000
00000000000000                 00000000011000000
9287010000000000000000000000000000000000000000

A00000027200599240 DUNHILL F/C WHITE(LT010001000
00000000000000                 00000000011000000
9287010000000000000000000000000000000000000000

A00000027200356843 DUNHILL INT'L RED FS010001000
00000000000000                 00000000011000000
9287010000000000000000000000000000000000000000

A00000013760000128 DUNHILL INT'L GREEN(010001000
00000000000000                 00000000011000000
9287010000000000000000000000000000000000000000

A00000013760000166 DUNHILL INT'L BLUE(L010001000
00000000000000                 00000000011000000
9287010000000000000000000000000000000000000000

That’s all there is to it!