Ironpaper Webintel:
By Chris Wallace
Are you having trouble importing text from an excel file in Japanese, Chinese, Arabic, or another language that uses non-standard characters?
When you save a csv directly from the Excel file, it turns the characters into question marks. But there is an extremely simple solution we recently came across that we wanted to share. All you need is a google account. We also assume here that you're using phpMyAdmin. Here are the steps:
1. Open up a new spreadsheet in google drive.
2. Choose File…Import.
3. Select the .xls file you containing the Chinese, Japanese, Arabic, etc. characters that you need to import to the database.
4. Select "Replace Spreadsheet" and click "import".
5. Once the spreadsheet is uploaded, select File…Download As…Comma separated values (.csv, current sheet).
6. Open the csv with a text editor to confirm that the characters came out correctly.
7. Clean up the csv to prepare for the import - make sure the first row doesn't contain column names, and delete extra blank rows at the bottom, but leave one blank line at the end. Do a search for blank fields - ",," and replace with whatever data type that field should be, if it's numeric, make it 0 or -1, etc. Don't forget to also search for two quotation marks in a row - "" and replace them all with \". And if there are commas in any of your fields, make sure to surround that whole value with quotation marks.
8. Open phpMyAdmin, select your table, and click Import. Assuming the fields match those in the csv correctly, everything should work and you should now have your non-standard characters in the database!
