Handling pipe separated data into a Db2 file

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.