0

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.

AlexMI
  • 824
  • 1
  • 15
  • 36
  • When I try your SQL and line of data, I get another error, saying the date fields are not correct format, which is true, as you don't specify the format in your DDL. Are your sure you don't have any hidden char in there? – gsalem May 07 '21 at 12:36
  • @gsalem: I open the data file in notepad++ and no other char is present in the file. For the DDL you mean that I have to set the field_definitions Clause in any case? – AlexMI May 07 '21 at 15:13
  • try adding DATE FORMAT "YYYY-mm-dd hh24:mi:ss" after the enclosed by, this should solve the error I mentioned. As for the error you mention, I cannot reproduce. – gsalem May 07 '21 at 21:05

0 Answers0