How to insert data from external file to db2 database using db2 import

Do you have any data in excel file . Do you want to bring those data into db2 table. Suppose I am having the below data in excel file.

1001 Akash Kumar 15000.00 ak@xyz.com
1002 Micky 60000.00 micky@xyz.com
1003 Jackson 100000.00 jack@zyz.com

          How to bring these data into db2 table. Simple you can use the command db2 import. Steps to bring those data into db2 table. Two ways are there.

First one : In excel , save the data in any folder (eg. D:\db2imp) as CSV (Comma seperated value) (emp.csv) file by selecting the file type as CSV (MS-DOS )(*.csv). Now create a table or use the existing table (employee) with the field empcode, empname, salary, email. Give the following command.

D:\db2> db2 import from emp2.csv of del insert into emp (empcode,empname,salary,email)

              Now all the records from the emp.csv are inserted. In the above statement we have used the word of del which means it will seperate the data fields by a delimiter (,) . Here the default delimiter is Comma (,).

IInd way : In excel save the file as Text files by selecting file type as txt (MS-DOS) (*.txt) . Now open the file and put comma (,) between each data in each row and issue the above command.

db2 import command is used

1. To transfer data from one db2 database table to another db2 database table or within the same database . This can be done with the help of db2 export command . First export data in a specied format by putting delimiter . Then the data is imported to another table using import command.

2. To insert data from an external file with a supported file format (csv , DEL , ixf ) into a table , view.

Now let us see how to move data from one table to another table in the same database or another database in the same or any remote system. Give the following commands

D:\db2expimp> db2 export to emp.ixf of ixf messages msgs.txt select * from emp

Copy the emp.ixf file to the system where the target database lying then issue the following command.

D:\db2expimp> db2 import from emp.ixf of ixf messages msg.txt insert into emp ;

- where msgs.txt , msg,txt are message files where error / successful messages , warnings are stored.

Some more examples :

-The default delimiter for column seperation is Comma (,) . Suppose if you are using column seperator as # (eg. 1001# Akash Kumar# 15000.00# ak@xyz.com ) , then you have to use the following command .

D:\db2expimp>db2 import from emp.txt of del modified by coldel# insert into emp (empcode,empname,salary,email)

      - where coldel is used to mention the column delimiter. The default string delimiter is double quote("). You can also change the string delimiter to any charater by using modified by chardel newcharacter; 
    eg. db2 import from emp.txt of del modified by chardel'' insert into emp (empcode,empname,salary,email).

      To substitute for the period as a decimal point character, use decptx where x the character to substitute. For example (;) instead of decimal point (.) , use decpt;

            Now let us see how to insert identity column from a text file to table independent the availabilty of identity column in the text file.

stu.txt

"abc", 400

"xyz", 300

"abc1", 450

"xyz1", 200

In the above file , no rows have identity column values. But we can insert the data into tables with identity column values by the issuing the command

db2 import from stu.txt of del modified by identitymissing replace into stu

Replace identityignore by identitymissing for inserting identity column values for those rows missing identity values in the text file.