MVSFORUMS.com

MFdigger · Posted: Mon Jan 25, 2010 5:32 pm Post subject: loading into DB2 from xls format

Hi All,

I have an xls file with the data that I have to upload into DB2 table. Initially I have to upload the data into mainframe and then into DB2 table.

the format of some of the fields are not matching with the DB2 columns (for example date format, Decimal format)

I have to convert them into correct format and do the upload into DB2 table. Could some one please help on this

When I searched the forums, I have seen some one pointing to pearl script, I dont know how to use it. Appreciate your help

Thank you

Rolling Eyes

_________________
Tx
Digger

papadi · Supermod Joined: 20 Oct 2009 Posts: 594 Topics: 1

One way is to:
save the .xls data to a tab-delimited text file,
upload the text file to the mainframe,
reformat any fields that need reformatting (using sort or some other process),
load the reformatted data into db2.
_________________
All the best,

di

kolusu · Posted: Mon Jan 25, 2010 5:42 pm Post subject:

MFdigger,

1. Save the excel file as "comma-separated fields", with double quotes as the text delimiter (only for text, not for numbers).
2. If your using DB2 v8 and higher you can use the FORMAT option of the LOAD utility

ex:

MFdigger · Posted: Tue Jan 26, 2010 2:39 pm Post subject:

Hi Kolusu,

Thank you for your immed response. In DB2 table we have a field defined as (5,3)

Eg: PLM DECIMAL(5 , 3) NOT NULL

so, while formating the xls file into the dataset , how the value should be

for eg: if its 9.5
does it should show as ' 9.500' or ' 9.5'?
_________________
Tx
Digger

MFdigger · Posted: Tue Jan 26, 2010 2:42 pm Post subject:

for example:

if the dataset is like

A char (2), B char(3), PLM decimal(5,3)

then does the dataset also should appear lke

kolusu · Posted: Tue Jan 26, 2010 3:48 pm Post subject:

MFdigger,

it should be

Dibakar · "blah blah one",772,-232,3.12,"2010-01-25"

kolusu · Posted: Thu Jan 28, 2010 12:32 pm Post subject:

Dibakar,

Date is nothing but a Char(10) field. So you need the quotes.

Kolusu

Dibakar · Posted: Wed Feb 03, 2010 8:24 pm Post subject:

Kolusu,

I looked at Exampl 5:Loading data in delimited file format at http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.ugref/db2z_loadsamples.htm and it didn't have quotes. I was wondering if it is just optional. Sorry, I couldn't test it.