Translate Fixed Width to Quoted Comma Delimited

The following example illustrates use of the QuoteLines filter to translate fixed-width data to quoted, comma-delimited data using option 0. Given the following fixed-width data representing zip code, name, birth date, sex, account balance and account number,

54667 "Tiny" Tim 05/05/1980 M 20456.45 2347281749
87896 John Doe 01/15/1960 M 234888.56 6482610982
98798 Luther, Lex 05/26/1940 M 9834.01 4566281737
67491 Betty Boop 05/26/1932 F 34772.01 1902630722
----+----1----+----2----+----3----+----4----+----5----+----6

the pipe,

SplitLines 8 21 33 37 48
TrimLinesRight
QuoteLines 1 4 6 6 /s6
AppendStr ','
JoinLines 6
StripChars 1

outputs:

"54667","""Tiny"" Tim","05/05/1980","M",20456.45,"2347281749"
"87896","John Doe","01/15/1960","M",234888.56,"6482610982"
"98798","Luther, Lex","05/26/1940","M",9834.01,"4566281737"
"67491","Betty Boop","05/26/1932","F",34772.01,"1902630722"

Notice that every column that is in range is quoted unconditionally as per option 0. Here, we omitted the 5th column because we knew that it represented a numeric value. If we know that the target system doesn't require string fields to be quoted unless they contain embedded quotes or delimiters then, instead of the default (0), we could choose option 1 for the QuoteLines filter:

QuoteLines 1 4 6 6 /s6 /o1

This produces the following output in which only two cells are actually quoted:

54667,"""Tiny"" Tim",05/05/1980,M,20456.45,2347281749
87896,John Doe,01/15/1960,M,234888.56,6482610982
98798,"Luther, Lex",05/26/1940,M,9834.01,4566281737
67491,Betty Boop,05/26/1932,F,34772.01,1902630722

Using option 1, we no longer need to specify particular columns to act on because now only those cells are quoted that have embedded quotes or delimiters; therefore, we could have the filter act on all 6 columns by specifying a single range that covers all of them:

QuoteLines 1 6 /s6 /o1

The resulting output would be identical to the previous output in this instance.

Finally, option 2 is useful when you want only non-numeric* columns to be quoted. Specifying the QuoteLines filter using option 2,

QuoteLines 1 6 /s6 /o2

gives us this output:

54667,"""Tiny"" Tim","05/05/1980","M",20456.45,2347281749
87896,"John Doe","01/15/1960","M",234888.56,6482610982
98798,"Luther, Lex","05/26/1940","M",9834.01,4566281737
67491,"Betty Boop","05/26/1932","F",34772.01,1902630722

Now, those cells in each of the 6 columns that contain non-numeric values are quoted; however, the zip code and account number fields are not quoted because they only contain numeric characters. In this case, you could choose to manually specify each and every column to be quoted (using option 0) or you could use option 2 as shown above and simply add another QuoteLines filter (option 0) to handle quoting those columns that were not automatically handled by the first QuoteLines call. The entire pipe would be as follows:

SplitLines 8 21 33 37 48
TrimLinesRight
QuoteLines 1 6 /s6 /o2
QuoteLines 1 1 6 6 /s6 
AppendStr ','
JoinLines 6
StripChars 1

Its execution gives us the desired output:

"54667","""Tiny"" Tim","05/05/1980","M",20456.45,"2347281749"
"87896","John Doe","01/15/1960","M",234888.56,"6482610982"
"98798","Luther, Lex","05/26/1940","M",9834.01,"4566281737"
"67491","Betty Boop","05/26/1932","F",34772.01,"1902630722"

Since the QuoteLines filter also works in-reverse, that is, it can unquote lines, you could use it to unquote any exceptional columns after having first quoted all columns using option 0.

For an example in which quoted, comma-delimited data is translated to fixed-width, see here. 

* To be considered a numeric value, a line must only contain the characters 0-9, a period (.) and a sign character (+,-).

[Home] [Contact Us] [Downloads] [Purchase/Register]

Copyright © 2005 Firefly Software