CODEWITHSUNDEEP
phpmysqlexcelunicode
Jesper Grann Laursen
Jesper Grann Laursen

Reputation: 2357

Howto import xls/csv file with unicode charset into php/mysql?

I want to give the user the ability to import a csv file into my php/mysql system, but ran into some problems with encoding when the language is russian which excel only can store in UTF-16 tab-coded tab files.

Right now my database is in latin1, but I will change that to utf-8 as described in question "a-script-to-change-all-tables-and-fields-to-the-utf-8-bin-collation-in-mysql"

But how should I import the file? and store the strings?

Should I for example translate it to html_entitites?

I am using the fgetcsv command to get the data out of the csv file. My code looks something like this right now.


file_put_contents($tmpfile, str_replace("\t", ";", file_get_contents($tmpfile)));
$filehandle = fopen($tmpfile,'r');
while (($data = fgetcsv($filehandle, 1000, ";")) !== FALSE) {
  $values[] = array(
    'id' => $data[0], 
    'type' => $data[1], 
    'text' => $data[4], 
    'desc' => $data[5], 
    'pdf' => $data[7]);
}

As note, if I store the xls file as csv in excel, i special chars are replaced by '_', so the only way I can get the russian chars out of the file, is to store the file in excel as tabbed seperated file in UTF16 format.

Upvotes: 3

Views: 24516

Answers (5)

Moxet Jan
Moxet Jan

Reputation: 130

I tried lots of alternative but the most easiest and rapid solution is to use Navicat

http://www.navicat.com/

enter image description here

Upvotes: 0

zmonteca
zmonteca

Reputation: 2354

Okay, my solution was ALSO to export the file from excel to UTF16 unicode text. The only difference was that I grab my file using a tab delimiter:

fgetcsv($fp, '999999', "\t", '"')

Upvotes: 0

Jesper Grann Laursen
Jesper Grann Laursen

Reputation: 2357

Okay, the solution was to export the file from excel to UTF16 unicode text and add the ';' instaid of '\t' and convert from utf16 to utf8.

file_put_contents($tmpfile, str_replace("\t", ";",  iconv('UTF-16', 'UTF-8', file_get_contents($tmpfile))));

The table in mysql has to be changed from latin1 to utf8

ALTER TABLE  `translation` 
CHANGE  `text`  `text` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE  `desc`  `desc` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

And then the file could be imported as before.

When I want to export the data from the database to a excel file, the csv-version is not an option. It has to be done in excel's html mode. Where data is corrected by eg. urlencode() or htmlentities()

Here some example code.


<?php
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="export.xls"');
print ('<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<div id="Classeur1_16681" align=center x:publishsource="Excel">
<table x:str border=0 cellpadding=0 cellspacing=0 width=100% style="border-collapse: collapse">');
for($i = 0 ; $i < count($lines) ; $i++) {
    print ('<tr><td>');
  print implode("</td><td>",$lines[$i]);
    print ('</td></tr>');
}
?>
</div>
</body>
</html>

Upvotes: 4

soulmerge
soulmerge

Reputation: 75714

I would not import it using PHP. Instead consider creating a temporary table to store your data using READ DATA INFILE.

$file_handle = fopen($file_name, 'r');
$first_row = fgetcsv($file_handle, 0, ',', '"');
fclose($file_handle);
# Your usual error checking
if (!is_array($first_row)) {
    ...
}
$columns = 'column'.implode(' TEXT, column', array_keys($first_row)).' TEXT';
query("CREATE TABLE $table ($columns) Engine=MyISAM DEFAULT CHARSET=ucs2");
query("LOAD DATA LOCAL INFILE '$file_name' INTO TABLE $table ...

Then you can do whatever you want with the data in that table.

Upvotes: 0

toluju
toluju

Reputation: 4107

Alternatively you could make use of the MySQL load command. This command lets you specify delimiters, character set, etc. The one caveat is that the server loading the data must have direct visibility of the file, meaning that the file must reside on a filesystem visible and readable by the db server.

Upvotes: 0

Related Questions