Using .CSV files on iseries / as400 DB2

In order to convert a .CSV file in the IFS to an AS/400 (DB2) use the ‘Copy from Import File’ command:

 

CPYFRMIMPF     FROMSTMF(‘Full path and file name with extension’)

Note:    You may need to use WRKLNK (Work with object Link) command to find the path filename and extension.

                                                TOFILE(Library/Filename)     

Note:     *LIBL is assumed by the system.

                                                MBROPT(*REPLACE)                                 

Note:     Valid values *ADD  *REPLACE  *UPDADD

                                                RCDDLM(*CRLF)                    

Note:     Carriage Return Line Feed works for .CSV files. There are other possible values.              

                                                FROMRCD(2)                                       

Note:     This parameter allows you to bypass headers. It may be set to any number.

                                                RPLNULLVAL(*FLDDFT)                             

Note:    This parameter allows you to replace null values with field default values. It should always be used when converting .CSV into DB2 files.

 

In order to convert an AS/400 (DB2) to a .CSV file in the IFS use the ‘Copy to Import File’ command:

 

CPYTOIMPF        FROMFILE(Library/Filename)     

Note:     *LIBL is assumed by the system.                         

TOSTMF(‘Full path and file name with extension’)

Note:     You may need to use WRKLNK (Work with object Link) command to find the path filename and extension.

MBROPT(*ADD)

Note:     Valid values       *ADD  *REPLACE

RCDDLM(*CRLF)

Note:     Carriage Return Line Feed works for .CSV files. There are other possible values.

DTAFMT(*DLM)

Note:    Because we intend to separate each field with a ‘;’ this parameter setting is required. There are other possible values.

STRDLM(*NONE)

RMVBLANK(*TRAILING)

Note:     To prevent transfer of non-significant blanks.

FLDDLM(‘;’)          

Note:    This the delimiter to ‘;’ rather than the default :,: this was a requirement from K + S for SAP data transfers.