control file help

[mrcamo]

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)

 

[SantaMufasa]

MrCamo,

If you can post a couple of abbreviated rows of data (including commas and quotes that you expect), we can show some nifty ways to deal with the data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mrcamo]

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,

 

[Dagon]

Doing either:


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

or

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

seems to work. You won't then need the SUBSTR to remove the single quotes.

For Oracle-related work, contact me through Linked-In.

 

[mrcamo]

unfortunatly neither of them aproaches work, the second one is in my origional post,

When porting data into oracle from that csv via sqlldr the enclosing single quotations on strings are carried accross..

I have solved the problem using substr, but now it is the problem with the comma in the address field...

'24,arly Street'

any ideas?

 

[mrcamo]

Sorry forgot to say... im importing into a different table than my controll file shows in the first post:

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
InterestRate - ?? decimal ??

 

[Dagon]

Are you sure you're doing it correctly ? It works perfectly well on my system. What I had was:

(single quote) (backslash) (single quote) (single quote)

or

(double quote) (single quote) (double quote)

Yours looks more like:

(single quote) (double quote) (single quote)

I think the only way to solve your problem is to get the enclosed characters to be interpreted correctly. I can't see otherwise how you can distinguish between a comma in the string and a comma used as a delimiter.


For Oracle-related work, contact me through Linked-In.

 

[mrcamo]

sorry yes " ' " does work.

however the comma in the row is a problem, as that comma breaks the fields, iv got 2000 rows, some of which in that field contain a comma,

surely theres something I can do or a method of formatting the column to ignore commas between the enclosed single quotes other than going through 2000 rows and looking manually?

 

[Dagon]

I'm a bit puzzled. That is exactly what the optionally enclosed by clause is supposed to do. I tested it on my system with your data and it loads correctly. For instance, I get "24,arly Street" loaded into address1.

For Oracle-related work, contact me through Linked-In.

 

[mrcamo]

sorry let me explain (my fault for being unclear)..

lets say I had data that was like ->

'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 and no substring function 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?
(hope that makes more sense, sorry im losing the plot)

 

[Dagon]

Since the file you're trying to load is fairly small, perhaps the simplest approach might be to edit it and replace all the single quotes with double quotes.

One simple way to achieve this would be to look for all cases of ', and replace them with ",. Then look for all cases of ,' and replace them with ,". There would then only be a problem if one of those combinations appears in your data, which seems unlikely. If you're on UNIX, you could probably use something like SED and do something a bit more sophisticated using regular expressions.

For Oracle-related work, contact me through Linked-In.

 

[mrcamo]

the file im trying to upload is over 1200 rows, I have just shown an extract.

In the address field, some rows contain embedded commas and some contain embedded single quotes..

When I use OPTIONALLY ENCLOSED BY "'" it overcomes problems with commas but not embedded single quotes

is there not a string function or something that I can use from the controll file to remove or replace embedded single quotes for that column?

 

[Dagon]

I don't think so. You'd either have to change all your embedded single quotes to pairs of single quotes or do as I suggested and change the enclosure character to something else.

For Oracle-related work, contact me through Linked-In.

 

[mrcamo]

so there is no SQL string function that will escape single quotes in a string !?!?

 

[Gruuuu]

mrcamo... the problem, as you've suggested, comes from IMPORTING poorly collected data. With the combination of formatting that you have, it appears that you'll need to scrub your data before importing it. Dagon's solution (find/replace your string terminators) should work.

 

[mrcamo]

I ahve done that, but I would also like to know how i could use a string function or something like that via the control file on the column that will escape single quotes between enclosing single quotes... surely it can be done??

 

[mrcamo]

O.K I see what your both saying now... I have 2 questions,,

Considering I dont have the source application and only have the CSV file...

1.How do I replace single quotes with doubles quotes on every column?

2. One column was exported without quotes or single quotes (it was a memo field) how can I add double quotes to this column?

 

[Dagon]

1) Any editor should be able to do a simple search and replace across the file. There are tools like SED in UNIX which can be used to script the update.

2) Where does the memo field appear ? If it's the last field, it might be a bit easier to do. Otherwise, it's potentially quite tricky.

For Oracle-related work, contact me through Linked-In.

 

[mrcamo]

I have fixed those issues, i managed to get a copy of the origional data and imported it into access and then exported as a csv using " (this worked for what was the memo field)

But...

the csv contains some record where the line breaks half way through the memo field (carridge return possibly?)

i.e

111,222," the line is breaking
like this",
222,333 " this line is not broken",

sp its messing up certain rows on the import, aside from going into the csv and deleting the line break manually (considering there is thousands of rows), is there any way i can format the control file?

Any help would be apriciated

 

[mrcamo]

Another problem im facing...

say i manually remove all the character returns (in the csv)

there is one row that keeps failing.. there is no line break in the row in the csv but the field is 340(including spaces) characters long.

iv been testing it with random strings and anything of that size seems to fail (does it put a character return in after so many characters or something? )

when testing it with a string that was smaller at 220 characters (including spaces) it works...

as in literally i have put in "a a a a a ...x 350" which fails

and "a a a a .. x 220" and it works

is there a limit a field can be? how can i get round this problem?