IBMi (AS400) fans only ' How to import a CSV file into db2

#IBMiSample

Let's try with a very simple csv like this, called file1.csv:

Code;Name

3741;Joe
3742;Barth
3810;
3945;

First of all we need to save file1.csv into ISF.

In my PUB400 (free AS400 at pub400.com) I have this directory:
/home/PALDO

I copy file1.csv to /home/PALDO

Use standard FTP transfer software (I use WinScp, I think Filezilla will work too)

Let's take a look inside my directory





I type WRKLNK and I verify the existence of File1.csv





It looks as expected:

To import this file I need a similar table on DB2.

In my example it means that I will create a table with a decimal field and a charachter field.

I create the table TEMPCSV (PALDO1 is my Library):







CREATE TABLE PALDO1.TEMPCSV (
CLCCL0 DECIMAL(7, 0) DEFAULT 0 ,
CLNOM0 CHAR(50) DEFAULT ' ' )
RCDFMT FMTXX


Here's my new table TEMPCSV:

The table TEMPCSV is now created and it is empty:

We can optionally set up a source file to note lines not imported by the CPYFRMIMPF command. In my opinion it is very useful.

Type
CRTSRCPF FILE(PALDO1/IMPERRORS) MBR(IMPERR) TEXT('Errors CPYFRMIMPF')
to create a source file. PALDO1 is my Library.

To fill TEMPCSV with File1.csv data we will use CPYFRMIMPF command:

Type:
CPYFRMIMPF FROMSTMF('/home/PALDO/File1.csv') TOFILE(PALDO1/TEMPCSV) MBROPT(*REPLACE) RCDDLM(*CRLF) FLDDLM(';') ERRRCDFILE(PALDO1/QCLSRC IMPERR) ERRRCDOPT(*REPLACE) RMVCOLNAM(*YES)


Let's take a look at the TEMPCSV table:

SELECT * FROM TEMPCSV






Let's check the error file:

IMPERR file is empty, it means no errors, it means all lines have been imported.

That's it.

I appreciate all the comments made on this blog.