Home » RDBMS Server » Server Utilities » CSV file as a Datafile in Sql*loader (Oracle 9i.0.2, Windows. )
CSV file as a Datafile in Sql*loader [message #474331] Sun, 05 September 2010 14:11 Go to next message
muktha_22
Messages: 397
Registered: December 2009
Senior Member
Hi,

1) can we use a CSV file as a Data file in any format (fixed, delimited...) of Sql loader. I tried, but not succeeded.

2) if not then tell me the reason for it....

3) Also tell me is there any restriction on using the file format for a datafile?

Thanks in advance
Re: CSV file as a Datafile in Sql*loader [message #474332 is a reply to message #474331] Sun, 05 September 2010 14:17 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>1) can we use a CSV file as a Data file in any format (fixed, delimited...) of Sql loader. I tried, but not succeeded.
Yes, but Control file must match actual file layout.
Re: CSV file as a Datafile in Sql*loader [message #474335 is a reply to message #474332] Sun, 05 September 2010 14:35 Go to previous messageGo to next message
muktha_22
Messages: 397
Registered: December 2009
Senior Member
Hi Blackswan,

Nice to have u back, and thanks for the answers.,
As I'm using mobile internet which is not connected to the PC, I explained the outline.

Load data.
"
Into table emp
(empno, job, mgr)

The excel file has the value as below.

7789| |clerk
5632| jay |asst
"

Thanks in advance
Re: CSV file as a Datafile in Sql*loader [message #474340 is a reply to message #474335] Sun, 05 September 2010 15:09 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the problem, actually? Looks simple.
Re: CSV file as a Datafile in Sql*loader [message #474345 is a reply to message #474340] Sun, 05 September 2010 15:40 Go to previous messageGo to next message
muktha_22
Messages: 397
Registered: December 2009
Senior Member
Hi,

But it shows me error, should I use field terminated by ','?

I just placed the data in the separated excelled cell.

please help me
Re: CSV file as a Datafile in Sql*loader [message #474351 is a reply to message #474345] Sun, 05 September 2010 15:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
CSV is an abbreviation for comma-separated values, which means that the values are separated by commas, or in other words the fields are terminated by commas. "," is a comma. What you have is not a CSV file; It is a file where the values (fields) are separated (terminated) by "|", so you should use:

fields terminated by '|'


Re: CSV file as a Datafile in Sql*loader [message #474352 is a reply to message #474351] Sun, 05 September 2010 15:59 Go to previous messageGo to next message
muktha_22
Messages: 397
Registered: December 2009
Senior Member
Hi Barbara,

So when we use CSV format datafile, we must set TERMINATED BY ','?
Re: CSV file as a Datafile in Sql*loader [message #474355 is a reply to message #474352] Sun, 05 September 2010 16:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
muktha_22 wrote on Sun, 05 September 2010 13:59

So when we use CSV format datafile, we must set TERMINATED BY ','?


Yes.
Re: CSV file as a Datafile in Sql*loader [message #474356 is a reply to message #474355] Sun, 05 September 2010 16:27 Go to previous messageGo to next message
muktha_22
Messages: 397
Registered: December 2009
Senior Member
Fine.......
Re: CSV file as a Datafile in Sql*loader [message #474425 is a reply to message #474356] Mon, 06 September 2010 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Barbara is far more experienced with SQL*Loader, but - I guess it won't hurt if I say something.

"CSV" surely is a "comma separated values", but I have noticed that people often use the "CSV" abbreviation for all kinds of "separated values" files used with SQL*Loader. Separator can be comma (,), pipe (|), semi-colon (;), whatever. People I mentioned don't say "PSV" (pipe separated values), but it is still a "CSV".

Furthermore, it means that TERMINATED BY clause should point to a correct separator. If values in your "CSV" file are separated by comma, you'd use comma in TERMINATED BY. If values were separated by pipe, you'd use pipe in TERMINATED BY. Etc.

I guess you got the point.
Re: CSV file as a Datafile in Sql*loader [message #474471 is a reply to message #474425] Mon, 06 September 2010 11:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
I started a separated thread on incorrect usage of terminology and such, beyond just CSV:

http://www.orafaq.com/forum/m/474467/43710/#msg_474467

Re: CSV file as a Datafile in Sql*loader [message #474485 is a reply to message #474471] Mon, 06 September 2010 13:15 Go to previous messageGo to next message
muktha_22
Messages: 397
Registered: December 2009
Senior Member
Hi Barbara,

Still I'm facing problem.
please help me on the below.

Load data
Infile 'path\case2.csv'
Truncate
Fields terminated by ','
Into table emp1
(empno, jname, age, sal)

In the excel file I have the below data and stored it as a case2.CSV.

7785 | clerk | <blank> | 58643 |
6352 | mgr | 35 | 85329 |

Thanks in advance
Re: CSV file as a Datafile in Sql*loader [message #474486 is a reply to message #474485] Mon, 06 September 2010 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fields are NOT terminated by "," but by "|".

Regards
Michel
Re: CSV file as a Datafile in Sql*loader [message #474492 is a reply to message #474486] Mon, 06 September 2010 14:05 Go to previous messageGo to next message
muktha_22
Messages: 397
Registered: December 2009
Senior Member
Hi Mickel,

Not used,
As Barbara said in the previous answer, all the excel data files needs to be terminated with ','.

But u say '|'.

I tried both of them.

please help me.......
Re: CSV file as a Datafile in Sql*loader [message #474493 is a reply to message #474492] Mon, 06 September 2010 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
all the excel data files needs to be terminated with ','.

You wrongly read what Barbara said. She said that, stricty speaking, a CSV file is "comma ...".
Your data are "pipe" separated values.

In addition, Excel can understand any CSV (in extended usage) file. Just see the Data/Convert command in your Excel.
But the CSV file it can generate (Save as...) depends on your version, in French it is semi-colon and not comma.

Regards
Michel

[Updated on: Mon, 06 September 2010 14:22]

Report message to a moderator

Re: CSV file as a Datafile in Sql*loader [message #474494 is a reply to message #474485] Mon, 06 September 2010 14:23 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
muktha_22 wrote on Mon, 06 September 2010 20:05
Hi Mickel,


As Barbara said in the previous answer, all the excel data files needs to be terminated with ','.

But u say '|'.


1) csv technically means comma seperated but excel doesn't need it and can work with other delimeters.
2) It was you who said it was |
muktha_22 wrote on Mon, 06 September 2010 19:15

In the excel file I have the below data and stored it as a case2.CSV.

7785 | clerk | <blank> | 58643 |
6352 | mgr | 35 | 85329 |

What is the data in the file actually seperated with?
Whatever it is, that's what you need to specify in the ctl file.

3) Telling us what error you get would also help.
4) Learn to spell Michel's name right.


Re: CSV file as a Datafile in Sql*loader [message #474497 is a reply to message #474492] Mon, 06 September 2010 14:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
Originally CSV stood for comma-separated values, but in more recent times, CSV has been used more loosely to mean character-separated values, where the character can be any character, not just a comma. When that character is a comma, then you need to use "terminated by ','". When that character is some other character, then you need to use whatever character that is. In the data that you posted, the character that separates your values is '|', so you need to use "terminated by '|'", as I said before. If you have tried this and it does not work, then you need to post what results you got. Was there an error message or just no rows loaded? What did the log file say? You need to find a way to post a copy and paste of what you actually did, not just typing an excerpt. It may be that some part that you have not posted is what is causing the problem. We need to see the whole thing.


Re: CSV file as a Datafile in Sql*loader [message #474500 is a reply to message #474497] Mon, 06 September 2010 15:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
You have some lines in your SQL*Loader control file in the wrong order. The "fields terminated by ..." needs to be after the "into table ...", as shown below.

Load data
Infile 'path\case2.csv'
Truncate
Into table emp1
Fields terminated by '|'
(empno, jname, age, sal)
Re: CSV file as a Datafile in Sql*loader [message #474507 is a reply to message #474500] Mon, 06 September 2010 15:15 Go to previous message
muktha_22
Messages: 397
Registered: December 2009
Senior Member
Hi All,

Thanks for your answers.
Previous Topic: Errors While Import.
Next Topic: Loading data to another schema
Goto Forum:
  


Current Time: Wed Oct 01 23:32:04 CDT 2014

Total time taken to generate the page: 0.09118 seconds