Excel won't recognise .csv files properly

Cianos

Hey,
I have Excel 2008 on my Mac, and it won't open .csv files properly

If I save a spreadsheet as a .csv, it makes the separators semi-colons instead of commas. If I change the separators to commas in Text Editor and open it back up in Excel, it doesn't organise it in to cells but all stays in the A column again.

Any idea what I'm doing wrong or how to fix this?

Thanks very much, wrecking my head now at this stage!

Lu Tze

Cianos wrote: »

Hey,
I have Excel 2008 on my Mac, and it won't open .csv files properly

If I save a spreadsheet as a .csv, it makes the separators semi-colons instead of commas. If I change the separators to commas in Text Editor and open it back up in Excel, it doesn't organise it in to cells but all stays in the A column again.

Any idea what I'm doing wrong or how to fix this?

Thanks very much, wrecking my head now at this stage!

Are you setting it to comma delimited when it opens?

TouchingVirus

Under the File Format option on Excel's Save As... box there are several CSV options: 'Comma Separated Values', 'Windows Comma Separated Values' and 'MS-DOS Separated Values' - which are you picking?

I've picked the first one (which is under "Common Formats" and it exported a csv file separated with commas and not semi-colons.

Do not load a CSV file into Excel the same way you would do an XLS file (i.e. via the File->Open menu). This gives you no options about how to import the data. Instead open a new workbook, and click in the import button on the toolbar. Go through the steps, say it's a CSV file, choose the CSV file, choose the delimiter on the next page, and then keep going or choose finish and it will import correctly

bhickey

Cianos wrote: »

I have Excel 2008 on my Mac, and it won't open .csv files properly

If I save a spreadsheet as a .csv, it makes the separators semi-colons instead of commas. If I change the separators to commas in Text Editor and open it back up in Excel, it doesn't organise it in to cells but all stays in the A column again. !

Don't Macs add extra info about file types into the files themselves? So I think that once you've edited & saved it in a text editor, it's no longer a CSV file as far as Macs are concerned - no matter what the file extension might say.

Have you a Windows machine with Excel that you can test the same process on?

P.S. What's wrong with semi-colons? What did they ever do to you

Cool Mo D

There is a function in excel that will fix this.

Open the .csv with File->Open. Select all your data. Click 'Data' -> 'Text to Columns' on the menu bar. In the wizard that appears, select 'Delimited' then select what you want to split the columns, commas, semi-colons, spaces, etc.

Cianos

Thanks very much for the replies to this, but unfortunately I still can't get it working

Lu Tze wrote: »

Are you setting it to comma delimited when it opens?

Yes when I import the saved .csv file that I edited in Text Editor, making sure that all separators are commas, I set it to comma delimited. The data is then organised correctly in the cells. When I save it in Excel as a .csv, then reopen in Text Editor, the commas are back to semi colons for some reason.

TouchingVirus wrote: »

Under the File Format option on Excel's Save As... box there are several CSV options: 'Comma Separated Values', 'Windows Comma Separated Values' and 'MS-DOS Separated Values' - which are you picking?

I've picked the first one (which is under "Common Formats" and it exported a csv file separated with commas and not semi-colons

I'm picking the same one as you, the one under Common Formats.

Do not load a CSV file into Excel the same way you would do an XLS file (i.e. via the File->Open menu). This gives you no options about how to import the data. Instead open a new workbook, and click in the import button on the toolbar. Go through the steps, say it's a CSV file, choose the CSV file, choose the delimiter on the next page, and then keep going or choose finish and it will import correctly

I tried this and it imported correctly, but it's when I save it again it replaces the commas with semi colons

bhickey wrote: »

Don't Macs add extra info about file types into the files themselves? So I think that once you've edited & saved it in a text editor, it's no longer a CSV file as far as Macs are concerned - no matter what the file extension might say.

Have you a Windows machine with Excel that you can test the same process on?

P.S. What's wrong with semi-colons? What did they ever do to you

I'm going to try test it on a Windows machine but I need to get it working on the Mac as well!

Cool Mo D wrote: »

There is a function in excel that will fix this.

Open the .csv with File->Open. Select all your data. Click 'Data' -> 'Text to Columns' on the menu bar. In the wizard that appears, select 'Delimited' then select what you want to split the columns, commas, semi-colons, spaces, etc.

I tried this also but again it's when I save it back as a .csv it rejigs the separators.

Any help would be brilliant!

Cianos

I think I've found the reason this is happening. It's explained here.

On Mac OS X, I haven't been able to discover a way to specify a different list separator symbol. So I just opened the CSV file into TextEdit, chose Edit > Find > Find, and then searched for all semicolons and replaced them with commas. Of course you can also change your computer's region by going to Apple Menu > System Preferences, clicking on International, and then the Formats tab. Here you'll find the Region menu, where you can specify an English speaking region, which you'd probably want to avoid doing.

If anyone can suggest a better workaround rather than changing my regional settings I'd love to hear it!

ceadaoin.

I know that with Windows the regional settings can change the type of list separators used. For example if the region/language is set to French then it uses semi colons instead of commas. To fix this you go to control panel and change the regional and language options to English.

Not sure if it could be the same for a Mac but it might be worth looking into?

Edit: Was typing this as you posted your reply above!