120

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.

1
  • 2
    You could recommend your users use LibreOffice Calc - it handles CSVs with arbitrary separators fine.
    – naught101
    Feb 15, 2017 at 2:57

7 Answers 7

-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.

3
  • yes, I think that's the best way to directly create XLS-files. I was hoping that there is some formatting for CSV-files that makes them internationally usable, but if that's not possible, XLS-files will work no matter where.
    – Holgerwa
    Aug 27, 2010 at 8:22
  • 26
    How is this the correct answer, it is useless.
    – tread
    Aug 20, 2016 at 12:54
  • 3
    Terrible answer - see the other upvoted answer.
    – jmc
    Sep 25, 2019 at 9:58
266

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.

13
  • 4
    +1 did this in excel 2010 and it worked perfectly.
    – N Reed
    Aug 22, 2012 at 8:42
  • 10
    Is ther a list of other such settings? Telling Excel to use , as the decimal separator usually goes along with telling it to use ; as the column separator.
    – user120041
    Jan 27, 2013 at 8:29
  • 7
    Works perfectly for Excel, but note that this may not be supported in other spreadsheet or data programs. It may only be a small issue, simply showing an extra first line with sep= or sep=; in it, but some automated programs or scripts may not expect the extra first line, resulting in the column headers or the sep=; line itself being interpreted as actual data.
    – Sygmoral
    Oct 1, 2013 at 14:02
  • 7
    Be careful with this. If you save in Excel, it will: - Remove the specification of the separator - Save with the regional setting separator
    – Bjinse
    Mar 17, 2014 at 11:08
  • 2
    Can confirm fwrite($fileHandle, 'sep=,' . "\r\n"); completely fixed the problem with fputcsv in PHP.
    – h2ooooooo
    May 21, 2014 at 11:25
31

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.

3
  • On Outlook 2007 got to the Data tab and select your colum and click on the Text to Columns button on the Data Tools sets.
    – Hondalex
    Aug 26, 2010 at 13:36
  • 1
    Yes, that's a solution. Unfortunately, the users who need to look at the data are not willing to do this. They can handle a "double-click-open", but won't be able to do anything more complicated. We have tried to educate them at least a little bit, but they refuse.
    – Holgerwa
    Aug 26, 2010 at 14:36
  • 1
    @Holgerwa - is it possible to run the files through some sort of conversion process before giving them to the users? Or do they need to remain semi-colon delimited? Other's have suggested this, but it should be fairly easy to swap semi-colons for commas using some sort of script (I'd advise against a simple find/replace because you don't want to convert those inside quotes).
    – DMA57361
    Aug 26, 2010 at 14:53
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

3
  • I agree, all valid options, but unfortunately, I am not in a normal situation with the users of these CSV-files. .TXT-import won't work (see my comment on DMA57361's answer), replacing the semicolons with commas won't work due to decimal separator issues, and changing the regional settings won't work because I don't have access to the PCs that are used. The users will just double-click files and if they don't get what they expect, they will complain and refuse to help in resolving the issue.
    – Holgerwa
    Aug 26, 2010 at 14:44
  • @Holgerwa: If you don't control the user PCs then there is nothing you can do. Because my other suggestion would be to change the handler to the .csv extension to be your own program, which will correct (but intelligently) the delimiters and call Excel.
    – harrymc
    Aug 26, 2010 at 15:16
  • @Holgerwa: Please note my edit above regarding regional settings.
    – harrymc
    Aug 26, 2010 at 16:20
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.

1
  • Actually, the OP's issue is the opposite. Should change to a European region to get comma as Decimal symbol and then semicolon will be the List separator.
    – yosh m
    May 22, 2017 at 13:51
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.

1
  • These CSV-files need to work in different countries (regional settings), and in many regional settings the comma is the decimal separator instead of the dot. A "standard" comma-separated-values file won't work. I guess that's the reason why semicolons were added as a valid delimiter for CSV-files.
    – Holgerwa
    Aug 26, 2010 at 14:38
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .