Solved

Import data from excel to AS400 table

Posted on 2006-11-15
10
2,850 Views
Last Modified: 2011-08-18
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??
0
Comment
Question by:Camillia
  • 5
  • 3
  • 2
10 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 17947955

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
 
LVL 7

Author Comment

by:Camillia
ID: 17948070

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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 17948124

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 7

Author Comment

by:Camillia
ID: 17948179
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 17948209

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

0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 500 total points
ID: 17949589
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
 
LVL 7

Author Comment

by:Camillia
ID: 17949632

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
 
LVL 7

Author Comment

by:Camillia
ID: 17949792

and I wonder if I can use: rfrompcb.exe and rtopcb.exe
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17949988
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
 
LVL 7

Author Comment

by:Camillia
ID: 17950246
thanks
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
teradata sql olap functions 2 286
SELECT * FROM [Stored Procedure] 6 102
DB2 iSeries Combine Results of 2 Selects (nested Join?) ? 2 78
iSeries DB2 SQL - Request user input 12 57
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question