# [SOLVED] Excel 2007, removing commas from csv



## doubleudee (Oct 4, 2011)

I have a csv file which I open with Excel 2007 ready for cleansing (removing/replacing certain characters from the cells) to then import into sql server 2008.
I have been trying various methods to remove commas from the cells but without much success, I have tried the following methods:-

Columns("J:J").Select
Selection.Replace What:=",", Replacement:="~~",LookAt:=xlPart,SerarchOrder=xlbyRows,MatchCase:=False


Cells.Select
Selection.Replace What:=",", Replacement:="~~", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False _
, SearchFormat:=False, _
ReplaceFormat:=False

But I'm still finding commas in some of the cells (which affect my importing the file as they are seen as extra column delimiters on import).

Is there a better way of removing commas from the text in each cell?

Thanks


----------



## doubleudee (Oct 4, 2011)

*Re: Excel 2007, removing commas from csv*

---------------------------------------------------
Update:-

I have also tried this method (which works very slowy but removes most of the commas)
Columns("J:J").Select
For Each oCell In Selection
With oCell
.Replace What:=",", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
oCell.Font.Bold = True
Next

But I still have a couple of instance where commas are left in the text, these seem to be where they are surrounded by text, e.g

"This person has attended,but they left early"

The commas are removed from text where they are not completely surrounded by text as in:-

"This person has attended, but they left early"


This has me baffled a little, any help please?


----------



## RSpecianjr (Jan 20, 2010)

*Re: Excel 2007, removing commas from csv*

Hey doubleudee,

Are you trying to replace only the commas in column J?

Regards,

Robert D. Specian Jr.


----------



## doubleudee (Oct 4, 2011)

*Re: Excel 2007, removing commas from csv*

Sorry for the late reply - different time zones?



RSpecianjr said:


> Hey doubleudee,
> 
> Are you trying to replace only the commas in column J?
> 
> ...


No, there are commas in several columns, I just use J as an example.

I have to programmatically (vba - macro) remove the offending characters from all the columns concerned and the import the saved file into sql server, this is all done automatically.
I know which columns contain text (although it would be nice to scroll through the others as well, just in case - sort of Belts & Braces - lol).

The routine has been working fine for most part, but lately some older data has come in that needs to be imported, and this seems to have multiple instances of comma's in some of the columns, when this happens my 'Bulk Insert' fails due to it seeing extra commas which in turn makes the import think they are the end of last field/start of the next field.

I'm wondering is there a more robust way of removing commas (or any character) perhaps by using it's ASCII name? Just a thought.


----------



## doubleudee (Oct 4, 2011)

*Re: Excel 2007, removing commas from csv*

OK, I've solved it now.

I had to have excel format the columns in question as text, then i had to replaced the characters '=' which some of the cells had as a first character, (which gave me an extra error as it saw these as formula) then I replaced the comma's where required.


----------

