124

When I double-click on a .csv file, it is opened in Excel. The csv-files have columns delimited with semicolons (not commas, but also a valid format).

Using a German Windows/Excel setup, the opened file is displayed correctly, the columns are separated where the semicolons existed in the csv-file.

But when I do the same on an (US-) English Windows/Excel setup, only one column is imported, showing the whole data including the semicolons in the first column.
(I don't have an English setup available for tests, users have reported the behavior)

I tried to change the list separator value in Windows regional settings, but that didn't change anything.

What can I do to be able to double-click-open those CSV-files on an English setup?

EDIT:
It seems to be the best solution not to rely on CSV-files in this case. I was hoping that there is some formatting for CSV-files that makes it possible to use them internationally.
The best solution seems that I'll switch to creating XLS-files.

studiohack
  • 13,477
Holgerwa
  • 1,534

7 Answers7

273

Add this on the first line of the CSV file:

sep=;

or

sep=,

This will override system setting ("list separator character") and Excel will open the file correctly.

slhck
  • 235,242
atx
  • 2,739
34

You can use the Text Import Wizard. This does not work on a double-click-open, but is the best I can come up with that doesn't involved messing with lanugage settings.

In Excel 2003 goto Data -> Import External Data -> Import Data on the menu bar (can't say where it is on 2007, I don't have that to hand at the moment). Find and select your CSV file.

When it opens you get the Text Import Wizard, which allows you to specify the delimiter(s) to use.

DMA57361
  • 18,793
6

See this article:
Trouble with Opening CSV Files with Excel? The Comma and Semicolon Issue in Excel due to Regional Settings for Europe

Which offers 3 solutions:

  • Change the CSV file extension to TXT (when you open a TXT file with Excel it will start the text file import wizard where you can choose the delimiter)
  • Replace all “,” with “;” in your CSV file(s)
  • Change your regional and language settings

As you have only changed the list separator value in Windows regional settings, I note that the article says to enter “.” for Decimal Symbol and “,” for List Separator.

If you feel comfortable with VBA, then see:

Opening semicolon delimited CSV file with VBA in Excel 2000
Exporting And Import Text With Excel

Community
  • 1
harrymc
  • 498,455
3

The best way I have tried is to set your computer regional settings as below:

  • in the "Standard and Formats" choose "English (United States)";
  • in the "Locations" choose "United States".

It works for me, just give it a try.

Alex
  • 1,865
jaz
  • 31
0

In PowerShell, use Set-Culture to set the locale and then open the ;-separated CSV in Excel:

Set-Culture de-DE   # en-US to revert back
xjcl
  • 471
  • 5
  • 11
0

If there are no other commas in the file, you can simply do a find/replace in a text editor on the semicolons to turn them into commas. Treat as normal csv file after that.

DHayes
  • 2,183
-5

Those files are distributed? downloaded? Locally generated?

I think the unique way to solve your issue without access to the user's PC and without him to have to do something different of "double-click" on the file is to convert the files to .xls before distibuting or placing to download or to generate a .xls (or another format that works) instead of a .csv file. You can do that with a script or manually with a working Excel, depending on the number of files and the way they are generated.

laurent
  • 4,448