Workbooks.Open function ignores set delimiter

Question

I need to convert a csv-file to xlsx. I want to use the interop functionality.

The csv file is semicolon separated, but the open function ignores the set delimiter.

Here's my code:

Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbooks workbooks = app.Workbooks;
Excel.Workbook wb = app.Workbooks.Open(sourceFile, 
                                       Type.Missing,
                                       Type.Missing,
                                       Excel.XlFileFormat.xlCSV,   // Format
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       ";",          // Delimiter
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing,
                                       Type.Missing);
workbooks[1].SaveAs(newXLSXPath, Excel.XlFileFormat.xlOpenXMLWorkbook);
workbooks.Close();

When I open the new xlsx file, I can see that the csv file was opened using ',' as separator.

What's wrong here?

Have a look at: https://stackoverflow.com/questions/39890471/import-semicolon-separated-csv-file-using-vba — Rand Random, Aug 29 '21 at 14:59

score 1 · Accepted Answer · answered Aug 29 '21 at 15:07

Thanks to Rand Random's comment, I read that question again a bit more carefully and found a difference.

You have to set the "Local" argument to true! Otherwise it ignores the delimiter and uses the default comma.

Here's the working code:

Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbooks workbooks = app.Workbooks;
Excel.Workbook wb = app.Workbooks.Open(sourceFile, 
                                   Type.Missing,
                                   Type.Missing,
                                   Excel.XlFileFormat.xlCSV,   // Format
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing,
                                   ";",          // Delimiter
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing,
                                   true,  // <--- THIS WAS MISSING!
                                   Type.Missing);
workbooks[1].SaveAs(newXLSXPath, Excel.XlFileFormat.xlOpenXMLWorkbook);
workbooks.Close();

Workbooks.Open function ignores set delimiter

1 Answers1