Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
Hi all,
If my end goal is to create a .csv file on the IFS for users to open in Excel, which way performs better... and RPG program that writes directly to the IFS, or an RPG program that writes to a PF and then does CPYTOIMPF to get the .csv file to the IFS?
I've done it both ways but am just curious which way performs better? This time I'm anticipating writing just a few hundred records monthly, if that makes a difference.
I like writing to the IFS directly from RPG especially when the number of fields/columns will vary, but this time the number of fields/columns will always be the same, so a PF would work.
Thanks.
Don't do any direct RPG writing to IFS files but created a routine to copy any PF to a .csv in the IFS instead. This is an easy way to let the end-user extract whatever PF they have to .csv without any help from the IT dept.
Anyway, with this method, performance is no issue as it takes only seconds to copy 100k records to the IFS.
I've never found performance to be a problem either way. With a few hundred records, it's not going to be an issue anyway. But there's definitely a lot more programming required to roll your own.
That being said, there are things you can do when you write your own, that CPYTOIMPF won't do for you. Excel sometimes interprets import data incorrectly, and there are no options to change how Excel handles the import. For example, if you have x,"0000123",y, Excel will change the value to 123, treating as a number and zero suppressing. But if that value represents an account number, that would be undesirable. CPYTOIMPF doesn't give you any way to fix this, but if you write your own, you could write it as x,="0000123",y, and it will appear correctly in Excel (it sees it as a formula that evaluates to a string literal).
Unless there are circumstances that the OP hasn't mentioned, it makes no sense to write all the code necessary to create that file directly and to create it correctly, when there is a tool available that can already do all that work.
Since I challenged IBM regarding their CPYTOIMPF command taking "centuries" to finish and my COZTOOLS CPYTOCSV taking seconds, they completely rewrote their command. It performs just as well as CPYTOCSV, and in v7r1 TR6, they added an ORDER By parameter and finally after 10 years of CPYTOCSV doing it, IBM finally added an option to insert the field names as the first row of the CSV file.
Since I don't make any money off of CPYTOCSV I don't care which version people use, but here are the pro's and cons of each implementation:
Feature | CPYTOCSV | CPYTOIMPF |
---|---|---|
Copy an DB2 to CSV | X | X |
Customized Separators | X | X |
Header Row Options | X | X (TR6 and later) |
Optionally Insert Excel Column Functions | X | |
Format Numeric Output | X | |
Tight (trimmed) quoted values | X | |
Convert Dates to Excel Dates | X | |
APIs to create CSV via RPG | X | |
Starting and End Record Selection | X | X |
ORDER BY SQL-style parameter | X (TR6 and later) |