How do I store Chinese characters on an as400 database file.

I need to store a product code in English & a product description in Chinese on an AS400 database file.
I have the data on a spreadsheet & need to upload it to the AS400
I've created a physical file with a field defined with a data type of E (see below).
     A          R ITMTSTR                                              
     A                                      TEXT('TEST ITEM FILE')     
     A            ITPROD        20          TEXT('ENGLISH PRODUCT CODE')
     A            ITCDES        80E         TEXT('CHINESE DESCRIPTION')
     A          K ITPROD                                               

Open in new window

I inserted a row using iSeries navigator & copy & pasted the data from the first line of the spreadsheet
The Chinese characters show up as squares in the row & when I try and save it I get an error
"Column 2: CWBNL0107 - Converted 4 bytes, 2 errors found beginning at offset 0"

Does anyone know what I need to do to get the Chinese characters onto my AS400 data file.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
CWBNL0107 indicates that you are trying to update a field with characters that are unsupported in the field's CCSID.  Looking at your DDS, you did not specify a CCSID for this column, so it will default to your system default CCSID, as specified in system value QCCSID, or your job CCSID.  You can use the DSPFD command to view the CCSID for the file, and DSPFFD to view the CCSID for each field in the file.

The Chinese in your Excel file is probably UTF-16.  I don't know if you'll be able to cut an paste Chinese text into the Run SQL Scripts window (I can't) if you are on an English-language PC, but you can use the Client Access File Transfer Excel Add-In to upload the Chinese columns to the AS/400.

In general, define the AS/400 database table column with the correct alternate CCSID, if it differs from the system default CCSID:

I suggest that you try a "G type field with CCSID(13488).  This combination works for me when uploading from Excel using the File Transfer Add-In.

Test it like this:

1) Create a table using DDS or SQL with a "G" column in CCSID 13488 (see example).

2) Then use the Excel Add-In for Client Access (you may need to install it - post back if you need help with that).

3) Copy the column containing your Chinese text into a fresh spreadsheet (only one column), and then run the "Transfer Data To iSeries" macro and transfer to the file that you created.

If that works, then you're in business.  Post back if you need more assistance.

- Gary Patterson


     A          R CHINESER                                              
     A            COL1        80G         TEXT('CHINESE DESCRIPTION')



Open in new window

Gary PattersonVP Technology / Senior Consultant Commented:
Here are basic instructions for installing the File Transfer Add-In, just in case:

- Gary Patterson
carlaohareAuthor Commented:
Hi Gary,
Thanks for the help.
I did as you said & the data did upload to the AS400.
It doesn't look like the data on the spreadsheet but is that how it's meant to be e.g.
Data on spreadsheet looks like this
when I do DSPPFM on file its like this
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

carlaohareAuthor Commented:
Chinese data changed when I pressed enter button to the upside down question marks you see
tliottaConnect With a Mentor Commented:
when I do DSPPFM on file its like this

DSPPFM will help as long as you can work out the hexadecimal values for the characters. You can almost be certain that you won't see the actual Chinese characters there.

However, if your PC and your iSeries Access terminal session are both configured to allow Chinese characters, and your job has a correct job CCSID setting, and your table and column have the correct CCSID settings, and the data uploaded correctly and updated the table correctly, then RUNQRY should display the characters.

That's about as far as I can comment. I don't have any PC/workstation configurations that would work nor any Chinese text to work with.

This is something of a specialty area. Personally, I would quickly be asking IBM to help. This kind of stuff takes some significant 'globalization' experience.

carlaohareAuthor Commented:
That's great,

Thanks Gary & Tom for all the help.
Gary PattersonVP Technology / Senior Consultant Commented:
Use the Run SQL Scripts utility in Navigator to view the Chinese characters. It works nicely with Unicode.
Gary's "Run SQL Scripts" suggestion is as good as it gets. I should have added it, but was thinking "green-screen". If Navigator can't do it (assuming a recent iSeries Access service pack), then the data and possibly the database still need work.

carlaohareAuthor Commented:
Thanks Guys,

That worked like a dream, Now I just have to figure out how to print it all out again.

Thanks for all the help.
Gary PattersonVP Technology / Senior Consultant Commented:
Well, Excel does a nice job of printing this sort of thing.  

Use the File Transfer Add-in to download the final merged file, format it as desired, and print it.

- Gary Patterson
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.