Sqlloder | Member | | Join Date: Jul 2007
Posts: 78
| |
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?
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Sqlloder
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.
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Sqlloder
Quote:
Originally Posted by 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? 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",
-
| | Member | | Join Date: Jul 2007
Posts: 78
| | | re: Sqlloder
Quote:
Originally Posted by amitpatel66 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
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Sqlloder
Quote:
Originally Posted by holdingbe 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
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,382 network members.
|
