Sqlloder

[holdingbe]

Hi,

I loaded amount of data into one table.Table name is SAMPLE. Some of the data inserted correctly. Some data is inserted but not correctly.

My SQL Loder commands:

In command promt, I wrote like

Sqlldr userid=scott/tiger@flower control=sample.ctl log=sample.log

Sample Control file:

LOAD DATA
INFILE *
INTO TABLE SAMPLE
FIELDS TERMINATED BY ","
(
  ID,
  NAME,
  STREET
)
BEGINDATA
10,'MICHAEL',JAMKHYA ROAD,89,CROSS STREET,
20,'SATHEEH',JKOIYTT STKAJ,12,MAIN ROAD',
 

Data loaded like

ID NAME STREET

10 'MICHAEL' JAMKHYA ROAD
20 'SATHEEH' JKOIYTT STKAJ


But I need full values JAMKHYA ROAD,89,CROSS STREET in street columns.


What I found means, the oracle server accepts street columns values up to first comma.because I mention in control file fields terminated by “’ ”.so that only it writes like this.

How to avoid these errors?

[amitpatel66]

As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use the tags in future.

MODERATOR.

[amitpatel66]

Hi,

I loaded amount of data into one table.Table name is SAMPLE. Some of the data inserted correctly. Some data is inserted but not correctly.

My SQL Loder commands:

In command promt, I wrote like

Sqlldr userid=scott/tiger@flower control=sample.ctl log=sample.log

Sample Control file:

LOAD DATA
INFILE *
INTO TABLE SAMPLE
FIELDS TERMINATED BY ","
(
  ID,
  NAME,
  STREET
)
BEGINDATA
10,'MICHAEL',JAMKHYA ROAD,89,CROSS STREET,
20,'SATHEEH',JKOIYTT STKAJ,12,MAIN ROAD',
 

Data loaded like

ID NAME STREET

10 'MICHAEL' JAMKHYA ROAD
20 'SATHEEH' JKOIYTT STKAJ


But I need full values JAMKHYA ROAD,89,CROSS STREET in street columns.


What I found means, the oracle server accepts street columns values up to first comma.because I mention in control file fields terminated by “’ ”.so that only it writes like this.

How to avoid these errors?

Enclose the data in double quotes as shown below:

LOAD DATA
INFILE *
INTO TABLE SAMPLE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
  ID,
  NAME,
  STREET
)
BEGINDATA
10,"MICHAEL","JAMKHYA ROAD,89,CROSS STREET",
20,"SATHEEH","JKOIYTT STKAJ,12,MAIN ROAD",
 

[holdingbe]

Enclose the data in double quotes as shown below:

LOAD DATA
INFILE *
INTO TABLE SAMPLE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
  ID,
  NAME,
  STREET
)
BEGINDATA
10,"MICHAEL","JAMKHYA ROAD,89,CROSS STREET",
20,"SATHEEH","JKOIYTT STKAJ,12,MAIN ROAD",
 

Hi,

I have 200 hundred record..It will take long time .. please tell to other way

[amitpatel66]

Hi,

I have 200 hundred record..It will take long time .. please tell to other way

SQL LOADER allows two methods:

1. Terminated BY Optionally Enclosed BY
2. POSITION (in case if the data is of fixed length, but it is not so in your case)

you need to go with method 1.

If you have a textpad, copy paste the data in it, create a macro to enclose the text with double quotes and use it.

You can also do that with a excel sheet as well