Problem with AS/400 data transfer to Excel

55 pts.
Tags:
AS/400 Data File
AS/400 data transfer
AS/400 import/export
AS400 Hex Invalid data
Client Access
Microsoft Excel
I have been downloading AS400 query files to Excel spreadsheets for many years using the Data Transfer utility in Client Access. There has never been any problem. Now suddenly all of the output data has started transferring to Excel as long hexadecimal strings and is unusable. I asked the IT folks if anything had changed that might account for this but the answer is always "no". Since this is affecting BOTH my office PC and my laptop simultaneously, I have to believe that this has something to do with the AS400 rather than the individual Client Access installations but I am not sure. I have tried both existing queries and output files and Excel files as well as created brand-new ones. I have Googled this problem and searched the IBM support site for hours and, although I have found some references to this issue, I haven't found a satisfactory solution. A colleague has had this problem for quite some time and no one has ever been able to fix it for him. But he and I work on completely separate queries and spreadsheets. I have tried the transfers both with and without the "Convert CCSID 65535" box checked in the Data Transfer program and while that does indeed translate from hex into English, every record is in a huge single-column text string. I tried the Excel "text-to-columns" function but in some of my bigger files, the precision required to parse these records is just too much for this manual process. I have some very large projects to complete and I really need to get these Excel files into a usable condition quickly. Any help would be appreciated. Thanks.

Answer Wiki

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

Can you state what versions of the IBM i operating system you are on, and what version of Client access?

You say you are using the Data transfer facility, so this creates a number of PC files which contain details of the transfer. Are you able to sanitise these and post them here?

The usual simpke anser is that the field for ‘translate system data to:’ from the ‘details’ prompt in th ePC part of the prompt is not set to BIFF (Basic interchange file format)

experiment with this – and continue to manage the CCSID settings

