1

I have a next code for converting excel file to csv file: 

Microsoft.Office.Interop.Excel.Application reportExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks reportBooks = null;
Microsoft.Office.Interop.Excel.Workbook reportBook = null;
Microsoft.Office.Interop.Excel.Sheets reportSheets = null;
Microsoft.Office.Interop.Excel._Worksheet reportSheet = null;
try
{
    reportBooks = reportExcel.Workbooks;
    reportBook = reportBooks.Open(excelFilePath);
    reportSheets = reportBook.Sheets;
    reportSheet = reportSheets.get_Item(1);
    if (File.Exists(csvtempFile))
    {
        File.Delete(csvtempFile);
    }
    reportBook.SaveAs(csvtempFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, false, false);
    ...
}
catch (Exception ex)
{
    ...
}
finally
{
    ...
}

I get next text in csv file:

...
"Some strings","309,145","4,964,398",,"1,194,780",,
...

As you see numbers contain an extra comma. Please tell me how to remove extra comma for getting next value:

"Some strings","309,145","4964,398",,"1194,780",,
Horman Lewis
  • 346
  • 2
  • 5
  • 13
  • You mean you want `,"309145","4964398",,"1194780",,` instead? – Lasse V. Karlsen Nov 05 '14 at 15:04
  • 2
    *Which* extra commas? The ones indicating thousands, or the ones indicating empty columns? – Some programmer dude Nov 05 '14 at 15:05
  • I wrote: "As you see numbers contain an extra comma", so: The ones indicating thousands. – Horman Lewis Nov 05 '14 at 15:09
  • This is a reason you should try to *not* implement your own CSV parser. While the format is deceptively simple, it has lots of corner cases and exceptions like the one you just found. There are many libraries, if you just search a little, which handles these cases very well. There's also another problem with CSV files: No meta-data. Unless there's a formal specification of what each field means, you can tell what kind of data is in the fields. – Some programmer dude Nov 05 '14 at 15:15
  • So my advice to you is: Don't make your own parser, and use a converter for the contained data that can handle the format it's in (like numbers with commas as thousands-separator). – Some programmer dude Nov 05 '14 at 15:16

1 Answers1

0

Check out this post: Set data type like number, text and date in excel column using Microsoft.Office.Interop.Excel in c#

You can check column type, set a range on each one you want to change and then set the number format before exporting it.

I know it is not pretty, but might work if you don't find something prettier.

Community
  • 1
  • 1
rodrigogq
  • 1,943
  • 1
  • 16
  • 25