When copying data from IFS file preventing errors due to nulls

I was asked to help a colleague with a problem he was having. He had a CSV, Comma Separated Variable length record, file in the IFS, and was getting errors with a program when it tried to copy the contents of the CSV file to a DDS files. The program had been running for many years without errors, until now. Looking in joblog I could see that the copy was failing due to null values in the CSV file's columns.

How is null shown in a CSV? Normally it is when two commas are next to each other: ",,"

For this post I created a DDS file, you will not be surprised to learn that I called it TESTFILE, and it contains three fields:

01 A R TESTFILER 02 A FIELD1 10A 03 A FIELD2 5P 0 04 A FIELD3 Z

Line 2: An alphanumeric, or character, field.

Line 3: A packed numeric field.

Line 4: A timestamp field.

The data I want into copy the above file is in a CSV file. This file in the IFS directory /home/Simon and is called ifs_file.txt. I deliberately did not have the "csv" extension on the file name to show that CSV data can exist in any file. The contents of the file looks like:

One,123,2027-01-01 11:41:00.000000 Two,, ,777,2027-12-31-07.14.31.123456

The first record has all three columns.

The second has a value in the first column, the second and third are null. There is not a second comma for the third column as the end of record character, which does not display, immediately follows the last comma.

In the third record the first column is null.

The Copy From Import File command, CPYFRMIMPF, is used to copy the data from the file in the IFS to the DDS file. The CL program looks like:

01 CPYFRMIMPF FROMSTMF('/home/Simon/ifs_file.txt') + 02 TOFILE(TESTFILE) + 03 MBROPT(*REPLACE) + 04 TOCCSID(37) + 05 RCDDLM(*CRLF)

Line 1: The data I want to copy from is in this file in the IFS.

Line 2: This is the file I want to copy the data into.

Line 3: Rather than have a separate command in the program to clear the output file, I can replace the to file's contents by using this parameter.

Line 4: I want the data transformed into English-US character set.

Line 5: The character at the end of each line is the Carriage Return and Line Feed character.

When I looked in the DDS file:

01 SELECT * FROM TESTFILE

Only first record had been copied:

FIELD1 FIELD2 FIELD3 ------- ------ -------------------------- One 123 2027-01-01 11:41:00.000000

When I looked in the joblog I could see errors. IMHO it is easier to read and follow the joblog messages looking at the results from the JOBLOG_INFO SQL Table function. The statement I used was:

01 SELECT MESSAGE_TEXT 02 FROM TABLE(QSYS2.JOBLOG_INFO('976848/SIMON/QPADEV0003')) 03 WHERE MESSAGE_TIMESTAMP BETWEEN '2026-10-17 17:51:20.385183' 04 AND '2026-10-17 17:52:00.000000'

Line 1: I only want to display the message text. If I wanted more detail I would include the MESSAGE_SECOND_LEVEL_TEXT column in this statement.

Lines 3 and 4: This program ran between these times, therefore, I only want to display the results from between those two.

The results were:

MESSAGE_TEXT ----------------------------------------------------------------------------------- 300 - CPYFRMIMPF FROMSTMF('/home/Simon/ifs_file.txt') TOFILE(TESTFILE) MBROPT(... Member TESTFILE file TESTFILE in MYLIB cleared. Data mapping error on member TESTFILE. Data mapping error on member TESTFILE. Data mapping error on member TESTFILE. Data mapping error on member TESTFILE. Null values not allowed in column or variable FIELD2. Data mapping error on member TESTFILE. Data mapping error on member TESTFILE. Data mapping error on member TESTFILE. Null values not allowed in column or variable FIELD1. 1 records copied to member TESTFILE. - RETURN /* RETURN due to end of CL program */

The messages inform me that there were data mapping errors due to null values, and only one record was copied.

There is an additional parameter in CPYFRMIMPF that will fix this. I only see it when I prompt the command and press the F9 to see all the parameters. It is the "Replace null values" parameter, RPLNULLVAL. Its default is "*NO", but this can be changed to "*FLDDFT" which will replace a null value with whatever the field's data type default is.

I add that parameter to my CL program:

01 CPYFRMIMPF FROMSTMF('/home/Simon/ifs_file.txt') + 02 TOFILE(TESTFILE) + 03 MBROPT(*REPLACE) + 04 TOCCSID(37) + 05 RCDDLM(*CRLF) + 06 RPLNULLVAL(*FLDDFT)

Line 7: This is the Replace Null Value parameter which will change a null value to the field's default value.

When I run this version of the CL program, I have to change the timestamp range for the JOBLOG_INFO statement:

01 SELECT MESSAGE_TEXT 02 FROM TABLE(QSYS2.JOBLOG_INFO('976848/SIMON/QPADEV0003')) 03 WHERE MESSAGE_TIMESTAMP BETWEEN '2026-10-17 18:02:00.000000' 04 AND '2026-10-17 18:03:00.000000'

Lines 3 and 4: Timestamps changed.

The results are:

MESSAGE_TEXT ----------------------------------------------------------------------------------- 300 - CPYFRMIMPF FROMSTMF('/home/Simon/ifs_file.txt') TOFILE(TESTFILE) MBROPT(... Member TESTFILE file TESTFILE in MYLIB cleared. 3 records copied to member TESTFILE. - RETURN /* RETURN due to end of CL program */

Which shows no errors, and three records were copied to the to file.

I can check the contents of the to file with the following SQL statement:

01 SELECT * FROM TESTFILE

The results show three records.

FIELD1 FIELD2 FIELD3 ------- ------ -------------------------- One 123 2027-01-01 11:41:00.000000 Two 0 2026-10-17 18:02:08.722876 777 2027-12-31-07.14.31.123456

In the second record FIELD2 contains zero, as that is the default for a packed numeric field. FIELD3 contains the timestamp value of when the record was copied to the file.

FIELD1 of the third record is blank as that is the default for an alphanumeric field.

Without making this change to the CL program the CSV would have to be changed to ensure that every column in every record contained something. This simple change to the CL program means that no matter which field in the CSV is null it is automatically handled.

 

You can learn more about the CPYFRMIMPF command from the IBM website here.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.