| External tables unable to load special chars from csv file [message #404772] |
Sat, 23 May 2009 09:58  |
McLan
Messages: 36
Registered: April 2008
|
Member |
|
|
Hi,
I have an issue with loading csv file data in to external table.
The csv files has some Dutch special characters hence unable to load the data in into external table.
I have defined the external table
Also created the directory with required permissions.
No issues in loading normal English data , successfully able to load the normal English data into table. But the issue is in loading the Dutch letters.For example , there is an issue in loading the below data into varchar2(255).
The name H.A.M.,Dangé is seen as H.A.M.,Dang\351 in csv file.
Below is the error:
SQL> select count(*) from xtern_customer_data;
select count(*) from xtern_customer_data
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52 [Updated on: Sat, 23 May 2009 11:40] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: External tables unable to load special chars from csv file [message #404812 is a reply to message #404785] |
Sun, 24 May 2009 07:12  |
McLan
Messages: 36
Registered: April 2008
|
Member |
|
|
Here is the table definition:
-bash-3.00$ cat xt_cust.sql
create table xtern_customer_data
(
FIRST_NAME varchar2(90),
LAST_NAME varchar2(90),
CITY varchar2(90),
customer_status varchar2(15)
)
organization external
( default directory report_dir_40104
access parameters
( records delimited by newline
fields terminated by ','
)
location ('xtern_cust_data.csv')
);
SQL> desc xtern_customer_data;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_NAME VARCHAR2(90)
LAST_NAME VARCHAR2(90)
CITY VARCHAR2(90)
CUSTOMER_STATUS VARCHAR2(15)
Here is the content of csv file, cat and vi.
-bash-3.00$ cat xtern_cust_data.csv
H.A.M.,Dangé,Uden,Active
-bash-3.00$ vi xtern_cust_data.csv
H.A.M.,Dang\351,Uden,Active
Here is the sql output:
SQL> select count(*) from xtern_customer_data;
select count(*) from xtern_customer_data
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
|
|
|
|
|
|
| Re: External tables unable to load special chars from csv file [message #404949 is a reply to message #404817] |
Mon, 25 May 2009 12:10 |
McLan
Messages: 36
Registered: April 2008
|
Member |
|
|
Hi Michel,
The below is the logfile content for rejected case:
-bash-3.00$ cat XTERN_CUSTOMER_DATA_5815.log
LOG file opened at 05/25/09 18:51:10
Field Definitions for table XTERN_CUSTOMER_DATA
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
FIRST_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
LAST_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
CITY CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
CUSTOMER_STATUS CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field CUSTOMER_STATUS
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /usr/src/sql/reports/xtern_cust_data.csv
The below is log file content when the record is loaded after I removed the \351 from the csv file:
-bash-3.00$ cat XTERN_CUSTOMER_DATA_6219.log
LOG file opened at 05/25/09 18:57:39
Field Definitions for table XTERN_CUSTOMER_DATA
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
FIRST_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
LAST_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
CITY CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
CUSTOMER_STATUS CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
Thanks,
|
|
|
|
| Re: External tables unable to load special chars from csv file [message #404951 is a reply to message #404949] |
Mon, 25 May 2009 12:43 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Add the parameter CHARACTERSET in your table definition so that it matches the character set of the data file.
By default Oracle assumes it is the character set of the database (UTF8) and so "é,U" that is X'E9DC55' is assumed to be only one character (ꛕ) and so one comma is missing.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
