Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

7 Answers 7

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '10 at 8:22

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.

share|improve this answer
1  
+1 did this in excel 2010 and it worked perfectly. –  Nathan Reed Aug 22 '12 at 8:42
7  
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. –  ZweiBlumen Jan 27 '13 at 8:29
    
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 '13 at 14:02
    
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 at 11:08
    
Can confirm fwrite($fileHandle, 'sep=,' . "\r\n"); completely fixed the problem with fputcsv in PHP. –  h2ooooooo May 21 at 11:25

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.

share|improve this answer
    
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 '10 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 '10 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 '10 at 14:53

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

share|improve this answer
    
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 '10 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 '10 at 15:16
    
@Holgerwa: Please note my edit above regarding regional settings. –  harrymc Aug 26 '10 at 16:20

Had this problem last week.

The person who is trying to open the .csv file needs to change some settings within their control panel.

Control Panel > Regional and Language Options

Within the Regional Options tab select the customize button. Where it states "list separator" you'll find it has a comma in it. This needs to be changed to a semicolon (;)

Then select apply and then ok.

This should resolve the issue for the user.

Regards, Josie

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
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 '10 at 14:38

protected by Ivo Flipse Jul 9 '12 at 11:30

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.