External Table issue

Hello Experts,

I have the attached file which I need to read the csv file and load into on of my table.
I was trying with external table but seems to be an problem on the clob column.

Sample file :

Application2.csv


Here is my sample external table DDL :

CREATE TABLE read_csv
  (
   APP_NAME VARCHAR2(100)                                 
,APP_DESCRIPTION clob                          
,APP_BUSINESS_CRITICALITY VARCHAR2(100)                           
,COOKEDENV VARCHAR2(100)                          
,APP_BUSINESSFACING VARCHAR2(100) 
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY "ORA_DIR" 
    ACCESS PARAMETERS ( 
          RECORDS DELIMITED BY '|\n' 
          CHARACTERSET WE8MSWIN1252 
          STRING SIZES ARE IN BYTES 
          BADFILE LOG_LOC:' read_csv.bad' 
          NODISCARDFILE 
          LOGFILE LOG_LOC:' read_csv.log' 
          SKIP 1 
          FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AND '"' 
          TRIM 
          ( 
            "APP_NAME" VARCHAR2(100) , 
            "APP_DESCRIPTION" VARCHAR2(10000),
            "APP_BUSINESS_CRITICALITY" VARCHAR2(100) ,
            "COOKEDENV" VARCHAR2(100) ,
            "APP_BUSINESSFACING" VARCHAR2(100)  
          ) 
    ) 
    LOCATION ( "ORA_DIR":' Application2.csv' )
  )
  REJECT LIMIT UNLIMITED;

Open in new window

LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
try changing  

"APP_DESCRIPTION" VARCHAR2(10000)

to

 "APP_DESCRIPTION" CHAR(10000),
0
Swadhin RaySenior Technical Engineer Author Commented:
Tried that but I am getting the below errors:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "column, (, ltrim, lrtrim, ldrtrim, missing, notrim, rtrim, reject"
KUP-01008: the bad identifier was: TRIM
KUP-01007: at line 9 column 11

Open in new window

0
sdstuberCommented:
Sorry. I didn't notice you had TRIM in there.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Swadhin RaySenior Technical Engineer Author Commented:
Even tried with notrim still not working ....
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
there is no "TRIM" within this context! You need to use "LRTRIM" !!

(http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch12.htm#1008302)
0
Swadhin RaySenior Technical Engineer Author Commented:
CREATE TABLE read_csv
  (
   APP_NAME VARCHAR2(100)                                 
,APP_DESCRIPTION clob                          
,APP_BUSINESS_CRITICALITY VARCHAR2(100)                           
,COOKEDENV VARCHAR2(100)                          
,APP_BUSINESSFACING VARCHAR2(100) 
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY "ORA_DIR" 
    ACCESS PARAMETERS ( 
          RECORDS DELIMITED BY '\n' 
          CHARACTERSET WE8MSWIN1252 
          STRING SIZES ARE IN BYTES 
          BADFILE ORA_DIR:' read_csv.bad' 
          NODISCARDFILE 
          LOGFILE ORA_DIR:' read_csv.log' 
          SKIP 1 
          FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AND '"' 
          LRTRIM 
          ( 
            "APP_NAME" CHAR , 
             "APP_DESCRIPTION" CHAR(10000),
            "APP_BUSINESS_CRITICALITY" CHAR ,
            "COOKEDENV" CHAR ,
            "APP_BUSINESSFACING" CHAR  
          ) 
    ) 
    LOCATION ( "ORA_DIR":'Application2.csv' )
  )
  REJECT LIMIT UNLIMITED; 

Open in new window


Used the above DDL.

And getting only one record after selecting:

SQL> select * from read_csv;

APP_NAME                                                                         APP_DESCRIPTION                                                                  APP_BUSINESS_CRITICALITY                                                         COOKEDENV                                                                        APP_BUSINESSFACING
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
QCARE - Production                                                                                                                                                1                                                                                Production                                                                       Business Application

SQL> 

Open in new window

0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Have you tested the use of "TRAILING NULLCOLS" in  your loader control file?!

Btw: the provided CSV looks pretty "shitty", sorry ;-)
0
Swadhin RaySenior Technical Engineer Author Commented:
Yeah the file is little bad. Need a solution to load the file. Even using TRAILING the issue still remains the same.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Can you provide the LOG and BAD files?!
0
Swadhin RaySenior Technical Engineer Author Commented:
Here is the details:

DDL :

