Getting info from AS400 to Excel

[Guest.Visitor]

A user has created some queries and reports through Report Writer on the AS400. They want to get this info into Excel, right now they are manually keying this info into Excel. What is the best way to do this? I have Client Access on my machine but the user does not. Thanks. Jill jherrmann@littleredtruck.com

[B.Myrick]

Jill, If you don't want to be uploading and downloading for the user all the time, by all means give them PC/Support. If you look at the options with Excel, these days, you will see an option for AS/400 transfer. Works great! Used it quite a bit for downloading reports and then letting the user import the document. If the user is not going to get PC/Support, but is on the network, you can also e-mail these to him/her. Course, if the user does not have PC/Support, how are they creating queries and spreadsheets? Later, Bret Myrick

[Guest.Visitor]

Use the CPYTOIMPF command to put the output from the Query into a comma delimited format. Then use FTP to place the file on a server that the user has access to. This can all be placed into a CL. The tough part is calculating the size of the temporary file. You need to account for the size of each field plus the delimiters. A comma between the fields and if the field is character then double quotes around the field.

[Guest.Visitor]

Jill, Currently the way I do it is to create a printer session in CA that prints to a predefined Generic/Text printer. This G/T printer is defined to print to port "File:". When you send a spool file to this printer session, a box will come up and ask you for the name of the file it will create. If sharing is enabled on the user's PC you could just direct the printout to their machine. A file name of \TheUserCTempprintout.txt . Would drop it right on their PC. (Caveat, I haven't tried this last part. I have CA on everyone's PC that would need this, so I haven't had to drop it on another's PC). Bill

[Guest.Visitor]

Hello Jill, If your query supports creating database files, you can use the CA/400 database transfer to transfer the data to the PC. If you're looking for a commercial solution for downloading your query reports directly to spreadsheet and database formats, check out our WinSpool/400 and DataImport products. For more information, check out our web site or contact me via email or phone. Regards, Richard Schoen RJS Software Systems Inc. "The AS/400 Report Distribution Experts" Email: richard@rjssoft.com Web Site: http://www.rjssoft.com

[Guest.Visitor]

Do both PCs have access to a network? If so import the file to your PC excel and then into the other PC. bobh

[Guest.Visitor]

First get it to a database file either through changing the query to output that way, or do a CPYSPLF to get it to a PF. Once there you can also use MS Query OR ADO to access this data and pull it into your SS. There is code on the Web for Slacker Indexes that will give you a working example on how to use ADO. (Jan 00) Good luck

[Guest.Visitor]

If you have Client Access or Express.. During the setup it will automatically create an OBDC data source and a plugin into EXCEL. I used this all the time and amaze people.. When it is installed correctly and the Excel application is started: Click on data A window opens with options, one of them is "Transfer Data From AS/400" Select this option and the rest is simple..