Solved

Import data from excel to AS400 table

Posted on 2006-11-15
10
2,864 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

691 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