control file help

[dsub42]

Hi Im importing a simple data set using sql loader, below is the controll file im using...

The field named 'CustName' imports all the records but surround them in the oracle table with single quotes ... eg 'Dave' .. not Dave.. how can i alter the control file to remove these sinngle quotes when the data is imported...



Also....



The field named 'registered' contains data as 'true' or 'false' in the csv... i need to import this into oracle as 1 for true and 0 for false... how can i alter the control file to do this?



my control file is as follows:



OPTIONS (ERRORS=999)

LOAD DATA

INFILE "c:\test.csv"

BADFILE "c:\test.bad"

INSERT

INTO TABLE test

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(CustName,
Registered)

[zargon]

Hi Im importing a simple data set using sql loader, below is the controll file im using...

The field named 'CustName' imports all the records but surround them in the oracle table with single quotes ... eg 'Dave' .. not Dave.. how can i alter the control file to remove these sinngle quotes when the data is imported...



Also....



The field named 'registered' contains data as 'true' or 'false' in the csv... i need to import this into oracle as 1 for true and 0 for false... how can i alter the control file to do this?



my control file is as follows:



OPTIONS (ERRORS=999)

LOAD DATA

INFILE "c:\test.csv"

BADFILE "c:\test.bad"

INSERT

INTO TABLE test

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(CustName,
Registered)

I need to see the data file you're using as well as a create table script for the destination table.

[dsub42]

I need to see the data file you're using as well as a create table script for the destination table.

I have managed to use decode to turn false/true into y/n and also a substr function to remove enclosing single speach marks...

the next problem I am facing is commas in the address field..

do you have any suggestions how to deal with the commas or alternative soloutions to the others?

2 rows of data are as follows... it is representative of the entire data set.. (comma in second line of address 1)

1,'Mr','John L','Davis','1234','19 belsover Street','dales court','pembrokshire','uk',01/04/2000,1.345,True,

2,'Mrs','Christine','Dunn','3241','24,arly Street','spanish gardens','pembrokshire','uk',01/04/2000,3.454,True,

The above lines are being sent into a different table:

CustiD - Number(1)
Suffix - VarChar2 (3)
Fname -VarChar2 (20)
Lname -VarChar2 (20)
CustSpecNo - Number(4)
Address1 -VarChar2 (100)
Address2 -VarChar2 (100)
Address3 -VarChar2 (100)
Address4 -VarChar2 (100)
Datejoined - Date
...

I want to use sql loader to get them into this table... having problems with data and the commas now.... '24,arly Street'

Any ideas?

[zargon]

I have managed to use decode to turn false/true into y/n and also a substr function to remove enclosing single speach marks...

the next problem I am facing is commas in the address field..

do you have any suggestions how to deal with the commas or alternative soloutions to the others?

2 rows of data are as follows... it is representative of the entire data set.. (comma in second line of address 1)

1,'Mr','John L','Davis','1234','19 belsover Street','dales court','pembrokshire','uk',01/04/2000,1.345,True,

2,'Mrs','Christine','Dunn','3241','24,arly Street','spanish gardens','pembrokshire','uk',01/04/2000,3.454,True,

The above lines are being sent into a different table:

CustiD - Number(1)
Suffix - VarChar2 (3)
Fname -VarChar2 (20)
Lname -VarChar2 (20)
CustSpecNo - Number(4)
Address1 -VarChar2 (100)
Address2 -VarChar2 (100)
Address3 -VarChar2 (100)
Address4 -VarChar2 (100)
Datejoined - Date
...

I want to use sql loader to get them into this table... having problems with data and the commas now.... '24,arly Street'

Any ideas?

You shoudln't need substr() to replace the single quotes as your strings should be delimited by double quotes as you instructed SQL*Loader in your control file; convert the ' to " and your single quote problem disappears. So will your embedded comma issue as they will be part of known string data. This:

'Mr','John L','Davis','1234','19 belsover Street','dales court','pembrokshire','uk',01/04/2000,1.345,True,
'Mrs','Christine','Dunn','3241','24,arly Street','spanish gardens','pembrokshire','uk',01/04/2000,3.454,True,

should be written like this:

"Mr","John L","Davis","1234","19 belsover Street","dales court","pembrokshire","uk",01/04/2000,1.345,True,
"Mrs","Christine","Dunn","3241","24,arly Street","spanish gardens","pembrokshire","uk",01/04/2000,3.454,True,

Fix your data file to fix your 'problems'.

[rajavu]

