Home » SQL & PL/SQL » SQL & PL/SQL » Removal of comma from CSV file while creating External Table
Removal of comma from CSV file while creating External Table [message #206416] Thu, 30 November 2006 00:18 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to create an external table from an excel file.
The excel file is containing some numeric columns like OH and QTY as given below.
I am converting the excel to csv by using FILE->> Save AS -->> to csv.
The external table is created using the csv file.
But when I am trying to select the data, this is giving wrong values as this is taking "," (comma) as column seperators.
Rpt_Grp   	           Categories   	OH	QTY
No Dmd for 53 wks	Total for ACCESSORIE    34,194	100,039
No Dmd for 53 wks	Total for RUGS  	21,682	142,820
No Dmd for 53 wks	Total for SHWR CURT	24,508	115,694

And I can't do this manually as this is a part of automatic process. Everytime the data would change and the excel data is a result of a crystal report which can't be changed.
Please suggst me a way to convert the excel into csv where the comma from column OH and QTY can be removed by some sql code without manually changing the excel file.

Thanks,
Mona

[Updated on: Thu, 30 November 2006 00:19]

Report message to a moderator

Re: Removal of comma from CSV file while creating External Table [message #206422 is a reply to message #206416] Thu, 30 November 2006 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you use as column-separators? How would YOU know if a comma should be interpreted as a thousands-separator?
Re: Removal of comma from CSV file while creating External Table [message #206426 is a reply to message #206422] Thu, 30 November 2006 00:36 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
I have an excel sheet generated from crystal report. The columns are formatted in CR as thousand seperator or something like that.
There may be descripton fields as well where the data in one column can be like "jim, robinson". And other time in numeric columns it can be like "123,45,567.00".
This excel needs to be converted into csv which later can be used for creation of external table.
So the comma is creating problem here as it is misguiding in respect of columns.
Please provide a solution for this.


Thanks,
Mona
Re: Removal of comma from CSV file while creating External Table [message #206450 is a reply to message #206422] Thu, 30 November 2006 02:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Thu, 30 November 2006 07:31
How would YOU know if a comma should be interpreted as a thousands-separator?


If YOU as a human look at the csv, can YOU tell wich columns should maintain their comma?
Re: Removal of comma from CSV file while creating External Table [message #206458 is a reply to message #206450] Thu, 30 November 2006 02:35 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I have got the way to overcome the comma.
While creating the external table I used
OPTIONALLY ENCLOSED BY '"'

and my problem is solved.
I also changed the datatype from NUMBER to VARCHAR2

Regards,
Mona

[Updated on: Thu, 30 November 2006 02:38]

Report message to a moderator

Previous Topic: How to strip of characters
Next Topic: DBMS_XMLGEN
Goto Forum:
  


Current Time: Tue Dec 06 08:21:47 CST 2016

Total time taken to generate the page: 0.14940 seconds