Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-09-03
10
Medium Priority
?
3,263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 35

Accepted Solution

by:
Gary Patterson earned 1000 total points
ID: 33597510
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 35

Expert Comment

by:Gary Patterson
ID: 33597527
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
ID: 33615639
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:carlaohare
ID: 33615645
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 1000 total points
ID: 33621095
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
 

Author Comment

by:carlaohare
ID: 33625158
That's great,

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

Expert Comment

by:Gary Patterson
ID: 33625877
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
ID: 33630583
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
ID: 33638618
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 35

Expert Comment

by:Gary Patterson
ID: 33639286
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Hello I read in a discussion about a person who configured a very simple mirror RAID with two hard drives; the system and data were on the same partition. He asked how to repair the system as it was not booting up anymore. In his case running …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

722 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