Data transfer from AS400 to Excel 2007 Stops at 16,xxx rows

35 pts.
Tags:
AS/400
AS/400 data transfer
AS/400 import/export
DDS
Excel 2007
Excel to iSeries
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

Answer Wiki

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

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.

<b>Ringside</b>: 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.

<b>Client Access Express Updates</b>: http://www-03.ibm.com/systems/i/software/access/windows/downloads.html

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
  • Ringside
    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.
    35 pointsBadges:
    report
  • cjdugas
    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.
    55 pointsBadges:
    report
  • Ringside
    I believe the patches for Client Access Express are located here: http://www-03.ibm.com/systems/i/software/access/windows/downloads.html
    0 pointsBadges:
    report
  • pdraebel
    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.
    3,040 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