tpayne51
Member
RE: Download File to Excel from batch Job
Firstly a big "Thank You" to everyone who responded on this, especially Don
- I now have the solution to the problem working thanks to your help,
although there are a few quirks that I found along the way.
To take data from an AS400 database file and create a Comma Separated File
(.csv) on the IFS:
1. Use the CPYTOIMPF command to copy the data down to the IFS (Shared
Folders) as follows:
CPYTOIMPF FROMFILE(UMIPRDDTA/F55588) TOSTMF('/qdls/email/f55588.csv')
MBROPT(*REPLACE) TOCCSID(*FILE) RCDDLM(*CR) DTAFMT(*DLM) DATFMT(*USA)
TIMFMT(*USA)
2. Open this file in Excel. If the data looks good, no problem, but if as
in my case the data is garbage because it is still in EBCDIC, you need to
perform a 2nd stage to convert the data to ASCII - see 2a below. Apparently
if you create the IFS file from the PC the codepage is correct, but if you
allow CPYTOIMPF to create the file it is created with the wrong codepage
(see extract of IBM document below). Don uses instead CPYTOIMPF to create
an AS400 delimited file, then uses CPYTOPCD to copy it to IFS, whereupon the
file is converted to ASCII. I kept getting "error reason code 11" trying to
create an AS400 file and could not find documentation to explain this, hence
my solution.
2a. To convert the data to ASCII, you need to execute the following command
to copy the file to a new object and to change the code page:
CPY OBJ('/qdls/email/f55588.csv') TOOBJ('/qdls/email/f55588a.csv')
TOCODEPAGE(437) DTAFMT(*TEXT)
----------
Document Title
CPYTOIMPF Output Cannot Be Read from the PC
Document Description
The CPYTOIMPF command may be used to create a delimited stream file from an
externally described file. If the target stream file does not exist when
running the command, the newly created stream file may not be readable from
a mapped network drive on the PC. This is because the data in the stream
file is in an EBCDIC code page. Verify this by displaying the attributes of
the stream file from the WRKLNK screens on OS/400. Note the code page.
The first solution to this problem is to create the stream file in the IFS,
from the PC, before running the CPYTOIMPF command. This causes the stream
file to be created with an ASCII code page. The translation from EBCDIC to
ASCII occurs when the CPYTOIMPF command is run.
If creating the file first from the PC side is not feasible, another option
is to use the CPY command. Create the new stream file as you normally would
using the CPYTOIMPF command. This is created in an EBCDIC code page. Then,
use the CPY command to copy this stream file to a new stream file specifying
TOCODEPAGE(*PSASCII) DTAFMT(*TEXT). The stream file created as the result of
the copy is in an ASCII code page and can be read from a mapped network
drive on the PC.
The parameter STMFCODPAG was added through APARs SA88265 and SA88341.
----------
Hope this helps.
Tony Payne
Tony Payne - Senior Systems Analyst
Selmer-UMI
Elkhart, Indiana 46516
Firstly a big "Thank You" to everyone who responded on this, especially Don
- I now have the solution to the problem working thanks to your help,
although there are a few quirks that I found along the way.
To take data from an AS400 database file and create a Comma Separated File
(.csv) on the IFS:
1. Use the CPYTOIMPF command to copy the data down to the IFS (Shared
Folders) as follows:
CPYTOIMPF FROMFILE(UMIPRDDTA/F55588) TOSTMF('/qdls/email/f55588.csv')
MBROPT(*REPLACE) TOCCSID(*FILE) RCDDLM(*CR) DTAFMT(*DLM) DATFMT(*USA)
TIMFMT(*USA)
2. Open this file in Excel. If the data looks good, no problem, but if as
in my case the data is garbage because it is still in EBCDIC, you need to
perform a 2nd stage to convert the data to ASCII - see 2a below. Apparently
if you create the IFS file from the PC the codepage is correct, but if you
allow CPYTOIMPF to create the file it is created with the wrong codepage
(see extract of IBM document below). Don uses instead CPYTOIMPF to create
an AS400 delimited file, then uses CPYTOPCD to copy it to IFS, whereupon the
file is converted to ASCII. I kept getting "error reason code 11" trying to
create an AS400 file and could not find documentation to explain this, hence
my solution.
2a. To convert the data to ASCII, you need to execute the following command
to copy the file to a new object and to change the code page:
CPY OBJ('/qdls/email/f55588.csv') TOOBJ('/qdls/email/f55588a.csv')
TOCODEPAGE(437) DTAFMT(*TEXT)
----------
Document Title
CPYTOIMPF Output Cannot Be Read from the PC
Document Description
The CPYTOIMPF command may be used to create a delimited stream file from an
externally described file. If the target stream file does not exist when
running the command, the newly created stream file may not be readable from
a mapped network drive on the PC. This is because the data in the stream
file is in an EBCDIC code page. Verify this by displaying the attributes of
the stream file from the WRKLNK screens on OS/400. Note the code page.
The first solution to this problem is to create the stream file in the IFS,
from the PC, before running the CPYTOIMPF command. This causes the stream
file to be created with an ASCII code page. The translation from EBCDIC to
ASCII occurs when the CPYTOIMPF command is run.
If creating the file first from the PC side is not feasible, another option
is to use the CPY command. Create the new stream file as you normally would
using the CPYTOIMPF command. This is created in an EBCDIC code page. Then,
use the CPY command to copy this stream file to a new stream file specifying
TOCODEPAGE(*PSASCII) DTAFMT(*TEXT). The stream file created as the result of
the copy is in an ASCII code page and can be read from a mapped network
drive on the PC.
The parameter STMFCODPAG was added through APARs SA88265 and SA88341.
----------
Hope this helps.
Tony Payne
Tony Payne - Senior Systems Analyst
Selmer-UMI
Elkhart, Indiana 46516
