IBM AS400 server in data base file format I try to fetch my data from my windows Excel via ODBC

s prasanna
s prasanna used Ask the Experts™
I have My data in  IBM AS400 server in data base file format I try to fetch my data from my windows Excel via ODBC but most of the fields are not converted to columns and even not presented in any format . (some numeric fields only we able to see in that excel sheet)
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
VP Technology / Senior Consultant
Normally, the IBM i (iSeries / AS/400) DB2 ODBC driver automatically converts physical file character columns to a PC-readable CCSID.  For example, on a US-localized IBM i, database columns are often stored in CCSID 37 - US EBCDIC.  Provided the physical file / SQL table was created with CCSID 37 assigned to these character columns, the ODBC driver, and other utilities, will automatically convert the data based on the code page in use on the PC.

So, most likely, your physical file is improperly configured, and the file or individual columns are set to CCSID 65535.  CCSID 65535 is supposed to be used for untranslatable "binary data".  Fortunately, there is an option in the IBM-i ODBC driver, on the Conversions Tab in ODBC Data Source Administrator to "Convert binary data (CCSID 65535) to text.  In some places, you may see this referred to as "Force Translation".  Try checking this box, if it is not already selected, and see if that doesn't solve your problem.

If it does not, additional information is needed:

1) Please provide the schema of the AS/400 database file.  You can use the DSPFFD command, or provide the SQL or DDS used to define the file.
2) Please provide a sample of the problem Excel file - just a few rows.
3) What ODBC driver are you using, specifically?
4) Please provide the ODBC DSN configuration that you are using - all values from each tab in the ODBC Data Source Administrator.
5) Please provide the value of the system value QCCSID, using the command DSPSYSVAL QCCSID.
6) When you connect using ODBC, the system starts a job associated with your IBM i user profile called QZDASOINIT (or QZDASSINIT if you are using SSL).  You can locate these jobs using WRKOBJLCK OBJ(userprofile) OBJTYPE(*USRPRF) .  Display the attributes of the QZDASOINIT job associated with your ODBC connection, and please provide the Default CCSID (DFTCCSID) associated with this job.
Theo KouwenhovenApplication Consultant
Hi s prasanna,

Marking the numeric fields as Text will help sometime and beware blank-cells are not allowed  (add a space to them), otherwise a null value will be added and they will not be accepted by the AS/400 (when nod specifically defined)

The fastest way is to place th Data in CSV on the IFS and use the AS/400's CPYFRMIMPF (Copy from Inportfile) to add it (or replace) to a AS/400 table.

Or use the File upload of your i Access to upload the data.
Seth SimmonsSr. Systems Administrator

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

-- Gary Patterson (https:#a42498943)
-- Murphey (https:#a42498946)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Experts-Exchange Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial