How to export data from AS/400 into Microsoft Excel

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.

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    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
    50,860 pointsBadges:
    report
  • Pcatlin
    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
    Good answer from the other Phil Doesn't anyone who will use this sheet need to set-up the data source? Phil
    50,860 pointsBadges:
    report
  • Pcatlin
    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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following