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.

I want to save an excel sheet as a csv file.
I choose 'Save as', select CSV file format and click on 'Save'.

When I open this saved file in textpad I see that excel used the ';' (semicolon) as delimiter for the cells.

I searched google for this problem and it was said that the list separator in the regions additional format settings should be changed.

I changed the systems list separator to ',' (comma), restarted excel tried to save it again, but still the semicolon is there. I restarted my computer, tried to save it again, but still the semicolon is there.

I searched all options of excel to set the list separator manually, but nothing there.

My questions:

  • How to set the list separator for excel 2010?
  • Why does excel ignores my changed list separator? (and how to repair it?)

Greetings, Mixxiphoid

share|improve this question
add comment

migrated from stackoverflow.com Apr 3 '12 at 18:15

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 2 down vote accepted

I finally found the answer.
It seems that the semicolon is a backup delimiter that excel uses if it can't use the comma for some reason.

That reason was in my case that the decimal symbol was already using the comma. Apparently it is not allowed to use the same character for the decimal symbol and the list separator.

To answer my questions directly:

  • How to set the list separator for excel 2010?
    You can set it in the Regional and Language settings.
  • Why does excel ignores my changed list separator? (and how to repair it?)
    It is possible that the decimal symbol uses the same symbol as the list separator, this is not allowed.
share|improve this answer
    
After a few searches over a year, I found my answer here ! Thank you. Had to change decimal commas to dot. –  David Bélanger Mar 13 at 20:08
    
@DavidBélanger Glad to see the solution also worked out for someone else :) –  Mixxiphoid Mar 13 at 20:10
add comment

The problem is that when writing a CSV file, Excel will use the local list separator but when closing the workbook it will overwrite the file with the default separator.

Solution to the problem is therefore:

Manually: Save As and then click NO when closing the workbook and excel asks to save the file

VBA do not use "Active.Workbook.Close True" but use "Active.Workbook.Close False"

this will force the local list separator to be used.

share|improve this answer
    
I did not know, this information is surely helpful, thanks :). –  Mixxiphoid Jan 18 '13 at 7:29
add comment

This answer seems to propose what you have already tried. Not sure why it did not work for you.

If you don't have any ; or , in the content of the cells, you could just open the file in Notepad and run a find&replace.

Alternatively, you can manually create the CSV file with some VBA code - see this and that for example. That gives you full control on the delimiter that you want to use.

share|improve this answer
    
Well thats the problem... The excel file has about 17 million cells. It would be way too much work to correct all already existing comma's. –  Mixxiphoid Mar 27 '12 at 9:19
    
I already thought about importing the excel file into memory and writing it to csv with a c# application, but still my excel installation seems to be broke/not working. I need to fix that too. –  Mixxiphoid Mar 27 '12 at 9:20
    
Then VBA it is I suppose. Let us know if you have problems adapting the examples I linked to. –  assylias Mar 27 '12 at 9:20
    
You could do it in C# but it should be fairly straightforward and fast enough in VBA. –  assylias Mar 27 '12 at 9:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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