All geek questions in one place

How to import unicode xls / csv file into php / mysql?

I want to give the user the opportunity to import the csv file into my php / mysql system, but I ran into some encoding problems when the language is Russian, which can only be stored in tabs with tabs UTF-16.

Now my database is in latin1, but I will change it to utf-8, as described in the question "a-script-to-change-all-tables-and-fields-to-the-utf-8 -bin-comparison -to-MySQL "

But how do I import a file? and save the lines?

Should I, for example, translate it to html_entitites?

I use the command fgetcsvto get data from a csv file. My code looks something like this.


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]);
}

, xls csv excel, "_", - excel UTF16.

+3
5

, , excel UIF16 unicode ';' instaid '\ t' utf16 utf8.

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

mysql latin1 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

.

excel, csv . excel html. , . urlencode() htmlentities()

.


<?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>
+2

load load. , .. , , , , , , db.

0

PHP. 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 this table.

0
source

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

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

I tried many alternatives, but the easiest and fastest solution is to use Navicat

http://www.navicat.com/

enter image description here

0
source

Source: https://habr.com/ru/post/1708853/

More articles:


All Articles