CREATE TABLE read_csv
  (
   APP_NAME VARCHAR2(100)                                 
,APP_DESCRIPTION clob                          
,APP_BUSINESS_CRITICALITY VARCHAR2(100)                           
,COOKEDENV VARCHAR2(100)                          
,APP_BUSINESSFACING VARCHAR2(100) 
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY "ORA_DIR" 
    ACCESS PARAMETERS ( 
          RECORDS DELIMITED BY '\n'
          CHARACTERSET WE8MSWIN1252 
          STRING SIZES ARE IN BYTES 
          BADFILE ORA_DIR:' read_csv.bad' 
          NODISCARDFILE 
          LOGFILE ORA_DIR:' read_csv.log' 
          SKIP 1 
          FIELDS TERMINATED BY ',' 
        OPTIONALLY ENCLOSED BY '"' LDRTRIM 
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
          ( 
            "APP_NAME" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM , 
             "APP_DESCRIPTION" CHAR(10000)   TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM ,
            "APP_BUSINESS_CRITICALITY" CHAR  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM,
            "COOKEDENV" CHAR  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM,
            "APP_BUSINESSFACING" CHAR  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
          ) 
    ) 
    LOCATION ( "ORA_DIR":'Application2.csv' )
  )
  REJECT LIMIT UNLIMITED; 
  
  

Open in new window


Here is the screen shot for the data, when selected :

Sample Output
Here is the log file:

 LOG file opened at 03/27/15 08:46:48

Field Definitions for table READ_CSV
  Record format DELIMITED, delimited by 

  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

    APP_NAME                        CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    APP_DESCRIPTION                 CHAR (10000)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    APP_BUSINESS_CRITICALITY        CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    COOKEDENV                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    APP_BUSINESSFACING              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field APP_DESCRIPTION
KUP-04036: second enclosing delimiter not found
KUP-04101: record 3 rejected in file E:\ORA_DIR\Application2.csv
KUP-04021: field formatting error for field APP_NAME
KUP-04036: second enclosing delimiter not found
KUP-04101: record 6 rejected in file E:\ORA_DIR\Application2.csv

Open in new window


Here is the bad file:


Universal TNS Names File - Production,"The location of the Universal tnsnames.ora : \\apps1\corphq_apps1\APPS\TNS_ADMIN
",,Production,Yes

Open in new window

0
Alexander Eßer [Alex140181]Software DeveloperCommented:
So, now, you're getting back 3 rows, instead of 1 as mentioned above. Referring your provided test data, the results look technically "valid" to me...
Btw: in your control file, you mark every single column with "TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM", but you already have that defined "globally", so you do NOT overwrite/override anything here -> totally redundant. Change this part
( 
            "APP_NAME" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM , 
             "APP_DESCRIPTION" CHAR(10000)   TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM ,
            "APP_BUSINESS_CRITICALITY" CHAR  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM,
            "COOKEDENV" CHAR  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM,
            "APP_BUSINESSFACING" CHAR  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
          ) 

Open in new window

to this
( 
            "APP_NAME" CHAR(255)   , 
             "APP_DESCRIPTION" CHAR(10000)   ,
            "APP_BUSINESS_CRITICALITY" CHAR  ,
            "COOKEDENV" CHAR  ,
            "APP_BUSINESSFACING" CHAR  
          ) 

Open in new window

0
Swadhin RaySenior Technical Engineer Author Commented:
after changing still I am getting 3 row ...

Here is the latest DDL:

CREATE TABLE read_csv
  (
   APP_NAME VARCHAR2(100)                                 
,APP_DESCRIPTION clob                          
,APP_BUSINESS_CRITICALITY VARCHAR2(100)                           
,COOKEDENV VARCHAR2(100)                          
,APP_BUSINESSFACING VARCHAR2(100) 
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY "ORA_DIR" 
    ACCESS PARAMETERS ( 
          RECORDS DELIMITED BY '\n'
          CHARACTERSET WE8MSWIN1252 
          STRING SIZES ARE IN BYTES 
          BADFILE ORA_DIR:' read_csv.bad' 
          NODISCARDFILE 
          LOGFILE ORA_DIR:' read_csv.log' 
          SKIP 1 
          FIELDS TERMINATED BY ',' 
        OPTIONALLY ENCLOSED BY '"' LDRTRIM 
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
          ( 
            "APP_NAME" CHAR(255)   , 
             "APP_DESCRIPTION" CHAR(10000)   ,
            "APP_BUSINESS_CRITICALITY" CHAR  ,
            "COOKEDENV" CHAR  ,
            "APP_BUSINESSFACING" CHAR  
          ) 
    ) 
    LOCATION ( "ORA_DIR":'Application2.csv' )
  )
  REJECT LIMIT UNLIMITED; 
  
  

