Problem with AS/400 data transfer to Excel

Request help for laptops, netbooks, minibooks, or any of the other smaller portable computers running windows.
Post Reply
Posts: 14
Joined: Tue Aug 28, 2012 6:13 pm

Problem with AS/400 data transfer to Excel

Post by iafmanoj »

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.

Posts: 15
Joined: Tue Aug 28, 2012 6:14 pm

Re: Problem with AS/400 data transfer to Excel

Post by iageoscience »

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.

Post Reply

Return to “Laptops / Netbooks / Minibooks”