Home » SQL & PL/SQL » SQL & PL/SQL » External tables unable to load special chars from csv file
External tables unable to load special chars from csv file [message #404772] Sat, 23 May 2009 09:58 Go to next message
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 #404777 is a reply to message #404772] Sat, 23 May 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post database character set and table definition.
Add logfile and badfile to your external table and post the errors.

Don't use quote tags if you don't quote.

Regards
Michel
Re: External tables unable to load special chars from csv file [message #404780 is a reply to message #404772] Sat, 23 May 2009 13:06 Go to previous messageGo to next message
McLan
Messages: 36
Registered: April 2008
Member
SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
UTF8


Unable to see the log file due to permisions , I will get back to you with the log files soon.
Re: External tables unable to load special chars from csv file [message #404782 is a reply to message #404780] Sat, 23 May 2009 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For the moment you can post the table definition ("desc xtern_customer_data").

Regards
Michel
Re: External tables unable to load special chars from csv file [message #404785 is a reply to message #404780] Sat, 23 May 2009 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is most likely as 'é' takes 2 bytes in UTF8 you exceed field size.

Regards
Michel

[Updated on: Sat, 23 May 2009 14:17]

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 Go to previous messageGo to next message
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 #404817 is a reply to message #404812] Sun, 24 May 2009 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I currently don't see the reason; you have to add a logfile in your table definition to know why the line is rejected.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: No .of rows in a table
Next Topic: Error in updating column
Goto Forum:
  


Current Time: Fri Dec 02 18:25:24 CST 2016

Total time taken to generate the page: 0.20853 seconds