CSV Regional Settings (Excel 2003 +)

Topic

New Reply

Hi All,

I now have global users of an application who have their Windows Regional Settings set to something other than English/United States (e.g. Finnish, etc) … this sets the Decimal Symbol as a comma and the List separator as a semicolon. The application we use downloads info to a Excel (via CSV) but comes out all in one column. (I know that the user could simply select the column and perform a Data > Text to Column using a comma but would rather change more permanently).

If I change just the List separator to a comma — it still doesn’t work because the Decimal Symbol is also a comma. If I change the Decimal Symbol to something else it does — but, of course, my Finnish users wish to continue using the comma as a Decimal Symbol.

So, is there a more permanent way for Excel to change the List separator from a semi colon to a comma.

As always, this forum’s help is deeply appreciated!!!
–cat

Reply | Quote

Replies

This is a pain. One option is to provide VBA code to open the .csv file: VBA always uses US settings, regardless of the user’s regional settings. Another is to change the extension to .txt. This will make Excel start the Import Text Wizard when the file is opened, so that the user can specify the delimiter.

Reply | Quote

It’s me again … I really have to think of a work-around for this issue …

Givens (things I can’t change but will be changed in the future by this company’s designers/developers):
–A CSV file is created as a pop-up that the user can either OPEN or Save … usually a user wishes to simply open the file
–currently CSV file type is opened with Excel (maybe I can do something here with switches???? )
–If the user has any other setting other then English(United States) as their Regional Setting …
the data is placed all in one column … with the important exception of the ALT+ENTER symbol (multiple lines in one cell)
this comes over as a new row incorrectly. Actually separating the quotes that denote the text field. So, even applying the Data | Text to Columns option in Excel doesn’t work correctly (due to ALT+Enter characters that are missing/forced into new row)

So, I’m thinking that maybe there’s a way to have CSV open with Excel (like it currently does),
–but have Excel automatically open a file that has a macro that does the work for the user after they click a keyboard combination that runs a macro that does:
(1) looks for that place that the special character should be … maybe by trying to count the quote symbols in a cell … if there are not two than do not break the row, and then
(2) do the text to columns using a comma.

But, I want the end user to be able to open the CSV file and simply click, say, CTRL+SHIFT+P, to run the macro, to produce the CSV file that his US counterparts get.

AS always, I really, really do appreciate all the help this forum gives me. I also feel a little guilty cause I know this can be done, and that I could probably learn how and do it, and I do usually do that approach … but, I know that by simply asking this group … I can save a lot of time … I don’t mean to be “using” you guys, but you really do help me a lot!!!

Thanks,
cat

I’ve uploaded an excel file (I couldn’t upload the csv files) … one tab represents what the Finnish user sees (but of course, it is a CSV file not a XLS file) with comments in C3 and J2, and an English tab with comments in J2 cell.

Reply | Quote

I don’t understand the part “A CSV file is created as a pop-up that the user can either OPEN or Save”. What does that mean?

If the .csv file is actually created as a file on disk, the following small macro can be used to open it correctly:

Sub ImportCSV()
Dim fileToOpen As Variant
fileToOpen = Application.GetOpenFilename(“CSV Files (*.csv), *.csv”)
If Not fileToOpen = False Then
Workbooks.Open fileToOpen
End If
End Sub

Reply | Quote

I don’t think it’ll be easy to convert the file once it has been opened incorrectly within Excel, since it’s virtually impossible to know whether a new row is actually intended as such or is the result of a line break in the original.

Reply | Quote

You could use some VBA code like this (not complete) to create the CSV file where rng is the range of cells to be written out:

strDelimiter = “,”

‘Create a file system object and use it to create a new
‘text file.
Set fso = New FileSystemObject
Set txs = fso.CreateTextFile(strFileName, True)

‘This loop is for looping from the top of the sheet to the bottom
For lngRow = 1 To rng.Rows.Count
For lngCol = 1 To rng.Columns.Count
txs.Write Chr(34) & rng.Cells(lngRow, lngCol) & Chr(34) & strDelimiter
Next lngCol
txs.Write vbCr
Next lngRow

txs.Close

Depending on the location you could have some code to change strDelimiter accordingly.

Nick

Reply | Quote