It started with a question:
If my file.txt is pipe filed separated, how can I make the records fall into the physical file to the corresponding fields.
A good question. Most of the time my incoming files are comma separated. The same methods I would use for those will work for pipe separated files too.
First I need a file with pipe separators. I made a copy of some of the columns from my PERSON DDL table to an output file in QTEMP:
01 CREATE TABLE QTEMP.OUTFILE AS 02 (SELECT PID,LNAME,FNAME FROM PERSON) 03 WITH DATA |
I could then copy the contents of the output file to the IFS using the Copy To Import File command, CPYFRMIMPF:
01 CPYTOIMPF FROMFILE(QTEMP/OUTFILE) +
02 TOSTMF('/home/MyDirectory/output_file.csv') +
03 STMFCCSID(*PCASCII) +
04 RCDDLM(*CRLF) +
05 FLDDLM('|')
|
Line 5: The Field Delimiter parameter is a pipe ( | ), therefore, a pipe is used as the separator character rather than the comma.
I can view the contents of the file in the IFS using the IFS_READ SQL Table function:
01 SELECT LINE
02 FROM TABLE(QSYS2.IFS_READ('/home/MyDirectory/output_file.csv'))
03 LIMIT 5
|
Line 1: The columns LINE is the data from the IFS file. It has rows of data as I gave a record delimiter code when creating the file.
Line 5: I am only going to show the first five rows of data.
LINE --------------------------------- 1 |"ALLEN"|"REG" 2 |"CROMPTON"|"JACK" 3 |"BYRNE"|"ROGER" 4 |"CAREY"|"JOHNNY" 5 |"MCNULTY"|"THOMAS" |
I created a copy of the output file to create an input file, INFILE. I now needed to copy the data from the IFS file into INFILE. I would use the Copy From Import File command, CPYFRMIMPF:
01 CPYFRMIMPF FROMSTMF('/home/MyDirectory/output_file.csv') +
02 TOFILE(QTEMP/INFILE) +
03 MBROPT(*REPLACE) +
04 RCDDLM(*CRLF) +
05 FLDDLM('|') +
06 ERRRCDOPT(*REPLACE)
|
Line 5: Notice that the Field Delimiter character is a pipe.
When this copy completed, I can then use the following SQL statement to check the contents of INFILE:
01 SELECT * 02 FROM QTEMP.INFILE 03 LIMIT 5 |
Line 3: I only want the first five rows.
The results are:
PERSON_ID LAST_NAME FIRST_NAME
--------- --------- ----------
1 ALLEN REG
2 CROMPTON JACK
3 BYRNE ROGER
4 CAREY JOHNNY
5 MCNULTY THOMAS
|
As I have shown the process to copy pipe delimited files into a Db2 file is as simple as I would do for comma separated ones.
This article was written for IBM i 7.6, and should work for some earlier releases too.
Please note that since the pipe symbol is a variant character (i.e., the Pipe character's hex value varies depending on the Code Page/CCSID) you can get unpredictable results when using it as a field delimiter.
ReplyDeleteI know this from experience...
When I was working for a multinational eye care company the European division used CCSID 500 as the default CCSID on their IBM i system, but we had used 37 as the default CCSID on the US-based IBM i system.
They were generating pipe-delimited files and sending to the US system for processing.
In US English Code Page 37 the pipe character '|' is X'4F'
In UK Code page 500 the pipe character is X'BB'
These differences can cause problems with CPYFRMIMPF when parsing the delimiters.
Regards,
SJL
Thank you for the reminder that CCSID can cause problems with characters like the pipe.
DeleteYou should always test on all of your IBM i systems to make sure that everything is compatible.