How to export a spreadsheet to a UTF-8 .csv from Excel 2016 on mac?

Question

Hoping you can help me out with this one...

For my work, I've been assisting a DBA for the last few weeks and we've now been given the task of uploading a very large spreadsheet which has several fields containing unicode characters (™, ®, °, so on and so forth) into a MariaDB table.

We initially tried just exporting it into a .csv file and putting it in the table but it seems to have been unable to use the special characters and it's actually cut off the rest of the data that was in the same cell(s) which obviously isn't ideal. When I opened the .csv file in a text editor (Sublime Text 3), it had all the special characters displayed as question marks in diamonds so we took that to mean that there was some kind of conflict between the formats.

If someone could advise me on how to export to a UTF-8 or UNICODE .csv file so we can preserve the characters, that'd be great.

Thanks in advance.

Answer 1

In the end, we just generated SQL insert statements as strings inside the spreadsheet and just pasted them across into a text editor and saved them with an .sql extension and ran it on the server.

I also found a site called sqlizer which takes spreadsheets, .csv's, .txt's, etc. and converts them into insert statements which was great.

This is the site.

Answer 2

Only way I found is open the .xls file in Numbers (fortunately it's free with recent OS), then export as CSV - Unicode UTF-8. Excel for Mac doesn't support UTF-8.

Answer 3

Google Sheets worked for us and was free. You will need to upload the Excel file, and then from inside Sheets you can export to CSV. What comes down from google is UTF-8.

Answer 4

If I can re-write your question as "How to export a spreadsheet to a UTF-8 .csv from an Excel 2016 file on mac?" then two answers are:

1. Use Apple Numbers - you can just open the excel file, then export to UTF-8. Numbers "does the right thing" and outputs all sheets to individual files (named like the sheets) in a directory of the name you specify.
2. Use a script, for example use xlrd in Python. More in this "closed but useful anyway" SO question on reading xlsx files.

Answer 5

The only solution I've been able to come up with for this unfortunate Excel 2011 shortcoming is to combine another netizen's "execShell()" UDF with a bit of shell scripting. The trick, I'd say, is to set the environment variable LC_ALL to en_US.UTF-8.

Here is an example. Hope that helps. I had been trying to find a solution for almost a year, before I made the environment variable revelation. Believe me, I was elated.