Yet Another Way to Build CSV Files
February 2, 2005 Hey, Ted
I know offshoring gets a lot of blame for the decline in programmer positions these days, but there’s another culprit that I never hear about: the vast array of reporting and data-mining tools available to users.
Twenty years ago a programmer had to write code to build a report program. Now users create their own reports with Microsoft Excel. In my shop, that means we have lots of programs that create CSV (comma-separated values) files. Let me add a simple but effective technique to the Copy to Import File (CPYTOIMPF) command and the plethora of third-party products that create files in CSV (and other) formats.
SQL presents an easy way to create CSV files. Use the CHAR function to convert numeric fields to alpha format. SQL puts in the necessary minus signs and decimal points. Concatenate all the fields together to get one big comma-delimited output field.
The following SQL command is an example of the technique. I use Qshell to retrieve the data and load it into a file in the Integrated File System (IFS).
db2 "SELECT char(CUSNUM)||','||LSTNAM||','||INIT||','||
CITY||','||STATE||','||char(baldue) from qiws.qcustcdt"
| sed -n '/,/p' >> custdata.CSV
The CSV file looks like this:
938472 ,Henning ,G K,Dallas,TX,37.00 839283 ,Jones ,B D,Clay ,NY,500.00 392859 ,Vine ,S S,Broton,VT,439.00 938485 ,Johnson ,J A,Helen ,GA,3987.50 397267 ,Tyron ,W E,Hector,NY,.00 389572 ,Stevens ,K L,Denver,CO,58.75 846283 ,Alison ,J S,Isle ,MN,10.00 475938 ,Doe ,J W,Sutter,CA,250.00 693829 ,Thomas ,A N,Casper,WY,.00 593029 ,Williams,E D,Dallas,TX,25.00 192837 ,Lee ,F L,Hector,NY,489.50 583990 ,Abraham ,M T,Isle ,MN,500.00
It isn’t necessary to run SQL under Qshell, of course, but doing so sure makes it easy to build an IFS file.
–Just call me “Joe”
|
If you need to get rid of extra blanks, use the TRIM function. If a character field might contain commas, use the TRANSLATE function to convert them to something else.
–Ted
