Solved

Import data from excel to AS400 table

Posted on 2006-11-15
10
2,837 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:Kdo
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:Kdo
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
 
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:Kdo
ID: 17948209

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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now