Open in new window

0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Yes, I see & I know. Again, taking this (test) setup, everything looks fine, technically speaking. Everything works as designed...
Even the LOG entries seem self-explanatory to me...
Where exactly is your problem now, please explain ;-)
0
Swadhin RaySenior Technical Engineer Author Commented:
If you open the attached csv file then you can see I have only two rows which need to be added :

Application2.csv
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Which 2 rows?! I'm sorry, but right now I'm quite confused what you're aiming at?!?
0
Swadhin RaySenior Technical Engineer Author Commented:
If you open the CSV you will see only two rows present.

If i need to load into any DB tables then there should only be two rows added.

But after loading the CSV file we see there are 3 rows added which is nothing but the second row is getting into two rows.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
If you open the CSV you will see only two rows present.
2 rows?!? Let me count: 1,2,3.... actually I'm able to count 6 (even 7 if you take the last blank line, too). Still, I am quite confused...
screen from Notepad
0
Swadhin RaySenior Technical Engineer Author Commented:
Open the same file as like below:

Sample CSV file
0
Swadhin RaySenior Technical Engineer Author Commented:
So except header we have only two rows.
Which need to be loaded into the target table.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Sorry, can't reproduce :-(
0
Wasim Akram ShaikCommented:
I know your problem.. this is what i had faced some time back.. there are actually two lines.. if you try opening it up in excel you will get  only two rows..

the more rows appear because the data has some special characters... tabs,new line feeds etc., etc., if you see the second line.. the field data enclosed in quotes has new line character too.. which makes the data to spread across multiple lines.. making text editors to get confused as a new line..

this is hard to do via oracle.. the best bet is to go for a CSV parser..( i believe you are using unix) and i had used this in unix too..

there is a excellent CSV parser tool written by lorance and its free to use  , refer to this site

http://lorance.freeshell.org/csvutils/

download the zipfile PK.zip

copy the standalone file csv2csv.sh to your unix box and use the command like this

[UNIXBOX]$  sh csv2csv.sh -N " " Application2.csv
APP_NAME,APP_DESCRIPTION,APP_BUSINESS_CRITICALITYCOOKEDENV,APP_BUSINESSFACING
QCARE - Production,,1,Production,Business Application
 ",,Production,Yes\\apps1\corphq_apps1\APPS\TNS_ADMINto be created and the path to the location set in the variable value:

Open in new window


this will actually convert your csv file to a real delimiter file , see the command -N " ", it replaces the new line character to a space.. so new line feed which is the line terminator in external file is handled.. so if you redirect the output to a new file and use it for your external table then your problem would get solved..
0
Swadhin RaySenior Technical Engineer Author Commented:
@Wasim : Do we have anything on windows ....
0
Wasim Akram ShaikCommented:
I am afraid, am not aware of any such free licensed versions which will do the task for you.. even if they do.. you may have to open it up in a gui and do it yourself and do write a little bit of batch scripting to open the tool and feed the file to it.. but if  you have a unix/linux box.. then its pretty easier.. transfer it to any of the unix machine do the required conversion and use it back..

if you are doing it manually.. then open up excel and save the excel in a different format.. may be a text(unicode format) and remove the new line and special characters manually ...

or may be you can download cygwin on windows..(unix emulator/simulator).. i doubt whether the package written by lorance will run successfully on cygwin or not..

i have never tried any commercial version of any software. .but if you google it.. there are some sites which claim that they can do this task..CSV2CSV converter windows..
0
Swadhin RaySenior Technical Engineer Author Commented:
I am not able to get anything on windows to do this....

Still the issue remains the same.
0
Wasim Akram ShaikCommented:
Why don't you install Unix via VMware?
When you don't have a solution.. When you have arrived at a dead end its better to look out for alternatives rather than being stuck there.. Have you tried any of the options.. Also there are couple of CSV parsers written in JAVA.. Did you try searching them out? I have used a one long back ..Which would work independent of platform... Basically you should broaden your search... Oracle doesn't have solution in this case.. Its better to think out of box.. Try searching for CSV parser.. https://commons.apache.org/proper/commons-csv/apidocs/org/apache/commons/csv/CSVParser.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Swadhin RaySenior Technical Engineer Author Commented:
thanks ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.