Discuss This Question: 14  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
  • JDK
    Sorry but I am not an AS400 pro. How do I determine the AS400 i operating system? And how do I "sanitize" the Transfer Definition Files"? The PC Output is set to BIFF8 (MS Excel 8). As I mentioned, even with the CCSID translation checked off in Client Access, I am just getting long text strings...not in Excel format. Even the field names are missing.
    55 pointsBadges:
    report
  • Rickmcd
    Why not try the Excel adins that can be added through excel addins for V5R4 and below To add the Client Access Data Transfer Add-In to Excel, click Browse and then navigate to the Program FilesIBMClient AccessShared directory. Select the cwbtfxla.xll file, and then click OK. Make sure that the Transfer Data From an iSeries option is checked, and then click OK to add the Client Access Data Transfer Add-In to the Excel environment. Different for V6R1
    1,630 pointsBadges:
    report
  • JDK
    I tried the Excel Add-In as soon as this started. Exactly the same result. But here's something interesting I just discovered...if I do a File Transfer on an older QUERY generated in Query/400, the data is beautiful - perfect Excel format but if I do a File Transfer on an newer QUERY (something generated within the last 10 days), it's all hex. That's why I think the problem is in the 400 rather than in Client Access but my company's AS400 people keep telling me that there have been no changes.
    55 pointsBadges:
    report
  • Teandy
    What happens if you change from BIFF8 (MS Excel 8), to ASCII Text?
    5,860 pointsBadges:
    report
  • TomLiotta
    Sorry but I am not an AS400 pro. Discussions can go a long ways. Any clarifications can be requested. Do you have access to an AS/400 command line? The WRKLICINF command will be as reliable as anything that you can do. The 'License Term' column will give the appropriate version for the first item that shows in the list. if I do a File Transfer on an older QUERY generated in Query/400,... Can you clarify what that means? Does it mean that you can run an old 'query' and create a resulting file... and when you retrieve that result file into Excel, it works fine? But if you try to use a newly created result file from a newly created 'query', Excel doesn't get useful values. Is that accurate? Tom
    125,585 pointsBadges:
    report
  • JDK
    I tried changing from BIFF8 (MS Excel 8), to ASCII Text. No difference. When I enter WRKLICINF on the Command Line, I get "Not authorized to command WRKLICINF". What I meant about old vs. new refers to the output file...not the query. I have some old output files that were the products of some older queries that I haven't touched in a while. If I do a File Transfer on them, they come over to Excel perfectly. That's what leads me to believe that it's an AS400 problem, not a Client Acess File Transfer problem. Thanks.
    55 pointsBadges:
    report
  • deepu9321
    Hi JDK, am not sure whether your problem is similar to this or not. I have faced the similar scenario when i was downloading data from i series to Excel. That was because field legnth of file in DB was exceeding the field legnth of Excel. Pradeep.
    4,235 pointsBadges:
    report
  • TomLiotta
    “Not authorized to command WRKLICINF” Moving on to the next reliable method, run these two commands from a command line:
    DSPJOBLOG OUTPUT(*PRINT)
    DSPJOB OPTION(*SPLF)
    The first command will cause your job to create a printable copy of the 'joblog' for your current job in the system. The second command will bring of a list of any printable stuff in your current job -- the joblog should be the last (or only) one in the list. Use option 5='Display' against the joblog file. In the upper left of the viewing area, you should see that the first line starts with something similar to "5761SS1 V6R1M0" or "5722SS1 V5R4M0" or a few other possibilities. The second 'word' gives the Version, Release and Modification levels commonly called the VRM value. That can tell us a lot about your system's capabilities. If you aren't allowed to use DSPJOBLOG or DSPJOB, we might try the DSPOBJD command against one of your recent query output files. That’s what leads me to believe that it’s an AS400 problem,... At the moment, I tend to agree. However, "problem" probably isn't the right word. It might be related to a change in system CCSID settings -- which could lead to a 'problem' for you of course. You have created a new output file from a query, and you want to look at that file in Excel. The data is effectively unreadable because it is presented as hexadecimal characters rather than the normal language characters that you need. Does your new query file show up as readable if you simply view it on the AS/400? You should be able to use the RUNQRY command to see if it's readable. If the name of the file is MYFILE and you created it in library MYLIB, you could run this command:
    RUNQRY  *N  MYLIB/MYFILE
    You should see your file listed in readable format. If it's readable, let us know what version of Client Access you have on your PC. From the PC Start menu, go to Programs-> IBM iSeries Access for Windows-> iSeries Access for Windows Properties. The Properties window should show version, release and modification level of your Client Access product as well as a 'Service level'. The VRM and service level will tell us more useful info. You refer to the product as 'Client Access'. The name might actually be 'iSeries Access for Windows' or 'System i Access for Windows' depending on how new it is. If it turns out to be 'Client Access' when you view its Properties, and if the AS/400 VRM is more recent, that could be a big part of the problem. Finally, what is your PC operating system? What version of Excel are you running? And how long has it been since you installed the Client Access add-in that you're using? Tom
    125,585 pointsBadges:
    report
  • Yorkshireman
    'Sanitise the defineiiton' if you use 'save as' against your file transfer, I'd expect a window to open which enables you to identify the folder and file names used to store the actual transfer definition. This/these are conventional PC text files, so you can copy and paste them here. Before doing so though, ensure that there is no data which would identify yourself or compromise your system or client information (there really won';t be anything in these files of that nature, but its a bit of a reflex before pasting live data to a web site, older Client access transfers ran with a simpler definition file, newerr ones have DTT, TFR and TTO files. Here is a .dtt file - .tfr files are the same (host name has been altered) I opened the file in notepad and did a copy/paste to this window, then edited it. It is pointing at a file named QRPG, which it has determined to be 92 bytes record length, it is also stating that it will be transferred into BIFF8 format etc etc we can match the entries in your transfer to known working transfers, and thus include or eliminate the transfer set up from the conundrum. [[DataTransferToAS400] Version=2.0 [HostInfo] CrtOpt=1 Database=*SYSBAS FRFFile= FileText= HostFile=QRPG/QIRGINC(CMRPG) HostFileType=1 HostName=NXXXXXXX MbrText= ObjAuth=0 RecLen=92 [ClientInfo] ConvType=5 FDFFile=c:/output.FDF PCFile=c:/output PCFileType=16 SendExtraSheets=63 UseFDF=0 [Properties] AllowNumericsInChar=0 AutoClose=0 AutoRun=0 Check4Untrans=0 Convert65535=0 ConvertExcelDateTime=0 ConvertTabs=0 DisSysName=0 Notify=1 ShowWarnings=0 UseCompression=1 UseSSL=2 UserOption=0 this tells us the attributes of the file you are transferring, and how it is being converted.
    5,580 pointsBadges:
    report
  • JDK
    Thanks TomLiotta and Yorkshireman. You've given me a lot of homework and it's going to take me a while. I will post after I've had an opportunity to try all of the steps. I did take a moment to try the RUNQRY *N MYLIB/MYFILE and my files are completely readable on the AS400 screen. Thanks.
    55 pointsBadges:
    report
  • JDK
    Let me modify my last answer. When I run RUNQRY *N MYLIB/MYFILE to my AS400 screen, MOST of the fields are completely readable but SOME are not readable and are yielding characters like this: U**" ` 3rr¤ *àêT *Ìr*** *Ìr***Y
    55 pointsBadges:
    report
  • TomLiotta
    SOME are not readable and are yielding characters like this: I assume this is output from a newly created query; or is it an old query that you simply used recently to create a new version of the output file? Are any of the files that go into the query newly created? Do you have any access to the query definitions themselves? Or are they created for you? Tom
    125,585 pointsBadges:
    report
  • 9277
    Hi can I?
    45 pointsBadges:
    report
  • 9277
    What OS you are using now?
    45 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