| CSV file as a Datafile in Sql*loader [message #474331] |
Sun, 05 September 2010 14:11  |
muktha_22
Messages: 527
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 #474335 is a reply to message #474332] |
Sun, 05 September 2010 14:35  |
muktha_22
Messages: 527
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 #474425 is a reply to message #474356] |
Mon, 06 September 2010 05:48 |
 |
Littlefoot
Messages: 21807
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 #474485 is a reply to message #474471] |
Mon, 06 September 2010 13:15 |
muktha_22
Messages: 527
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 #474493 is a reply to message #474492] |
Mon, 06 September 2010 14:19 |
 |
Michel Cadot
Messages: 68634
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 |
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
muktha_22 wrote on Mon, 06 September 2010 20:05Hi 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 |
 |
Barbara Boehmer
Messages: 9082
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
