Excel incompatible with itself

Question

So I have a csv I created with excel in this format:

col1;col2;col5
a;b;c
d;e;f

I want to change it to the more usual " and , how can I do that as of now every change either changes the encoding or keeps the ;

Which in the end is not recognized by excel anymore and leads to localization issues like Libell, instead of Libellé.

BTW what is the default encoding of excel I tried UTF-8 and windows-1252/cp1252 with no success ?

thanks

downvoter must be an excel fan boy :D , feedback ?
– maazza
Commented Dec 14, 2015 at 15:28 — maazza, Commented Dec 14, 2015 at 15:28

Eddie Dunn · Accepted Answer · 2015-12-14 15:38:27Z

1

I think the easiest thing to do would be to:
Paste the data in to a new workbook
Use Data -> Text to Columns and select the semicolon.

That should get it sanely imported where you can manipulate and save however you want. If you just save as csv from there it will default to commas.

answered Dec 14, 2015 at 15:38

Eddie Dunn

9311 gold badge6 silver badges11 bronze badges

will try that any idea on encoding ?
– maazza
Commented Dec 14, 2015 at 15:41
no Excel for me :/
– maazza
Commented Dec 14, 2015 at 19:11
You can change the encoding by going to Options -> Advanced -> Web Options... -> encoding tab
– Eddie Dunn
Commented Dec 14, 2015 at 19:43

Add a comment |

Aganju · Accepted Answer · 2015-12-14 15:29:14Z

2

The default Excel uses for CSV is what you define for your Windows system, under "Control Panel\Clock, Language, and Region\Change date, time, or number format", and then "Additional Settings". There is a field for "List Separator" which is usually "," but could be ";" (or whatever you like)
You cannot change that Excel puts " around text fields (and not around numbers). You can change all your columns to 'Text' format in Excel, and then it will use " around all values, but that is tedious and has other negative consequences

answered Dec 14, 2015 at 15:29

Aganju

10k3 gold badges24 silver badges43 bronze badges

I am using version 2013 are you sure I have to change my whole system for something so trivial ?
– maazza
Commented Dec 14, 2015 at 15:31
Excel has no customization to tell which separator to use, and it works that way (I do change mine often). So, yes. You might not like it, and I don't claim it's great, but it works, and I doubt there is another way (except coding Macros).
– Aganju
Commented Dec 14, 2015 at 15:35
ok thanks, feel bad for microsft developers tough O_O any idea on the default encoding utf-8 and windows-1252 dont work
– maazza
Commented Dec 14, 2015 at 15:36

Add a comment |

2 Answers 2