insert data from a file

jsaravana
jsaravana used Ask the Experts™
on
Hi,

I need to insert data from csv file and need to do some validations.
import is not working inside stored procedure.
load might create integrity issue.
Is there a way to use insert command in this?. Any method to read csv data n insert to table using insert command?.

I need to catch the exceptions if any error occurs n throw back the error. So if there is any way to use insert command it would be gr8!

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

$ cat a.csv
a,b
a,3

$ db2 connect to shoa2
$ db2 "create table a ( a char,b int )"

$ db2 import from a.csv of del modified by coldel, messages a.log replace into a
Number of rows read         = 2
Number of rows skipped      = 0
Number of rows inserted     = 2
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 2

SQL3107W  There is at least one warning message in the message file.

$ cat a.log
SQL3109N  The utility is beginning to load data from file "a.csv".

SQL3119W  The field value in row "1" and column "2" cannot be converted to an
INTEGER value.  A null was loaded.

SQL3110N  The utility has completed processing.  "2" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "2".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "2" rows were processed from the input file.  "2" rows were
successfully inserted into the table.  "0" rows were rejected.

$ db2 "select * from a"

A B
- -----------
a           -
a           3

  2 record(s) selected.
and if all data are legal, you will not get warning
SQL3107W  There is at least one warning message in the message file.


$ cat a.csv
a,1
a,3

$ db2 import from a.csv of del modified by coldel, messages a.log replace into a

Number of rows read         = 2
Number of rows skipped      = 0
Number of rows inserted     = 2
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 2

Author

Commented:
Hi experts,

I am creating this on windows so AIX commands wont work.
Also, I wont be able to use import inside stored procedure. I would like to catch the errors and post back to the front end. Exception handling can be done inside stored procedure only. So please suggest the best way to do this.

you could try this:
create another table with a single varchar column
load the file into that table as a single column using the load utility (this will never cause errors)
use your procedure code to parse the values of the rows of table you have loaded and perform insert to the target table

Author

Commented:
Hi Experts,
I found out a way to use import in stored procedure.
CALL SYSPROC.ADMIN_CMD('import from C:\testfield.csv of del insert into administrator.test_title');

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial