Solved

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

Posted on 2010-09-03
10
2,882 Views
Last Modified: 2013-12-06
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.
 
0
Comment
Question by:carlaohare
  • 4
  • 4
  • 2
10 Comments
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 250 total points
Comment Utility
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:
http://www-01.ibm.com/software/globalization/ccsid/ccsid_registered.jsp

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


DDS:

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

SQL:

CREATE TABLE GARYP1.CHINESE1 (COL1 GRAPHIC(10) CCSID 13488 DEFAULT NULL ) ;

Open in new window

0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Here are basic instructions for installing the File Transfer Add-In, just in case:

http://www.code400.com/forum/showthread.php/539-Add-in-for-Excel-to-pull-data-from-AS400

- Gary Patterson
0
 

Author Comment

by:carlaohare
Comment Utility
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
¿¿¿7814,¿¿¿¿,¿¿¿¿,¿¿¿
when I do DSPPFM on file its like this
é:VÑ+;a±>+;rÎQÎ'jJbÊ
537E60030303030240528186024052917D02779D87
1FA595070801040CEDE61AFE0CEDE690680C6D1122
 
0
 

Author Comment

by:carlaohare
Comment Utility
Sorry,
Chinese data changed when I pressed enter button to the upside down question marks you see
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 250 total points
Comment Utility
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.

Tom
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:carlaohare
Comment Utility
That's great,

Thanks Gary & Tom for all the help.
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Use the Run SQL Scripts utility in Navigator to view the Chinese characters. It works nicely with Unicode.
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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.

Tom
0
 

Author Comment

by:carlaohare
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
In a recent article here at Experts Exchange (http://www.experts-exchange.com/articles/18880/PaperPort-14-in-Windows-10-A-First-Look.html), I discussed my nine-month sandbox testing of the Windows 10 Technical Preview, specifically with respect to r…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now