Non-english language (Norwegian) Mac is placing semi-colons in instead of commas as CSV separators (in Excel 2008). Can this be changed to commas?
We have software that takes CSV files as input. A user is using a Mac laptop to enter her data into Excel, then saving the file as CSV. The problem is this: If we look at the CSV file (that was saved by Excel) in a text editor (ex: TextEdit on the Mac), the output delimiter is a semi-colon instead of commas appearing between each data item. The format we need for the other software must have commas.
Saving this file as CSV is hurdle enough for this user, so I don't want to as her to do save as CSV, open the CSV in TextEdit, do a find/replace for ; to , ... That would be confusing for her to do.
I did try changing the language on the laptop to English, then doing the save as CSV from Excel, and commas do correctly appear. This too is not a solution, as she doesn't speak English very well, and should be able to have her laptop in Norwegian if she wishes.
Are there any other solutions to this problem?
Is there another way to force commas to appear as the delimiter?
We also tried OpenOffice's Calc, but had the same results (so, it definitely seems to be an issue stemming from the language setting). Actually, OpenOffice worked better at correctly opening the CSV file initially, but still saved the semi-colons out.
Any solution with OpenOffice Calc, Excel, or any other CSV Editing software that will work on a Mac is welcome!
Saving this file as CSV is hurdle enough for this user, so I don't want to as her to do save as CSV, open the CSV in TextEdit, do a find/replace for ; to , ... That would be confusing for her to do.
I did try changing the language on the laptop to English, then doing the save as CSV from Excel, and commas do correctly appear. This too is not a solution, as she doesn't speak English very well, and should be able to have her laptop in Norwegian if she wishes.
Are there any other solutions to this problem?
Is there another way to force commas to appear as the delimiter?
We also tried OpenOffice's Calc, but had the same results (so, it definitely seems to be an issue stemming from the language setting). Actually, OpenOffice worked better at correctly opening the CSV file initially, but still saved the semi-colons out.
Any solution with OpenOffice Calc, Excel, or any other CSV Editing software that will work on a Mac is welcome!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Won't the user still have to change the decimal separator to something other than a comma so the CSV file does not have extraneous commas?
When saving from the interface or with the argument Local:=True, Excel will use the local decimal and list separators, as well as the selected date format if applicable. With Local:=False, default US settings are used: period as decimal separator, comma as list separator, and US date format.
Cheers!
(°v°)
Cheers!
(°v°)
Sounds like that should work then. Good tip!
ASKER
Harfang, this is a great tip -- it sounds like this should work.
I've created macros before and can run them from the macro menu, but what would be the easiest way to make this macro always available to this user no matter what file she is in? When she will need to run it, she could be in any number of CSV files (they are always newly, dynamically generated, so they will differ each time).
One more question -- will this syntax also work in OO Calc, or only Excel?
I've created macros before and can run them from the macro menu, but what would be the easiest way to make this macro always available to this user no matter what file she is in? When she will need to run it, she could be in any number of CSV files (they are always newly, dynamically generated, so they will differ each time).
One more question -- will this syntax also work in OO Calc, or only Excel?
CSV files cannot contain macros, naturally. The most logical place for this line of code is probably the personal macro workbook. If it doesn't exist yet, the easiest way to create it is to record a new macro and to select this option in the dialogue option “save macro in”. You can later switch to the Visual Basic Editor (Alt+F11), select the personal macro workbook in the project explorer at the left, delete the recorded macro, and create your own macro(s) from scratch. To run the macro, you can instruct the user to select “run macro” or assign it to a toolbar button (up to version 2003).
As for OO Calc, I'm not sure. There is some support for VBA in the Windows version, but probably not on the Macintosh (at least I didn't find any references). OO supports many other scripting languages, so I'm quite confident that something similar can be done, but I'm not sure I can help you with that for lack of experience. On the other hand, it's quite possible that OO Calc offers this option directly from the interface, being developed by a much more international team that any Microsoft product. I'll look into it tonight.
Cheers!
(°v°)
As for OO Calc, I'm not sure. There is some support for VBA in the Windows version, but probably not on the Macintosh (at least I didn't find any references). OO supports many other scripting languages, so I'm quite confident that something similar can be done, but I'm not sure I can help you with that for lack of experience. On the other hand, it's quite possible that OO Calc offers this option directly from the interface, being developed by a much more international team that any Microsoft product. I'll look into it tonight.
Cheers!
(°v°)
ASKER
You've been a huge help. Thank you!
Obviously, if a comma is being used as a decimal separator, it cannot also be used as a field separator without causing ambiguity, and that is why Excel has to use a different delimiter.
So if your user changes her number format to use a period for the decimal place, rather than a comma, this should solve the problem.
The change can be made in Apple>System Preferences> Language and Text>Formats.
Click the Customize button beside "Numbers".