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)

 

[mrcamo]

sorry i have sorted that out aswell.. max characters is 255 unless specified

 

[mrcamo]

do you know how i can format dates in this format via the control file..?

31/01/2001 00:00:00

 

[mrcamo]

considering some colums aare empty and some contain dates in that format

 

[Dagon]

Have you tried:

regdate DATE "DD/MM/YYYY HH24:MI:SS"

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

 

[mrcamo]

yes cheers, thats fine, another problem however..

one field in the database I am exporting from is Time..

i.e 07:40:00 ....

but when I import this into access and then export as CSV it seems to be adding a date to it...

instead of showing "07:40:00" it shows "30/12/1899 07:40:00"

no idea where this date is comming from...

do you know how I can remove the date part when im importing the values? (also what is the field type for time in oracle?)

 

[Dagon]

There isn't a TIME field. There is only DATE, which is a date and time. If there is only time present, then Oracle will default the lowest possible system date for the DATE. If you're only interested in storing the time, then it probably doesn't matter what the date is set to. Alternatively, you could change the field to VARCHAR2 or default a different date using a SQL function.

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