I'm facing an error trying to import a CSV file in a Oracle external table. I search for this issue but I do not find a solution that fit my case. I'm iporting a CSV file with filed delimiter and no fields name row. The create table ... end successfully but the select from imp_headersordini raise an error at 1st row.
I'm working on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Here the CREATE TABLE :
CREATE TABLE imp_headersordini (
col01 varchar2(6)
,col02 varchar2(11)
,col03 varchar2(8)
,col04 varchar2(8)
,col05 number(4)
,col06 varchar2(3)
,col07 varchar2(2)
,col08 varchar2(1)
,col09 date
,col10 date
,col11 varchar2(3)
,col12 varchar2(200)
,col13 varchar2(1)
,col14 varchar2(1)
,col15 date
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY WEBLINK_FILES
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('HeadersOrdini.csv')
);
Here the first row of the CSV. All rows has the same structure and there isn't a fields name row.
"header";"02400000001";"28206933";"28206933";"233";"EUR";"L2";"";"2017-03-07 09:31:10";"";"O";"consegna urgente spese a carico";"";"";"1970-01-01 00:00:00"
Here the log file with the error
LOG file opened at 05/07/21 13:22:39
Field Definitions for table IMP_HEADERSORDINI
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:
COL01 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL02 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL03 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL04 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL05 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL06 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL07 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL08 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL09 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL10 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL11 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL12 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL13 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL14 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
COL15 CHAR (255)
Terminated by ";"
Enclosed by """ and """
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field COL01
KUP-04035: beginning enclosing delimiter not found
KUP-04101: record 1 rejected in file /FILE/dsc/HeadersOrdini.csv
++++++ EDIT +++++
After gsalem comments I use PLSQL and utl_file to check the csv and I found that the first row is :
"header";"02400000001";"28206933"...
 the extra characters are the optional Byte Order Mark for a UTF-8 file I modify the access param clause :
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BYTEORDERMARK CHECK
...
but it is still not working with the same error.