One Star

Special Character Loading to Oracle from a File

Hi,
I'm loading a text file of data to an Oracle table. The program is working great. One minor thing I can't seem to figure out.
The file contains a few rows of data that contain some special characters. When the program load the data to the database, they look weird. For example,
the file contains: E.Fiedler > R.Parkin > M.Csépai
data in the DB after the data load in Oracle: E.Fiedler > R.Parkin > M.Cspai
FYI..The special character looks like [], even though when I paste it here it looks like
I ran a query in Oracle using the DUMP function. The results of the incorrectly loaded records are as follows (E.Fiedler > R.Parkin > M.Cspai):
dump(DESCR, 1017): Typ=1 Len=33 CharacterSet=UTF8: E,.,F,i,e,d,l,e,r, ,>, ,R,.,P,a,r,k,i,n, ,>, ,M,.,C,s,ef,bf,bd,p,a,i
dump(DESCR, 1017, 28, 1): Typ=1 Len=33 CharacterSet=UTF8: ef
I updated the tables manually and ran the same query. Now the results are as follows (E.Fiedler > R.Parkin > M.Csépai):
dump(DESCR, 1017): Typ=1 Len=32 CharacterSet=UTF8: E,.,F,i,e,d,l,e,r, ,>, ,R,.,P,a,r,k,i,n, ,>, ,M,.,C,s,c3,a9,p,a,i
dump(DESCR, 1017, 28, 1): Typ=1 Len=32 CharacterSet=UTF8: c3
The results above tells me the data is UTF8 encoded. Therefore I changed the advanced properties of both the tFileInputDelimitted and tOracleOutput components to Encoding of UTF-8. But the problem exists. I've also tried ISO-8859-15 with no luck.
It is not a big deal to manually update these using a query, as I'm able to find these rows by running SQL after the job completes. But it would be awesome if this can be avoided by making sure the job loads the data correctly in the first place.
Thanks for any help.
Suchintha.
3 REPLIES
One Star

Re: Special Character Loading to Oracle from a File

I am expiriencing the same problem with loading from Excel into MySQL.
However, it seems to me that tExcelInput is not handling characters properly.
My original file has this string: ' MEGA CHEDRAUI ACAPULCO Calzada Pié de la cuesta No 239 Col.'
When I create schema and look a the data in Preview mode, I see that string is corrupted: ' MEGA CHEDRAUI ACAPULCO Calzada Pi de la cuesta No 239 Col.'
Please explain how to handle international data properly.

Re: Special Character Loading to Oracle from a File

You may need to add an additional jdbc paramater:
"noDatetimeStringSync=true&characterEncoding=utf8"
One Star

Re: Special Character Loading to Oracle from a File

Hi,
I've tried the option you mentioned including some variations. But I'm still not able to load the data as is. Included are some images of my settings.
Thanks.