You can also use the CPYFRMIMPF command with CSV files and fixed width files. Fixed width files require that you create an additional text file to define the sizes and locations of the fields. It's a bit of a hassle getting the additional text file in the correct format. But it does work.
The CPYTOIMPF command lets you do the opposite: take data from a DB2 file and create a stream file (e.g., CSV file, tab delimited file, fixed width file).
Don't forget you can transfer data from stream files to DB2 files--and vice versa--using client access.
http://as400questions.blogspot.com/2009/08/tips-copying-data-using-client-access.html
Plus there are third party tools that can help. We have a third party tool that lets us define a SQL query over DB2 files, write the output to a true Excel file (xls format), and email it to a user with a single CL command.
Thanks,
Kelly Cookson
Senior Programmer/Analyst
Dot Foods, Inc.
217-773-4486 x12676
www.dotfoods.com
-----Original Message-----
From: cobol400-l-bounces@xxxxxxxxxxxx [mailto:cobol400-l-bounces@xxxxxxxxxxxx] On Behalf Of Nancy Barney
Sent: Monday, June 07, 2010 4:34 PM
To: COBOL Programming on the iSeries/AS400
Subject: Re: [COBOL400-L] READING A TAB DELIMITED FILE IN ILE COBOL
Thanks, Kelly. This is probably what I need. We're all new to AS400
here. I'll try it and let you know if it worked.
Nancy Barney
IT - Programmer
Dakota Drug, Inc.
-----Original Message-----
From: cobol400-l-bounces@xxxxxxxxxxxx
[mailto:cobol400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kelly Cookson
Sent: Monday, June 07, 2010 4:10 PM
To: COBOL Programming on the iSeries/AS400
Subject: Re: [COBOL400-L] READING A TAB DELIMITED FILE IN ILE COBOL
If no one can help you with the UNSTRING command, you could try
importing the tab delimited file into a DB2 file first. Then process the
DB2 file.
The CPYFRMIMPF command imports tab delimited files into DB2 files.
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/c
l/cpyfrmimpf.htm
Thanks,
Kelly Cookson
Senior Programmer/Analyst
Dot Foods, Inc.
217-773-4486 x12676
www.dotfoods.com
-----Original Message-----
From: cobol400-l-bounces@xxxxxxxxxxxx
[mailto:cobol400-l-bounces@xxxxxxxxxxxx] On Behalf Of Nancy Barney
Sent: Monday, June 07, 2010 3:53 PM
To: COBOL400-L@xxxxxxxxxxxx
Subject: [COBOL400-L] READING A TAB DELIMITED FILE IN ILE COBOL
I am trying to read a tab delimited file, which was created by Excel.
This ILE COBOL Version 6, Release 1.
I have the tab character in working storage defined as:
01 WS-HEX-TAB PIC X(01) VALUE X"09".
This is the unstring statement:
UNSTRING WS-INPUT
DELIMITED BY WS-HEX-TAB
INTO
IN-MEMBER-NUM
IN-LIC
IN-FILLER1
IN-GLN
IN-ST-DATE
IN-NAME
IN-ADDR1
IN-ADDR2
IN-CITY
IN-ST
IN-FILLER2
IN-ZIP
IN-PHONE
IN-DESC
IN-REG
Etc.....
It acts like it does not recognize the tab. When I go into the debugger,
the beginning of WS-INPUT is in the first field IN-MEMBER-NUM, and the
character that is supposed to be the tab character looks like a
highlighted square or box. It is not unstringing the data. The other
fields past the first field in the unstring statement are blank.
I had a similar problem with another file, which was a sequential file
with fields delimited by the "|" (vertical bar). In that situation, the
"|" character became a "]" (right bracket), and I was able to unstring
it. But I don't know what kind of character is represented by the
highlighted box.
I can't make the file comma delimited or space delimited because it has
name, description and address fields with commas and spaces.
Any help would be appreciated!
Nancy Barney
IT - Programmer
Dakota Drug, Inc.
midrange.com
