Problem solve Get help with specific problems with your technologies, process and projects.

Transferring spreadsheet data with blank cells to DB2

This tip shows you how to transferring spreadsheet data with blank cells to DB/2.

If you have to import a spreadsheet to your AS/400 which is in .CSV format, you can use CPYFRMIMPF. However, this command will not work if any of the fields in the .CSV file are blank or zero (the record looks something like this:

73,"1","1","","4",0,0,"","W 13","","",0   

To get around this limitation, define another physical file, using DDS, identical to the one you want to copy to, except add the ALWNULL keyword to all fields which could be blank or zero.

I use the FTP client on my PC to transfer the spreadsheet, which is in .CSV format, to a flat file (which you can let FTP create for you). Then, I use the CPYFRMIMPF command to copy the flat file to the file I created with the null capable fields (I use the default delimiters of a double quote for STRDLM, and a comma for FLDDLM).

Then, I copy the records from that file to my "regular" file (the one without the null-capable fields) with CPYF FMTOPT(*NOCHK). The data will be in the non-null-capable-field file correctly. The null fields in the .CSV will be blank or zero.

Note: The companion command CPYTOIMPF will not produce a file with null fields in it; numeric fields with zero values will have a zero, for example, an amount field would have ".00 ", and blank character fields will have blank spaces filling the length of the field).


This was last published in July 2001

Dig Deeper on RPG iSeries programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.