6

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.

5 Answers 5

1

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.

1
  • Welcome to Super User! This duplicates another answer and adds no new content. Please don't post an answer unless you actually have something new to contribute.
    – DavidPostill
    Jun 14, 2016 at 15:11
0

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.

0

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.
2
  • The question was how to export it from Office 2016, so your answer of using Numbers wouldn't have helped too much, given that I don't have it. The SO link you commented looks interesting though so I'll give that a read.
    – Olly John
    Oct 19, 2015 at 16:07
  • 1
    SO removed the closed question for 'reasons of moderation'. Found it on Internet Archive here, though: web.archive.org/web/20180216070531/https://stackoverflow.com/…
    – JosephK
    Aug 19, 2020 at 11:25
-1

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.

3
  • Care to explain the downvote? Nov 8, 2015 at 20:34
  • I'd hazard a guess that it's because your answer not only comes after the question has already got an accepted answer but also it's about Excel 2011 when the question is about (mentioned & tagged) Excel 2016.
    – Olly John
    Nov 9, 2015 at 10:04
  • Thanks Olly. Excel 2016 VBA also lacks support for UTF8. As for the second point, though OP's needs may have been met, I leave my answer for posterity. Nov 9, 2015 at 23:35
-1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .