Monday, July 27, 2009

How to deal with unwanted commas while reading .csv file.

While dealing with the .csv files i have come across with the situations where we have a string with a comma in it. and we do not want to split that string while splitting the whole line with the comma "," character.
In Such a case,
We can open the existing csv file in excel and then use replace function in excel to replace comma with some other character and then save the csv file. and then we can open and read the new csv file.

The code in VB.net looks like this;

Dim oExcel As New Excel.Application
Dim obook As Excel._Workbook
Dim osheet As Excel._Worksheet
obook = oExcel.Workbooks.Add()
obook = oExcel.Workbooks.Open(FIleName, , 43)
tabname = FIleNAme.Substring((FIleNAme.LastIndexOf("\") + 1), ((FIleNAme.IndexOf(".") - FIleNAme.LastIndexOf("\")) - 1))
osheet = obook.Worksheets(tabName)
osheet.Cells.Replace(",", ".")
obook.SaveAs("C:\NewFileName.csv")
obook.Close()