AS/400 Data Transfer to Excel

[Guest.Visitor]

Hi, I have the same problem with my data transfer from the AS400. Did you find a solution? Harry Matthie Bermuda General Agency hmatthie@bga.bm

[andreas.goering@web.de]

Hello, we have developed a tool "iExcelGen" which does a native Excel generation with all the option you need. Free download at http://www.goering.de Regards Andreas Goering

[Guest.Visitor]

In my company what we have been using is Client Access for Windows. You could download directly from IBM. What this does is allows you to set up an ODBC connection from the client machine (Windows computer) to the AS400 Server. Once connected you can download the information you need into an Excel file.

[Guest.Visitor]

I am a trainer and I need to create a manual for my company on Client Access. Most of the information available seems to be on how to install the application; but no information on how to utilize the application nor the why things are done the way they are done. If anyone knows of a book or any posting on this issue please let me know.

[Guest.Visitor]

Found the solution yet? I have the same problem.. and manage to settle it myself. I give you a hint : 1) Create a temporary file to keep the header. 2) In your CL (before create STMF), DSPFFD the file you want to convert to Excel -> OUTPUT(*OUTFILE) to create a new file 3) Read the file in your CL (cycle by RCVF). Get column heading WHCHD1 and put it into a long CL variable wrapped by (") and end by (,) and continue for each field. So it will become -> "Column 1", "Column 2", .... 4) Put variable 3) inside another variable which start with "INSERT INTO QTEMP/file in 1) " (at the end of it) 5) Use utility RUNSQL / EXCSQLSTMT (download from net) to run the SQL statement in 4) 6) Use CPYTOSTMF to write to the target STMF file. 7) Proceed to write your data to the same target file. Try it!

[J.VanHuklon]

The link below contains links to some iSeries Access for Windows publications, including redbooks. The top red book ("iSeries Access for Windows Hot Topics: Tailored Images, Application Administration, SSL, and Kerberos") focuses on Admin, so it might not be what you want. The last time a complete redbook was done on the product was V4R4, so some of the information is old. The link to that is included also. http://www-1.ibm.com/servers/eserver...cess/calib.htm

[gotto@islandpacific.com]

There was an article in MC press online from last November that described a utility (RTVCOLHDG) that can be downloaded to do the job. I don't know if the following link will take you to the article or not. Hopefully it will. http://www.mcpressonline.com/mc/.6ae84984!more=1#more. The title of the article was "TechTip: Add Column Headings to your Export Files" by Michael Sansoterra. Nov. 17, 2003.

[Guest.Visitor]

I have a User that would like headings transferred along with the data to Excel. Currently the file transfer from an AS/400 to Excel transfers the DDS field names as the Excel headings. I tried changing a few of the Tabs on the file transfer...along with placing COLHDG & TEXT in the file DDS. I am still getting the DDS field names each time they Download the file. Do any AS/400 Gurus know a secret? Thanks

[jspera]

After I data transfer from the AS/400 , I copy an existing excel spread sheet with the correct headlines and paste it into the new excel spread sheet.( Of course the column have to matched)