Home > IT Answers > AS/400 > Data transfer from AS400 to Excel 2007 Stops ...
Ringside
35 pts.
Q:
Data transfer from AS400 to Excel 2007 Stops at 16,xxx rows
AS400 - DDS, AS/400 data transfer, DDS, DDS Server, Excel to iSeries, Excel 2007, Exporting AS/400 to Excel, Microsoft Excel
I am using client server to access my AS400.  i downloaded and installed it within the last 6 months

i have the excel add on installed and it seems to be working well on tables with less than 16,xxx rows. 

1. can anyone help me find out if there is a more up to date add on for excel 2007?

2. My AS400 is really old.  Can anyone help me find a way to determine if it is an iseries. (i did find some information on the internet that was for an iSeries, but i think my AS400 system is older than that.)

I'm grateful for any and all suggestions.

Thank you



Software/Hardware used:
AS400 Excel2007
ASKED: Oct 1 2009  9:09 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
Chatmaker
1090 pts.
A:
 RATE THIS ANSWER
+2
Click to Vote:
  •   2
  •  0
  • Bookmark and Share
Greetings Ringside:

1) You can use MS Query instead of the add in. It will retrieve up to XL's maximum of 65,536. How to do this is in my blog: http://itknowledgeexchange.techtarget.com/beyond-excel/

2) You can retreive your system's model number through DSPSYSVAL QMODEL and your OS level from DSPDTAARA QSS1MRI. V5R3 ended support on 04/30/2009. V5R4 is currently supported. See http://www-947.ibm.com/systems/support/i/planning/upgrade/suptschedule.html


Good luck: Chatmaker

Chatmaker. I am grateful for your help. I used the technique in your blog and everything worked fine until i got an error PWS0007 Operation Result Set Not found. I tried to get around it and redid the procedure but to no avail.
I can run ms query and it gives me the option to select the table data, but when i press finish, it fails.
it also fails if i add the table to MS query and then try to add data into the query.

Thank you for your patience.


Ringside: Sorry for not seeing you had responded earlier.

The AS400 command DSPMSGD RANGE(PWS0007) MSGF(QIWS/QIWSMSG) describes the error as:

Message . . . . : Operation result set not found.
Cause . . . . . : The handle specified for the operation result set to be filled, returned, or used as the based on result set is not found for the server.

Recovery . . . : Correct the operation result set handle and do the function again.

One KB post suggests:Try unchecking "Enable Extended Dynamic Support" in the ODBC DSN setup

All other KB's indicate this is resolved by patching CA Express.

Client Access Express Updates: http://www-03.ibm.com/systems/i/software/access/windows/downloads.html
Last Answered: Oct 7 2009  12:42 AM GMT by Chatmaker   1090 pts.
Latest Contributors: Ringside   35 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Ringside   35 pts.  |   Oct 5 2009  4:11PM GMT

Chatmaker,
thanks again for all your help.
I am using CA Express. Is there a location where i can go for an update?

I’m grateful for your help.

 

Cjdugas   55 pts.  |   Oct 5 2009  4:28PM GMT

Have you tried the Data Transfer option? Select file as the output device and under details select a file type of CSV.

This program is generally found in the Tool bar and may be found on the menu bar as “actions| Receive file from host”. Older versions of CA may list it differently. Usually they are also listed as executables in the folder.

 

Craig Hatmaker   0 pts.  |   Oct 6 2009  1:16PM GMT

I believe the patches for Client Access Express are located here: <a href="http://www-03.ibm.com/systems/i/software/access/windows/downloads.html" title="http://www-03.ibm.com/systems/i/software/access/windows/downloads.html" target="_blank">http://www-03.ibm.com/systems/i/software…</a>

 

Pdraebel   1095 pts.  |   Oct 9 2009  7:56AM GMT

You can use the latest CA version to access an older version of OS400.
If that does not do the trick why not use a CPYTOIMPF to the IFS (You can use delimiters and create a kind of .CSV file) map a drive to the IFS directory and open that file directly in Excell. The limit should be lifted.