Improve company productivity with a Business Account.Sign Up

x
?
Solved

Import data from excel to AS400 table

Posted on 2006-11-15
10
Medium Priority
?
2,896 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 46

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 46

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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 46

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

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 (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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…

605 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