Home » RDBMS Server » Server Utilities » Loading String has double quotes in it (Oracle, 11g, Unix)
Loading String has double quotes in it [message #593976] Thu, 22 August 2013 06:30 Go to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi,

I am loading .csv file into Oracle using sql loader
file has strings, and numberics, Strings are surrounded by double quotes(") and field terminated by comma(,)

load data
        BADFILE '/var/opt/app/bad/filename'
        DISCARDFILE '/var/opt/app/discard/filename'
        append into table source_file
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS


Some time String fields may have double quotes in it, at that time it is rejecting the records. Could someone help me on this how to handle those records to load into table.

Thanks
Re: Loading String has double quotes in it [message #593977 is a reply to message #593976] Thu, 22 August 2013 06:34 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
It'd help if you posted some example data from the csv file.
Re: Loading String has double quotes in it [message #593980 is a reply to message #593976] Thu, 22 August 2013 06:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1994
Registered: January 2010
Senior Member
If your load file doesn't have any values enclosed indouble quotes, then get rid of OPTIONALLY ENCLOSED BY '"'. If it does, you have bad data file - you can't enclose field values with characters that can be used in fields themselves.

SY.
Re: Loading String has double quotes in it [message #593996 is a reply to message #593980] Thu, 22 August 2013 07:35 Go to previous message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Data is as below
Quote:

08/21/13,12:02:04,Service,"Search.Results",0.219,P,Number,20600,21820,507162584563,aslfine,10.117.232.42,0,"",0,"ss:G "S","",""


There are few fields which are enclosed with double quotes since there may be chance of having special characters

[Updated on: Thu, 22 August 2013 07:36]

Report message to a moderator

Previous Topic: sqlloader detect invisible characters
Next Topic: loading vertical data using sqlldr
Goto Forum:
  


Current Time: Fri Aug 22 19:34:49 CDT 2014

Total time taken to generate the page: 0.10118 seconds