www.fireflysoftware.com

Translate Quoted Comma Delimited to Fixed Width

Suppose we wanted to translate the following quoted, comma-delimited data to fixed-width:

"54667","""Tiny"" Tim","05/05/1980","M",20456.45,"2347281749"
"98798","Luther, Lex","05/26/1940","M",9834.01,"4566281737"
"67491","Betty Boop","05/26/1932","F",34772.01,"1902630722"

The first step in doing this is to parse all of the comma-separated fields onto separate lines. We could use the ReplStr filter to replace all commas (,) with carriage return / linefeeds, in-effect forcing all fields onto separate lines; however, any of the string fields could contain a comma (as for example, the name "Luther, Lex" in the second line) and therefore this isn't an ideal solution. A better approach is to use the ParseCSV filter instead. 

Sending the above data through the one-filter pipe,

ParseCSV

outputs each field onto a separate line:

"54667"
"""Tiny"" Tim"
"05/05/1980"
"M"
20456.45
"2347281749"
"98798"
"Luther, Lex"
"05/26/1940"
"M"
9834.01
"4566281737"
"67491"
"Betty Boop"
"05/26/1932"
"F"
34772.01
"1902630722"

Adding 3 more filters to the pipe gives us:

ParseCSV
QuoteLines 1 6 /s6 /u
PadLinesRight ' ' /s6
JoinLines 6

The /U switch used by the QuoteLines filter is needed to unquote each line, (i.e. remove the outer quotes surrounding each line and then restore any embedded quotes). Finally, the parsed and unquoted fields are padded with blanks using PadLinesRight and then re-joined into fixed-width lines using JoinLines. The final output is:

54667"Tiny" Tim 05/05/1980M20456.452347281749
98798Luther, Lex05/26/1940M9834.01 4566281737
67491Betty Boop 05/26/1932F34772.011902630722
----+----1----+----2----+----3----+----4----+----5

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

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

Copyright © 2005 Firefly Software