JDK Jun 3, 2011   4:40 PM GMT

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:

Rickmcd Jun 3, 2011   5:11 PM GMT

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,720 pointsBadges:

JDK Jun 3, 2011   6:03 PM GMT

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:

Teandy Jun 3, 2011   6:16 PM GMT

What happens if you change from BIFF8 (MS Excel 8), to ASCII Text?

5,860 pointsBadges:

TomLiotta Jun 3, 2011   10:20 PM GMT

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:

JDK Jun 5, 2011   10:10 PM GMT

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:

deepu9321 Jun 6, 2011   1:16 PM GMT

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,980 pointsBadges:

TomLiotta Jun 6, 2011   10:35 PM GMT

“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:

Yorkshireman Jun 7, 2011   7:29 AM GMT

'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.

6,085 pointsBadges:

JDK Jun 9, 2011   2:17 PM GMT

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:

JDK Jun 9, 2011   2:21 PM GMT

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:

TomLiotta Jun 9, 2011   9:52 PM GMT

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:

9277 Sep 13, 2011   3:20 AM GMT

Hi can I?

45 pointsBadges:

9277 Sep 13, 2011   3:21 AM GMT

What OS you are using now?

45 pointsBadges:

sr0b0214 May 30, 2016   6:43 PM GMT

Client Access has a setting that can be used to control how the CCSID 65535 data is handled when being transferred. In release V3R1M3 and later, this setting is accessible from the File drop-down menu, Properties, select the Conversion tab, and select the check box for convert CCSID 65535. In previous releases of Client Access this setting is controlled from an .ini file. A file named CWBTFR.INI must be created in the Microsoft Windows directory. Client Access looks in this file for the following entries: 

Client Access Data Transfer 
ForceTranslation=1; Set to 1 to force 65535 translation, 0 for the default behavior. 

20 pointsBadges:

sr0b0214 May 30, 2016   6:48 PM GMT

On the IBM data transfer app.... click File .. Properties... Conversions... Check the box for "Convert CCSID 655535. 

20 pointsBadges: