MySQL – How to Export Spreadsheet and Import as Table

UTF-8 is a fickle mistress. For a typical web application, you have to set UTF-8 at many levels. Even if the phpMyAdmin code works well with UTF8, it does not operate in a vacuum: the server configuration is important. So is the database, and so is the data source for the database. Let's summarize areas of potential friction in a general application, with a particular focus on areas of interest to phpMyAdmin.

A. Data Source

When data is imported into the database, such as from a text file, it must have the right encoding. The diacritics may be showing in Excel, but the encoding could be anything. So you can have a CSV file that looks right but that does not import properly. You can check and convert the encoding of your CSV file in a text editor such as EditPad or an IDE such as Komodo. In Notepad++, which many people have, you can select "Encoding" to see your current encoding, and you can select Encoding / Convert to UTF8 in needed.

B. In your database. The table's character set and collation have to be set to UTF-8. It sounds like you may have already done so.

If not, you need something like:

ALTER TABLE MyTable
DEFAULT CHARACTER SET utf8,
COLLATE utf8_general_ci;

That is the generic setting, but your Romanian collation should work fine too.

C. The connection to the database. You need something like:

$connectDSN = "mysql:host={$db_host};dbname={$db_name};charset=UTF-8";

PhpMyAdmin should connect correctly. On the landing page, you should see

Server charset: UTF-8 Unicode (utf8) 

If not, keep reading.

D. php has to process UTF8. In php.ini, you will want something like:

default_charset = UTF-8     
mbstring.language = Neutral 
mbstring.internal_encoding = UTF-8      
mbstring.encoding_translation = On      
mbstring.http_input = auto      
mbstring.http_output = UTF-8        
mbstring.detect_order = auto        
mbstring.substitute_character = “0xFFFD”    

You can check your server settings with phpinfo(). On a shared host, you usually don't have access to php.ini and have to change settings directly in the script. By the way, remember to use the mb functions.

E. Header setup

For the browser to know what it is getting, it needs to receive a header such as the following sent by php:

<?php  header('Content-type: text/html; charset=UTF-8');   ?>

This should not be an issue with phpMyAdmin. In Firefox, using the Web Developer extension, you can go Information / View Page Information. In the General tab, this will show you the encoding.

F. HTML Meta Tag

This is recommeded to help the browser.

<meta charset="UTF-8">

This should not be an issue with phpMyAdmin. Again check in the browser what encoding it is seeing.

G. Font

The browser may not have the font to display some characters, although modern versions are pretty good about fallback fonts. This is probably not a problem in your case.

So there are many places where things can go wrong.

Hope this helps you identify the problem!

When everything lines up, then magic... You get UTF8 everywhere.

And don't forget:

ಬಾ ಇಲ್ಲಿ ಸಂಭವಿಸು ಇಂದೆನ್ನ ಹೃದಯದಲಿ

ನಿತ್ಯವೂ ಅವತರಿಪ ಸತ್ಯಾವತಾರ

In notepad++ regular expression "[^\r]\n" will match LF, but not CRLF. Now replace this with whatever you want. This answer has useful information.