CODEWITHSUNDEEP
eclipsedb2etl
Jess
Jess

Reputation: 25079

How do you import a spreadsheet into DB2?

I have a CSV file, but this could apply to any txt, data, or xls file. (xlsx) I have exported the data from one source and I want to import the data into a DB2 table.

I first tried Data Tools Plugin (DTP) in eclipse Helios (3.6.3) by right clicking on the table and selecting: Data > Load...

But I got this error:

Loading "myschema"."mytable"... com.ibm.db2.jcc.am.SqlException: [jcc][10103][10941][4.14.113] Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null Data loading failed.

Then I tried Eclipse SQL Explorer on Eclipse Juno, but it does not support data import.

How do I get past this error so I can import?

Upvotes: 1

Views: 25870

Answers (5)

Jonyx4
Jonyx4

Reputation: 156

From the db2 console, try this:

Import from 'yourcommaseparatedfile.csv' of del insert into "SCHEMA"."TABLE"

Regards =)

Upvotes: 2

Vikky
Vikky

Reputation: 1233

db2 'import from /users/n0sdsds/test.csv of del insert into ENTPRISE.tmp_x'

Upvotes: 0

zawhtut
zawhtut

Reputation: 8561

You can import using DB2 "Control Center" *

Right Click the table and select "Import"

enter image description here

Then specify the csv file and message file message file is important because in the case of failed upload, you can find the error cause in the message file

enter image description here

* Control Center is now deprecated in favor of "Data Studio"

Upvotes: 2

AngocA
AngocA

Reputation: 7693

You can import a CSV file directly into DB2 via the IMPORT or LOAD command, even with XML or BLOB as part of the data to import.

The procedure to import depends on the structure of the file you are going to import. Probably you should modify the default behaviour of these commands; DB2 has many option to adapt the command to the input file.

For more information about:

I think your question was more oriented to: how to use Eclipse to import data in DB2 from a CSV file. However, as I said, you can do that directly via DB2.

If you are going to import a file like the next one, the only thing that you need is to have access to a db2 client.

data.txt

1,"Andres","2013-05-18"
2,"Tom","2011-04-16"
3,"Jessica","2002-03-09"

You import with

db2 import from data.txt of del insert into test

Upvotes: 3

Jess
Jess

Reputation: 25079

I solved this by installing Eclipse Juno (4.2) and Data Tools Plugin (DTP) 1.10.2.

Now Data > Load... will work fine. This is the new message I get:

Data loading was successful. 142 row(s) loaded. 135 row(s) could not be loaded.

com.ibm.db2.jcc.am.go: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC= , DRIVER=4.7.85 One or more values could not be set in the following column(s): USER_TIME, USER_DATE

FYI for the entire process I was using this:

  • DB2 driver: /opt/IBM/db2/V9.7/java
  • With jar files: db2jcc4.jar, db2jcc_license_cisuz.jar
  • Driver Class: com.ibm.db2.jcc.DB2Driver

Upvotes: 2

Related Questions