Workbook.SaveAs method save incorrect CSV file - French XP and Off

[Roman]

I use Workbook or Worksheet SaveAs(…) methods for saving a report as CSV file
in my application. The methods work fine if I run the application on PC with
U.S. Windows XP and U.S. Office 2003/2007.

But they don’t work properly on a PC with French Windows XP SP3 and French
Office 2007. The List separator character is semi-colon in the Microsoft
Windows regional settings for the French XP. So if I save as CSV from Excel
directly is saving the separators as a semi-colon and the CSV file display
correctly in Excel.

The issue is:

If I run my application on the French PC, and generate a report as CSV, I
get saving CSV file with comma separators instead of semi-colon. And the
French Excel cannot display the file correctly. The CSV formatted file looks
like the text file (i.e. all comma delimiters shown) when displayed in the
Excel.

Looks like, if you save as CSV file programmatically Excel ignores Regional
Settings. I need the CSV file with semi-colon separators when I save it
programmatically .

How to fix the issue? Any ideas?

The same issue happens on a PC with Germany Win XP and Germany Office 2003.

There is requirement: The MS Windows regional settings cannot be changed
manually or programmatically.

My code:

bool bReadOnlyRecommended = false ;

bool bCreateBackup = false ;

bool bAddToMru = false ;

bool bLocal = true ;

oWorkbook.SaveAs(“MyFileName.csvâ€,
Microsoft.Office.Interop.Excel.XlFileFormat .xlCSV, Type .Missing, Type
..Missing, bReadOnlyRecommended, bCreateBackup,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode .xlExclusive,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution
..xlLocalSessionChanges, false, Type .Missing, Type .Missing, bLocal);

Thanks,
Roman.

 

[Steve Rindsberg]

It sounds as though the real problem is that Excel is saving CSV files correctly
but it isn't *reading* them correctly.

As a workaround, have you tried tab-delimited files instead?

 

[Roman]

Steve,

How can I set tab as delimiter(separator) for Workbook.SaveAs(...) method?

Thanks,
Roman.

 

[Steve Rindsberg]

I think you'd need to use FileFormat:=xlText
(xlText = -4158)

This tells it to save as tab delimited (note: that's not the same thing as a comma-
separated value file with tabs substituted for commas).

 

[Darrell]

I'm a co-worker of Roman's. Unfortunately, the request from the customer was
specifically for a CSV file (maybe some automatted process they have only
works with that, not sure). This customer was US based so the problem Roman
described isn't a direct problem of thiers. However, obviously the feature
is added for all the customers, and we found this problem in French, as well
as some other languages.

When saved through Excel manually, the spreadsheet will save the CSV with
semi-colons separating the values. Excel also knows how to read these
semi-colon delimited CSV files, but cannot handle actual comma separated CSV
files. This is because of the fact that in French, the semi-colon is the
list separator, and the comma is the decimal separator.

However, if the CSV is saved through interop, it is always saving the CSV
with comma separated values, ignoring the culture settings. This CSV is then
unreadable by Excel, since in that culture it is expecting semi-colon
separated CSV files. We've verified that the culture settings of the thread
saving the CSV is set to French, which is why it's so confusing.

The interop call should act the same as if the the user manually used Excel
to save the CSV, but we don't understand why this is not happening.

Darrell

 

[Steve Rindsberg]

I'm a co-worker of Roman's. Unfortunately, the request from the customer was
specifically for a CSV file

I see. Ah well ... CSV it must be, then.

When saved through Excel manually, the spreadsheet will save the CSV with
semi-colons separating the values. Excel also knows how to read these
semi-colon delimited CSV files, but cannot handle actual comma separated CSV
files. This is because of the fact that in French, the semi-colon is the
list separator, and the comma is the decimal separator.

I suspect that, regardless of the locality, Excel *should* be using commas as the
list separator (the C in CSV does stand for "comma" after all).

However, if the CSV is saved through interop, it is always saving the CSV
with comma separated values, ignoring the culture settings. This CSV is then
unreadable by Excel, since in that culture it is expecting semi-colon
separated CSV files. We've verified that the culture settings of the thread
saving the CSV is set to French, which is why it's so confusing.

The interop call should act the same as if the the user manually used Excel
to save the CSV, but we don't understand why this is not happening.

This suggests "bug" even more emphatically.

As a workaround, how about reading the output file into a string variable, then
stepping through it and replacing any occurrences of "," with ";" then writing it
back out.

 

[Darrell]

I see. Ah well ... CSV it must be, then.


I suspect that, regardless of the locality, Excel *should* be using commas as the
list separator (the C in CSV does stand for "comma" after all).

That is what one would logically suspect, but that is not the behavior.
Excel will use the regional settings for the list separator for it's
separator, not just a comma. I can't find any "official" documentation about
that, but that is the behavior since at least Excel 2003, possibly sooner.
You can verify this for yourself by going into the regional settings of your
computer and changing the "List Separator" property then saving a CSV from
Excel.

This suggests "bug" even more emphatically.

As a workaround, how about reading the output file into a string variable, then
stepping through it and replacing any occurrences of "," with ";" then writing it
back out.

Unfortunately it's not that simple because of the fact that French also uses
the comma as a decimal separator, which means it's not just a simple replace,
I'd have to parse the CSV and determine by context if each individual comma
should be replaced or not.

I'm pretty sure the only bug here is on our side, we just can't seem to find
what we're doing wrong.

 

[Steve Rindsberg]

Unfortunately it's not that simple because of the fact that French also uses
the comma as a decimal separator, which means it's not just a simple replace,
I'd have to parse the CSV and determine by context if each individual comma
should be replaced or not.

I meant the quote marks above *literally* ... not as quotes around the characters.

That is, no matter what separators the file uses, they'll always be surrounded by quote
marks. Replacing the string

Chr$(34) & "," & Chr$(34)

with

Chr$(34) & ";" & Chr$(34)

should do it. For example:

"data 1","data 2, with comma","data 3"

After replacing "," with ";"

"data 1";"data 2, with comma";"data 3"