I needed to export some data from JDE on an IBMi to CSV files. It should have been simply a case of extracting the data to a temporary table and then using the CPYTOIMPF CL command to copy it down to a CSV file in the IFS, zip up all the CSV files and copy of to another system.
It actually all looked OK when I did this. I set the CPYTOIMPF format to be *PCASCII, it all worked and looked great on the IBMi when I used EDTF to check the contents. I then copied the CSV file off to a PC and tried to open it in Excel and found the file was rubish. For some reason CPYTOIMPF had ignored the request to generate a *PCASCII file and had instead created an EBCDIC file in the IFS.
CPYTOIMPF STMFCCSID parameter doesn’t work properly.
If you look hard enough in the IBM manuals it mentions that STMFCCSID parameter is ignored if the source DB/2 table doesn’t have a CCSID of 65535. So I created my temporary table with a CCSID of 65535 and tried again and it still didn’t work – so not sure what’s going on with that parameter.
I then tried a different approach based on my knowledge of how the underlying C libraries for when setting CCSID on IFS files. I created an empty CSV file with the right CCSID. I did this initially by exporting an empty sheet from Excel to a CSV and uploading the file to the IFS on the IBMi.
How to make CPYTOIMPF output to a ASCII CSV file correctly
I found that the file I had uploaded had a CCSID of 1252. I then used the CPYTOIMPF command to export to that filename but with an ADD rather than a REPLACE, and it worked, the data was perfectly in ASCII CSV format.
I then put this together into a few CL commands so I can run the process repeatidly. Here are the steps I use to automate the download:
EXESQL STMT(‘Select * from F0911 where GLCO in +
(”00031”,”00032”,”00033”,”00034”)’) +
OUTPUT(*OUTFILE) OUTFILE(MYDEV/EXF0911)
DEL OBJLNK(‘/home/me/export/f0911.csv’)
MONMSG CPFA0A9
QSH CMD(‘touch -C 1252 /home/me/export/f0911.csv’)
CPYTOIMPF FROMFILE(MYDEV/EXF0911) +
TOSTMF(‘/home/me/export/f0911.csv’) +
STMFCCSID(*PCASCII) RCDDLM(*CRLF)
Note: The EXESQL command is the old RUNSQLSTM command renaled so it doesn’t conflict with the IBM version which was added around 7.3. We’ve used this for years, I think it was published in Midrange Computing or News/400 back in the days of OS/400 V3 or V4 before most of us had SQL and long before IBM added a native RUNSQL command – which they still haven’t really got right. In summary the EXESQL command passes a big string into the RUNQMQRY command containing the SQL statement. That’s great as it supports Output options of Display, Print and *OUTFILE. I might have to update it to support an option of *CSV and bring it up to date. In the meantime there are numerous ways to extract data into a temporary table, including CPYF or building a logic view.
If you have any questions do please let me know.
0 Comments