No need to change the Data file (.csv)

You just have to change the control file slightly.

OPTIONS (ERRORS=999)
LOAD DATA
INFILE "c:\test.csv"
BADFILE "c:\test.bad"
INSERT
INTO TABLE test
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "'" TRAILING NULLCOLS

[dsub42]

You shoudln't need substr() to replace the single quotes as your strings should be delimited by double quotes as you instructed SQL*Loader in your control file; convert the ' to " and your single quote problem disappears. So will your embedded comma issue as they will be part of known string data. This:

'Mr','John L','Davis','1234','19 belsover Street','dales court','pembrokshire','uk',01/04/2000,1.345,True,
'Mrs','Christine','Dunn','3241','24,arly Street','spanish gardens','pembrokshire','uk',01/04/2000,3.454,True,

should be written like this:

"Mr","John L","Davis","1234","19 belsover Street","dales court","pembrokshire","uk",01/04/2000,1.345,True,
"Mrs","Christine","Dunn","3241","24,arly Street","spanish gardens","pembrokshire","uk",01/04/2000,3.454,True,

Fix your data file to fix your 'problems'.

I fixed it by using OPTIONALLY ENCLOSED BY "'" instead of '"' but if we take the following examples...

'24,arly street'
'18 matthew's close'

... one string including an embedded single quote and one an embeddedcomma

if i dont use OPTIONALLY ENCLOSED BY "'" ... and i do use a substring function then "24, arly street" is not imported because of the embedded comma but "18 matthew's close" is imported correctly and includes the embedded single quote.

if i do use OPTIONALLY ENCLOSED BY "'" then "24, arly stret" is imported correctly, however this time the row that contained "18 matthew's close" is moved to the bad file because that embedded single speach mark breaks the file.

so i think the only way i can make sure that commas and embedded single quotes are imported into the column is by (and correct me if im wrong).. if i do use OPTIONALLY ENCLOSED BY "'" ... and then on that column in the control file put something in to escape that embedded single quote?

any idea how I would do this? escaping embedded single quotes that is?

[zargon]

I fixed it by using OPTIONALLY ENCLOSED BY "'" instead of '"' but if we take the following examples...

'24,arly street'
'18 matthew's close'

... one string including an embedded single quote and one an embeddedcomma

if i dont use OPTIONALLY ENCLOSED BY "'" ... and i do use a substring function then "24, arly street" is not imported because of the embedded comma but "18 matthew's close" is imported correctly and includes the embedded single quote.

if i do use OPTIONALLY ENCLOSED BY "'" then "24, arly stret" is imported correctly, however this time the row that contained "18 matthew's close" is moved to the bad file because that embedded single speach mark breaks the file.

so i think the only way i can make sure that commas and embedded single quotes are imported into the column is by (and correct me if im wrong).. if i do use OPTIONALLY ENCLOSED BY "'" ... and then on that column in the control file put something in to escape that embedded single quote?

any idea how I would do this? escaping embedded single quotes that is?

Or do what I told you earlier and enclose your strings in " and then the apostrophes are imported correctly and the commas are also imported correctly. This is why I did not suggest you change your control file on the string enclosure character.

[dsub42]

but say this was not an option, how can i use a string function etc to escape the ingle quotes inbetween the enclosing single quotes?

im just trying to learn and play around, any help you could give me would greatly be appriciated

[dsub42]

The problem is that I have been given a csv file with about 20 columns, .. all the strings are enclosed in single quotes.. so theres not alot I can do to convert these into double quotes...

either way lets say i didnt convert the single quotes, is there not a function that will escape sinlge quotes in the string?

[zargon]

but say this was not an option, how can i use a string function etc to escape the ingle quotes inbetween the enclosing single quotes?

im just trying to learn and play around, any help you could give me would greatly be appriciated

You cannot. To preserve the apostrophes you need to change the string enclosure character to " because sql*loader takes every occurrence of the delimmiter pairs to define a string and your apostrophe terminates the string it is part of when you tell the utility to use ' to enclose strings. You have already discovered this behaviour. You could, of course, change all of your apostrophes to " then go through the data in the table and convert them back with translate() but you'd still be changing characters in your data file and it appears that changing characters in your data file is not an option simply because you don't want it to be. Yes, there are other options such as writing an OCI/OCCI program to load the data in whatever format you decide you want, writing a Pro*C program to do the load, create an external table and use insert statements to load your data from the external table to your 'permanent' table but using an external table would cause you to run into this same problem as external tables are processed by sql*loader.