Transfer a PF into Excel with column headings instead of fields
Is it possible that when I transfer a file from a PF on the iSeries to Excel that I could get the column headings for each field instead of the fields names? Currently using rpgivtools commands cpytormtf/sndftpf to download to network drive. Suggestions?
Software/Hardware used:
Software/Hardware used:
ASKED:
November 17, 2006 10:03 AM
UPDATED:
December 25, 2009 6:38 AM
Answer Wiki:
I've used a CL Program in the past to copy files to a .CSV format. The writers name is Herb Bujak and you can get a copy of the program here. http://search400.techtarget.com/tip/1,289483,sid3_gci873462,00.html
I hope this helps.
Scott
=====================================================
If the question is simply how to transfer files for use by Excel with field names in the first row, the simplest option is probably a iSeries Access file transfer request in .DIF format. You might even create a VIEW that exposes the fields as you'd like them along with descriptive column names.
If it must be initiated from the iSeries, you might use RUNRMTCMD to execute it. But once the transfer request is created, it's usually easy enough for the user to initiate.
As a bonus, .DIF files also work great to transfer to Open Office Calc.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
This is how I got around the same issue. I created a spreadsheet with the headings I wanted and save it as csv. Then when I do my file transfer I just copy this excel spreadsheet to the location that the physical file is going. But I used the cpytoimpf with the comma separated and append option. Then I created a Physical file to hold these values and created a ILE program to process it. This a little manual but the only way I could do it. If you find an easier way please let me know please.
This is my workaround to the field name limitation. Column headings for my spreadsheet are stored on the i5 in a file F55001B which I maintain with DFU.
I copy the heading row (row 1) with the command:
CPYTOIMPF FROMFILE(F55001B) +
TOSTMF(‘/QNTC/server/rmaO/text.TXT’) +
MBROPT(*REPLACE) +
STMFCODPAG(*PCASCII) +
RCDDLM(*CRLF)
I append data to this text file with CPYTOIMPF MBROPT(*ADD)
In Microsoft Excel, on menu bar, select Data > Import External Data, and define an import from the text.txt file. Users can then refresh this Excel worksheet by clicking the red Exclamation point(!) icon.
EricWL