How to export data from AS/400 into Microsoft Excel

NADEE123

20 pts.

Tags:

AS/400 administration

AS/400 errors

AS/400 import/export

Microsoft Excel

Microsoft Excel import/export

Hi, Plse tell me ,what is the problem. I try to export data from As400 into Excle.I wrote a macro in the excle sheet.I run a progrme it gives error massege ['Excel is not recognized as an internal or external command operable programe or batch file. thanks & best regards, Nadeeshani.

Asked: January 22, 2009 7:58 AM Last updated: January 26, 2009 2:52 PM

Answer Wiki

Last updated: January 22, 2009 9:36 AM GMT

ReshmaG455 pts.
History
Contributors
- Ordered by most recent
- ReshmaG455 pts.

Thanks. We'll let you know when a new response is added.

Hi Nadeeshani

You can FTP the AS400 file through Command Prompt

First copy ur data to Comma seperated flat file thru CPTOIMPF
then
GET <lib>/<File> C:\ <name>.CSV

Thanks
Reshma

Discuss This Question: 4 Replies

philpl1jb Jan 22, 2009 3:13 PM GMT

Hi Nadeeshani You're getting a Windows DOS error -- you haven't gotten to the 400. I'm not sure how much help you'll get from the AS/400 group. Have you got the AS/400 client access ODBC stuff installed on your PC? And a data source defined -- mine appears to be defined as Excel Files -- so perhaps -- you put Excel in where you needed Excel Files ??? Phil

54,090 pointsBadges:

report
Pcatlin Jan 23, 2009 3:03 PM GMT

Nadeeshani... Install the data transfer pieces from the client access install, also install the excel interface pieces. Once you do that, you will be able to go into excel and under the "DATA" menu, you can "Import External Data" then select "New Database Query". That will open a wizard that will prompt you for your Database. If this is your first time, you can select "New Data Source". This prompts you to setup an ODBC connection to the iseries using the data transfer pieces you installed above. Once that ODBC connection is in place you select the table you want to download into your spreadsheet, and which cell you want the table to start in. The import will bring the field names in the first row and the data in the following rows of the spreadsheet. You can save the query as a part of the wizard as well so you only have to refer to it in future spreadsheets. As for your macro, you can then just use the "refresh data" option on the "data" menu, or bring that button out to your tool bar. Good Luck! Phil

200 pointsBadges:

report
philpl1jb Jan 23, 2009 3:34 PM GMT

Good answer from the other Phil Doesn't anyone who will use this sheet need to set-up the data source? Phil

54,090 pointsBadges:

report
Pcatlin Jan 26, 2009 2:52 PM GMT

Phil is right... Users that need to refresh the data will need to setup the data source.. Another option: You can save the sheet with the data. Future users can use the iSeries data, but without setting up the data source they would not be able to refresh it. Not setting up the data source lets you have some control over the generation of the sheet, depending on your needs. We have a document that is updated weekly. Most users cannot refresh the data because we want all of them working off the same starting point, but the data in the iSeries file is dynamic, so if we let everyone refresh, all the users would get different results. Something else I discovered... You can set the query properties to refresh automatically every time the worksheet is opened. This eliminates the need for either a macro or having to push the ! button. --the other Phil--

200 pointsBadges:

report