Import data from excel to AS400 table

we need to write a simple application (VB) to read an excel file and import the data into AS400 table/file...

Anyone done a project like this...do we need to read the excel row by row and insert into AS400 row by row...is there a way to read the entire excel and insert into the database table/file all at once??
LVL 8
CamilliaAsked:
Who is Participating?
 
ghp7000Commented:
I dont think you can do that directly, although I have never tried. What we do is open Excel, save the file as csv, and then import or load it into db2. Once you have a stable text file in any format (comma delimited, space delimited, no delimiter), you can easily code in any language to insert the data into db2 tables, or call the db2 load or import api to load or import the data.
That being said, you can use Excel to read tables directly from db2. You do this by using the Import External Data choice from the Data menu of Excel.
The problems you have to watch out for are excel based text files that contain data that is not consistent with db2 datatypes. For example, if your decimal datatype in Excel contains the $ sign, then you will have to code appropiately. Dates can also pose problems for db2 when converted from Access or Excel. You also have to watch out for char or varchar lengths as well as string fields that contain what db2 considers to be delimiters, like the comma or period or double quotes.
0
 
Kent OlsenData Warehouse Architect / DBACommented:

If this is a one-time thing (so that you don't need an on-demand application) it's probably easiest to open Access, import the spreadsheet, and export the data to DB2.


Kent
0
 
CamilliaAuthor Commented:

no, not a one time things, my manager wants an application ...
and why Access??

we want ...export from AS400 to Excel
                import from Excel to AS400

not easy to do?? does it need row by row processing if VB is used?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Kent OlsenData Warehouse Architect / DBACommented:

Sorry -- I'm not a VB coder so I can't help much here.

I suggested Access because it can read the Excel spreadsheet and connect (via ODBC) to DB2.


Kent
0
 
CamilliaAuthor Commented:
yeah, this has to be done thru code...

I guess my main question is if importing back from excel to the database...if that has to be done row by row ...
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Neither Excel not DB2 make that requirement so it would depend on the tools available in VB.

0
 
CamilliaAuthor Commented:

found this:

http://support.microsoft.com/kb/321686

this is for sql server but i think it can be changed to connect to DB2 and do the importing to db2...

"You do this by using the Import External Data choice from the Data menu of Excel. "...we want this to be an application to give to our clients ...something with interface to it...
0
 
CamilliaAuthor Commented:

and I wonder if I can use: rfrompcb.exe and rtopcb.exe
0
 
ghp7000Commented:
db2 doesnt have DTS, so forget that.
RTOPCB looks possible, but that hardly qualifies as GUI interface.
I think your best bet is to code application that depends upon text readable file
0
 
CamilliaAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.