CODEWITHSUNDEEP
sql-loader
Savitha
Savitha

Reputation: 415

Double quotes in the in the data loaded through SQL loader

I have a problem with the data getting loaded to Oracle tables through SQL Loader.

The problem is that double quotes is getting enclosed to the value inserted.

example: 

"917681904           "
"914481475            "
"935848150            "

The problem is with the CHAR fields, especially with the CO_COMERCIAL_SERV.

It should have inserted without the double quotes. I have no clue from where these double quotes are getting added. :(

The control file, which I used earlier is :

OPTIONS (SKIP = 1)
LOAD DATA CHARACTERSET WE8ISO8859P1

APPEND
PRESERVE BLANKS
INTO TABLE "TAB_SAVITHA"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

(
"NU_SECUE_PSCO",
"CO_CLIENT_COM",
"CO_PRSE",
"CO_COMERCIAL_SERV",
"CO_PROVINCIA_INST",
"NU_DURA_ANOS",
"IN_LINEA"
)

Later on, I took off double quotes around the column names and gave it as below. But still the same issue.

OPTIONS (SKIP = 1)
LOAD DATA CHARACTERSET WE8ISO8859P1

APPEND
PRESERVE BLANKS
INTO TABLE "TAB_SAVITHA"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

(
NU_SECUE_PSCO,
CO_CLIENT_COM,
CO_PRSE,
CO_COMERCIAL_SERV,
CO_PROVINCIA_INST,
NU_DURA_ANOS,
IN_LINEA
)

The shell script used to load the data is as below::

cat<<EOD >PBPS.sql 
set linesize 800
set pagesize 3000
set heading off
set feed off

select NU_SECUE_PSCO||'|'||CO_CLIENT_COM||'|'||CO_PRSE||'|'||CO_COMERCIAL_SERV||'|'||CO_PROVINCIA_INST||'|'||NU_DURA_ANOS||'|'||IN_LINEA||'|' FROM PBPS_BKP WHERE HITO =$vpin;

exit
EOD


sqlplus -s $1/$2@$3 @PBPS.sql > PBPS.dat

sqlldr $1/$2@$3 control='./sqlloader/Main.ctl'
rm -f PBPS.sql

I am in bad need of help.

Any help is greatly appreciated.

Thanks, Savitha

Upvotes: 4

Views: 24429

Answers (4)

Medha Khairnar
Medha Khairnar

Reputation: 11

This is all because of the CRLF in your CSV file when opening viva Notepad++. In View--> Show all symbols --> All characters, you will find CRLF. So to replace this, you need to make following change in your ctl file. This is because of the hidden special character in you CSV.

OPTIONS (SKIP=1)
LOAD DATA
INFILE *
INTO TABLE table_name
APPEND
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  CARRIER_ITEMS CHAR(200) "trim(REPLACE(:CARRIER_ITEMS, CHR(13), ''))",
  CARRIED_ITEMS CHAR(100) "trim(REPLACE(:CARRIED_ITEMS, CHR(13), ''))"
)

Upvotes: 0

Rony
Rony

Reputation: 55

I had this same issue. It was due to file text conversion from Windows to Linux on my input file.

Executing the following command solved the issue:

dos2unix /myInputFile.dat

Upvotes: 0

Tarun
Tarun

Reputation: 182

I got the same problem. After investigating, I found out that this issue is actually due to special character in the string. First replace space then only that special character remains. I am not sure how that character is coming into string but you can copy that character from sqldeveloper etc and use that character in replace function to remove that from you table columns.

Upvotes: 0

Zohaib
Zohaib

Reputation: 7116

try this and see if it works.

FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'

Upvotes: 3

Related Questions