Ci sono diversi di modi per importare in un database dei dati provenienti da forme diverse come file excel, file di testo txt o file csv.
I file csv (Comma Separated Values) sono semplici file di testo e servono per la rappresentazione “portatile” di un database. Ogni riga è un record e i campi del record sono separati da delimitatori (commas), in genere punti e virgola. I file csv si possono ricavare facilmente da programmi come excel. Basta cliccare su “salva con nome” e impostare il tipo csv. Il programma creerà automaticamente il file di testo con i campi separati da un separatore.
I metodi che utilizzo personalmente sono:
1) Lettura e interpretazione dei file di testo (csv e txt) riga per riga
2) Utilizzazione del comando mysql LOAD DATA INFILE
3) Creazione di query di inserimento SQL con excel
La scelta di uno dei metodi dipende dalla situazione che si viene a creare.
1) Lettura e interpretazione dei file di testo (csv e txt) riga per riga
Prima di tutto vediamo il codice. Il tutto sarà composto da due parti, il form e la lettura e interpretazione del file. Supponiamo di voler inserire il contenuto di un file txt o csv in una tabella di un db. La tabella (chiamiamola semplicemente TABELLA) ha i campi ID, NOME, CITTA, DATA:
TABELLA [ ID , NOME , CITTA , DATA ]
ID= INT, chiave primaria, auto_increment
NOME= VARCHAR(255), NOT NULL
CITTA= VARCHAR(100)
DATA= INT (la data di inserimento, in automatico, del dato in formato unix, ).
Form (file form.php):
Codice PHP:
Upload file da inserire nel db</p>
Carica il file di testo (.csv o .txt) da inserire nel db
Campi (rispettare l'ordine): NOME, CITTA</p>
<form enctype="multipart/form-data" method="post" action="upload.php">
Carica il file di testo:
<input type="file" name="filenomi" enctype="multipart/form-data">
<input type="hidden" name="MAX_FILE_SIZE" value="100000">
</p>
[b]Scegli il separatore dei dati[/b]:
<input type="radio" name="separatore" value=","> , (virgola)
<input type="radio" name="separatore" value=";" checked> ; (punto e virgola)
</p>
<input type="submit" name="upload" value="REGISTRA"> </p>
</form>
<p align="center">[b]N.B.[/b]: se la lista è in excel, salva il file in formato csv rispettando l'ordine dei campi e usa il [i];[/i] come separatore.</p>
Upload e interpretazione (upload.php):
Codice PHP:
<?php
// se i dati in ingresso sono stati inseriti correttamente:
if($_POST[upload]=="REGISTRA" && isset($_FILES[filenomi][tmp_name]) && (substr($_FILES[filenomi][name],-4)==".txt" || substr($_FILES[filenomi][name],-4)==".csv")){
// CONNESSIONE AL DB
$link=mysql_connect("localhost","tuauser","tuapassword");
if(!$link) die("
Impossibile connettersi al database MYSQL
[b]Errore[/b]: ".mysql_error()."</p>");
mysql_select_db("TUODB") or die ("Impossibile aprire il db TUODB")
// COPIA FILE SUL SERVER
$dir="$_SERVER[DOCUMENT_ROOT]/tuadirectoryi"; // la directory nella quale verrà salvato il file
$nomefile=$_FILES[filenomi][name];
if(is_uploaded_file($_FILES[filenomi][tmp_name]))
move_uploaded_file($_FILES[filenomi][tmp_name],"$dir/$nomefile") or die("Impossibile spostare il file");
else die("Errore nell'upload del file.");
// COPIA DATI NEL DB
$file="$dir/$nomefile";
// apertura file
$fr=fopen($file,'r') or die("Impossibile aprire il file in lettura!");
$sep=$_POST[separatore]; // separatore dei dati
$riga=""; // azzeramento riga
?>
<table border="1" align="center" bordercolor="black" cellspacing="0">
<tr>
<td align="center">[b]NOME[/b]</td>
<td align="center">[b]CITTA'[/b]</td>
</tr>
<?php
while(!feof($fr)){ // lettura file fino alla fine
$riga=fgets($fr); // legge tutta la riga
if(strlen($riga)>1){
$riga=trim($riga); // elimino gli spazi all'inizio e alla fine
$riga=ereg_replace("'","\'",$riga); // cambio i ' in \'
$arrayriga=explode($sep,$riga); // metto i dati della riga in un array divisi col separatore
$num_elementi=count($arrayriga); // numero di elementi nell'array
// query di inserimento
if($num_elementi==2 && strlen($arrayriga[0])>0){
$data=time(); // data attuale in formato linux
$query1="INSERT INTO TABELLA(NOME,CITTA,DATA) VALUES('$arrayriga[0]','$arrayriga[1]',$data)";
mysql_query($query1,$link) or die("Impossibile eseguire la query [b]$query1[/b]
[b]Errore[/b]:".mysql_error());
print "<tr><td align=center>$arrayriga[0]</td><td align=center>$arrayriga[1]</td></tr>";
}
else {
?>
<tr><td>Errore: I dati nel file di testo non corrispondono. Riga: <?php print $riga; ?></td></tr>
<?php
}
}
$arrayriga=array_slice($arrayriga,0,0); // azzero per il prossimo inserimento
$riga=""; // azzero per il prossimo inserimento
}
?>
</table>
<p align="center">Dati inseriti.</p>
<?php
}
// se i dati in ingresso non sono stati inseriti correttamente:
else { // else 11
?>
<p align="center">Dati non corretti.</p>
<?php
} // end else 11
?>
Il primo file (form.php) penso non abbia bisogno di commenti. Vediamo in dettaglio il secondo file (upload.php).
Nella riga
if($_POST[upload]=="REGISTRA" && isset($_FILES[filenomi][tmp_name]) && (substr($_FILES[filenomi][name],-4)==".txt" || substr($_FILES[filenomi][name],-4)==".csv")){
si controlla che il file sia stato caricato e che l’estensione del file sia .txt o .csv.
Non ho utilizzato il controllo $_FILES[filenomi][type]==”text/plain” perché ho notato che spesso i file csv non sono riconosciuti come tali dal server.
Le successive righe sono la connessione al db e la copia del file importato sulle quali non mi soffermo.
Veniamo alla copia dei dati vera e propria.
$file è il file di testo che è stato caricato sul server; lo apro in lettura con fopen.
$sep è il separatore dei campi che è stato scelto nel file form.php.
$riga è la variabile che serve a memorizzare al suo interno ogni singola riga del file di testo.
La tabella serve per stampare a video i risultati dell’importazione (non è obbligatoria).
Con un while leggo il file di testo fino a quando è stata raggiunta la fine (feof).
Ad ogni iterazione del while, con fgets leggo la riga successiva e la inserisco nella variabile $riga.
A questo punto faccio un controllo ( if(strlen($riga1)>1) ) per non considerare eventuali “a capo” non voluti che si possono trovare all’inizio o alla fine del file di testo.
Se il controllo è positivo ho all’interno della variabile $riga i valori da inserire nella tabella del db separati da un separatore conosciuto.
Con trim pulisco la stringa da eventuali spazi bianchi, tabulazioni e avanzamenti di riga all’inizio e alla fine e con ereg_replace modifico il carattere apice (‘) per non creare problemi nell’esecuzione delle query.
Ora con la funzione explode trasformo la stringa in un array in base al separatore scelto.
Se tutto è andato a buon fine, dovrei avere un array ($arrayriga) formato da 2 elementi, NOME e CITTA in $arrayriga[0] c’è il NOME e in $arrayriga[1] c’è la CITTA.
Con la funzione count mi trovo il numero degli elementi dell’array.
Ora è possibile inserire i valori nella tabella del db.
Prima di eseguire la query viene fatto un ulteriore controllo: il numero degli elementi dell’array deve essere 2 e il NOME deve essere di almeno un carattere ( if($num_elementi==2 && strlen($arrayriga[0])>0) ).
Se il controllo viene superato viene eseguita la query e viene stampato il risultato a video (facoltativo).
Si possono fare altri controlli, dipende da quali sono i vincoli, ad esempio se un dato è numerico si può utilizzare al funzione is_numeric e così via.
Il mio, ovviamente, è solo un esempio. I casi reali dovranno essere adattati alle esegenze.
2) Utilizzazione del comando mysql LOAD DATA INFILE
Sul questo metodo mi soffermerò poco, in quanto basta leggersi il manuale MySql. LOAD DATA INFILE è utilizzato anche dal programma phpMyAdmin. Posso dire che si sono verificati diversi problemi di sicurezza riguardanti questa operazione e spesso è disabilitata.
3) Creazione di query di inserimento SQL con excel
Questo metodo è abbastanza semplice, anche se un po’ macchinoso e lo utilizzo solo “una tantum”. Supponiamo di avere una tabella T1 con 2 attributi ID e NOME, con ID intero chiave primaria e NOME varchar(50):
T1 [ ID , NOME ]
Supponiamo di avere un file excel con due colonne corrispondenti alla tabella del database, ovvero nella colonna A abbiamo l’attributo ID e nella colonna B l’attributo NOME.
Il procedimento è il seguente: supponiamo che il primo record sia posizionato nelle caselle A1,B1; nella casella C1 digitiamo il seguente codice che sfrutta la funzione CONCATENA di excel:
=CONCATENA("INSERT INTO T1(ID,NOME) VALUES(";A1;",'";B1;"');")
Per evitare di fare errori di digitazione si può utilizzare anche la funzione guidata CONCATENA di excel.
Se in A1 ci sarà il valore 356 e in B1 il valore ‘Pinco Pallino’, nella casella C1 apparirà: INSERT INTO T1(ID,NOME) VALUES(356,'Pinco Pallino');
Ora col comando “Trascina” di excel copia la funzione su tutte le riga.
A questo punto avrai tutti le righe con le query di inserimento sql e dovrai solo copiarle e incollarle su uno spazio apposito dove si potranno eseguire (ad esempio con phpMyAdmin). Attenzione però, se le copierai senza opportuni trucchi ti verranno copiate le formule. Per ovviare a questo problema, in excel copia la colonna C, vai sulla colonna D e clicca: incolla speciale -> valori. Ora puoi tranquillamente copiare e incollare i comandi sql. Più facile a farsi che a dirsi.
Se nelle stringe da inserire può essere presente il carattere apice (‘), è bene sostituirlo col barra apice (\’) per evitare problemi nell’esecuzione delle query. É sufficiente selezionare la colonna e usare la funzione excel SOSTITUISCI.