DB2 import CSV files

Import csv files

 

1) Open the DB2 command line

2) If this is the first time you connect to a remote DB2 database; if not, go directly to 3)

Run the following command in the DB2 command line window:

Catalog tcpip node DB21 remote 10.22.140.226 server 50000

Catalog database safemon as SAFEMON at NODE DB21

 

3) connect to a remote DB2 database and run the following command in the DB2 command line window:

Connect to safemon user db2admin using test @ 1234

 

4) if the imported file has not created a response data table for it, run the following command in the DB2 command line window:

Create table test (COL1 VARCHAR (50), COL2 VARCHAR (50), COL3 VARCHAR (100), COL4 VARCHAR (50 ))

 

(5) imported csvfile inclusion see file excel_template.csv, please confirm that the file encoding format is UTF-8

6) execute the import command (note that there is no line feed for this statement) and execute it in the DB2 command line window:

LOAD client FROM 'f: \ excel_template.csv 'OF del modified by codepage = 1208 chardel "" COLDEL0x2c method p (,) insert into test (col1, col2, col3, col4)

This statement is explained in detail below:

L Load Client from indicates that the file is imported from the Client. If the file is directly executed on the data server, the Client keyword is removed.

L of del, del is the abbreviation OF delimeter, meaning that the specified punctuation marks are used as the separation between bars, rather than the column length is fixed.

L CODEPAGE, indicating the csv file encoding

L Chardel, which indicates the character used as the start sign in the string column. Here, it is enclosed in double quotation marks.

L COLDEL, which indicates the delimiter between spaces. Here, the comma is used as the separator of the column.

L method p, which indicates that the column data to be imported is located according to the column number,

In addition, there is method l, which indicates that it is a fixed starting position in the CSV file by column, for example

Method p (001 020,022 030,032 045,047 100)

L insert into: Specifies the table to be inserted and the corresponding field.

 

7) Remove the title Header

Delete from test where col1 = 'application No. 'and COL2 = 'application date'

 

8) check whether the import is successful

SELECT * from test fetch first 5 ROWS ONLY