logo

MySQL Export non English CSV and import in MS Excel

utf-8 import into ms excel

Today I will share something I learnt handy, I hope it may help you also. I was having Arabic and other non-English language strings in a table, there was requirement to export that data to excel. I hope you know how can we export data using PhpMyAdminin CSV format, lets see

How to export data in CSV format from MySQL using PhpMyAdmin

  • Open PhpMyAdmin
  • browse table data, put where conditions if required, sort if required etc.
  • when you are ready to export, click export link which is shown below
If you don’t know what is PhpMyAdmin it comes as WAMP or XAMP package, shortly this is a GUI interface to interact with MySQL databases.

mysql export

 

Then select target csv format as shown in snap below

export type

click go button, it will download queried data in a CSV file.

Now coming towards MS Excel

How to import CSV in MS Excel

  • open Ms Excel
  • go to data tab
  • click from file in get external data section, as shown below

just click next, and select proper delimiter like comma or semicolon

it will import data BUT non-English characters will be broken as shown below

broken utf-8 in excel

Now I will show  you how we can avoid this while importing utf-8 or non-English data into MS Excel. 

during importing you see an option to select file origin

Select Unicode (UTF-8) as shown below

unicode utf-8 file origin

Now after completing importing your data will be ok, means non-English characters will not be broken. as shown below see Arabic, Urdu, Pashto and Hindi all languages are correctly displayed this time.

excel import utf-8 correct

The only trick was to select proper File Origin which in this case was Unicode (UTF-8), may be for  some other languages and file type you have to select different File Origin, but this is where you can play.

Hope this was easy, share with your friends.

 

Comments

    Write a Reply or Comment

    Your email address will not be published. Required fields are marked *