PHP31 Agosto 2016by Daniele Carnovale5Da excel a mysql. Converti xlsx in csv e importa in mysql

Converti da excel a mysql

La seguente guida descrive i passi da seguire per caricare il contenuto di un file excel del formato di Office 2010 .xlsx su database mysql.

Ambiente:

Piattaforma linux server 14.04 php 5.5

(La versione di linux può anche essere diversa, ma quella php non inferiore)

Comandi shell

Se non presente installare unoconv

sudo apt-get install unoconv

Requisiti

dare i permessi di esecuzione sudo senza password al comando unoconv

aggiungi i seguenti righi al file /etc/sudoers

nano /etc/sudoers

Righi da aggiungere alla fine del file appena aperto con il comando nano (per gli amanti di VIM o CAT non cambia niente) # Give apache sudo privileges to run /usr/bin/unoconv script
www-data ALL=NOPASSWD: /usr/bin/unoconv

aggiungere permessi alla cartella per eseguire il file csv da mysql

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Righi da aggiungere alla fine del file appena aperto

/run/mysqld/mysqld.pid rw,
/run/mysqld/mysqld.sock w,
/var/www/** rwk,
/sys/devices/system/cpu/ r,

Aggiorniamo

sudo /etc/init.d/apparmor reload

__________________________________________

Nel caso di conversione di file grandi ricordare di cambiare i valori di default nel file php.ini

post_max_size=20M
upload_max_filesize=20M

alla fine sempre un bel restart

/etc/init.d/apache2 restart

__________________________________________

codice php da excel a mysql

dare i permessi ai file csv
$result = @move_uploaded_file($_FILES['xlsxfile']['tmp_name'], $filetmpimport); // upload it
// $file=$filetmpimport;

//require_once 'xlsx2csv.php';
$start=microtime(true);
// $command = "/usr/bin/ssconvert /var/www/html/xlsx2csv/Sampleformslist.xlsx /var/www/html/xlsx2csv/fileimportato3.csv";
// $command = "sh /var/www/html/xlsx2csv/mysqript.sh";

$command="sudo unoconv -f csv /var/www/html/xlsx2csv/fileimport.xlsx";

exec( $command , $output, $ret );
$fp=fopen("/var/www/html/xlsx2csv/fileimport.csv","r");

if($line=fgets($fp,65535)) {
$line= str_replace(' ','_',$line);

$line= str_replace(',',' varchar(250) DEFAULT NULL,',$line);

$line='CREATE TABLE tempImport.temporanea ('.$line.' varchar(250) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;';
echo $line . '</br>';
}
if($line){

$result = mysql_query('DROP TABLE IF EXISTS tempImport.temporanea;');

$result= mysql_query($line);
// die;

if ($result) {
echo "create successfully";
} else {
echo "Error create table: ";
}

}else {

echo 'Importazione fallita';
$end=microtime(true);
fclose($fp);
$delta1=($end - $start);
echo $delta1 . '</br>';
die;
}
$end=microtime(true);
fclose($fp);
$delta1=($end - $start);

// echo $delta1 . '</br>';

// $queryInsertdafileCsv="LOAD DATA INFILE '/var/www/html/xlsx2csv/fileimport.csv' INTO TABLE tempImport.temporanea FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;";

// echo $queryInsertdafileCsv . '</br>';

$result= mysql_query('LOAD DATA INFILE "/var/www/html/xlsx2csv/fileimport.csv" INTO TABLE tempImport.temporanea FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\"" LINES TERMINATED BY "\n" IGNORE 1 LINES;');

if ($result) {
echo "Import successfully";

$warningDetailResult = mysql_query("SHOW WARNINGS");
if ($warningDetailResult ) {
while ($warning = mysql_fetch_assoc($warningDetailResult)) {

echo print_r($warning).'<br/>';

}
}

} else {
echo "Errore Importazione tabella: ".mysql_error();;
}
echo 'Importazione eseguita correttamente';

Considerazioni finali ed errori frequenti

Il codice di esempio citato, per importare un file da excel a mysql, funziona correttamente per file di formato .xlsx
Da notare che il file da importare deve contenere sul primo rigo il nome delle colonne della tabella che verrà creata sul database.
Gli errori possono derivare da caratteri speciali non previsti sul primo rigo o se il nome di qualche colonna è vuoto.