5

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.

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.
  • 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 '15 at 16:07
0

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.

  • 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 '16 at 15:11
0

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.

-2

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.

  • Care to explain the downvote? – Pax Per Scientiam Nov 8 '15 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 '15 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. – Pax Per Scientiam Nov 9 '15 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.