Può capitare di dover importare dei dati in un database di un’applicazione partendo da un file Excel. In questi casi, può essere una buona soluzione convertire il file di Excel in un file di testo (ad esempio un file CSV) ed importare i dati presenti nel file di testo in una o più tabelle del nostro DB. In questo caso prendiamo in esame un’applicazione ASP che si poggia su un database MySQL, con il file di dati trasformato da Excel in CSV, coi campi delimitati dal “;”. In MySQL esiste un comando, load data infile, che permette appunto di importare file di testo in una tabella a scelta, peccato che questa procedura non si adatti a tutte le situazioni, ad esempio, non funziona quando non si ha un accesso diretto al server Web, oppure se il file contiene dati che non vanno su un’unica tabella ma che, essendo un file di testo una tabella di un database “piatto”, non ha un minimo di normalizzazione, da ricreare in fase di importazione.
Una situazione tipica può essere quella di un file di testo contenente una lista di dipendenti e delle relative aziende di appartenenza, infatti esistendo più dipendenti per ogni azienda, le informazioni relative ad ogni singola azienda vengono ripetute diverse volte, e quindi questi dati non possono essere importati in una singola tabella, ma dovranno essere posti in due tabelle, aziende e dipendenti, in relazione uno a molti; in questo caso, non può essere usata l’istruzione load data infile, poiché, a quanto ne so io, questo comando non supporta l’inserimento in due tabelle distinte. Intanto vediamo da quali campi è composto il file CSV e come questi campi verranno distribuiti nelle due tabelle “Dipendenti” e “Aziende”:
File CSV
Cognome;Nome;Via;CAP;Localita;CodiceFiscale;NomeAzienda;
ViaAzienda;CAPAzienda;LocalitaAzienda;PartitaIVA
Tabella Dipendenti
idDipendente int not null auto_increment primary key
cognome varchar(30) not null
nome varchar(30) not null
via varchar(60)
CAP varchar(5)
localita varchar(60)
codiceFiscale varchar(16)
idAzienda int not null
Tabella Aziende
idAzienda int not null auto_increment primary key
nomeAzienda varchar(60) not null
viaAzienda varchar(60)
CAPAzienda varchar(5)
localitaAzienda varchar(60)
partitaIVA varchar(11)
A questo punto vediamo come impostare il codice vbscript della pagina ASP. Per prima cosa bisognerà aprire il file di testo che quindi dovrà essere posto in una cartella visibile del nostro server Web, utilizzando l’oggetto FileSystemObject per aprire il file e per scorrerlo; i primi dati a dover essere letti sono quelli relativi alle aziende, visto che poi dovrò associare i dipendenti all’azienda di appartenenza. Di seguito la prima parte del codice:
<% Dim percorso, fileFSO, apriFile, rigaFile, arrRiga ‘dichiarazione delle variabili Dim vNomeAzienda, vViaAzienda, vCAPAzienda, vLocalitaAzienda, vPartitaIVA Dim conn, importTempAziende, importAziende, truncateTempAziende, queryAzienda, rsAzienda, importIscritti Dim vidAzienda, vCognome, vNome, vVia, vCAP, vLocalita, vCodiceFiscale, vPartitaIVAAzienda set conn = server.CreateObject(“ADODB.Connection”) ‘creazione dell’oggetto di connessione al database conn.ConnectionString = “DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=prova; UID=prova; PWD=prova; OPTION=3” ‘opzioni di connessione al DB conn.Open ‘apertura della connessione al DB percorso = Server.MapPath(“filecsv.csv”) ’si specifica il percorso relativo del file da aprire. Server.MapPath serve per convertire un percorso virtuale in un percorso fisico set fileFSO = server.CreateObject(“Scripting.FileSystemObject”) ‘crea una nuova istanza dell’oggetto FileSystemObject if fileFSO.FileExists(percorso) then ‘utilizzo – all’interno di una struttura condizionale if… then… else – il metodo FileExists per verificare se il file esiste set apriFile = fileFSO.openTextFile(percorso, 1) ‘utilizzo il metodo openTextFile per aprire il file csv in sola lettura (il numero 1 significa l’apertura del file in sola lettura) do until apriFile.AtEndOfStream ‘uso un ciclo per leggere riga per riga il file fino ad arrivare alla fine tramite la proprietà AtEndOfStream rigaFile = apriFile.ReadLine ‘uso il metodo ReadLine per leggere una riga del file di testo e trasformarla in stringa arrRiga = split(rigaFile, “;”) ‘tramite la funzione split trasformo la stringa ottenuta tramite il metodo ReadLine in un array vNomeAzienda = arrRiga(6) ‘assegno alla variabile vNomeAzienda il valore relativo al nome dell’azienda pescato nell’array creato precedentemente vNomeAzienda = replace(vNomeAzienda, “’”, “””) ’sostituzione dell’apice con un doppio apice per permettere l’inserimento nella tabella senza problemi vViaAzienda = arrRiga(7) vViaAzienda = replace(vViaAzienda, “’”, “””) vCAPAzienda = arrRiga(8) vLocalitaAzienda = arrRiga(9) vLocalitaAzienda = replace(vLocalitaAzienda, “’”, “””) vPartitaIVA = arrRiga(10) %>
Ora abbiamo tutti gli elementi che ci servono per inserire i dati relativi alle aziende in una tabella di transito, infatti non è possibile inserire direttamente i dati nella tabella Aziende, poiché i dati sarebbero duplicati e quindi il database di fatto inutile, quindi di seguito verrà fatto in modo di inserire questi dati nella tabella “transitoAziende”, e da qui si prenderanno i dati filtrati in modo da non avere record duplicati per inserirli nella tabella “Aziende”.
<% ImportTempAziende = “INSERT INTO tempAziende (nomeAzienda, viaAzienda, capAzienda, localitaAzienda, partitaIVA) ” & _ VALUES (’” & vNomeAzienda & “’, ‘” & vViaAzienda & “’, ‘” & vCAPAzienda & “’, ‘” & vLocalitaAzienda & “’, ‘” & vPartitaIVA & “’)” ‘istruzione di inserimento dei dati prelevati dall’array nella tabella tempAziende conn.Execute ImportTempAziende ‘esecuzione dell’istruzione SQL precedente loop ‘termine del ciclo end if ‘termine struttura condizionale importAziende = “INSERT INTO aziende (nomeAzienda, viaAzienda, capAzienda, localitaAzienda, partitaIVA) ” & _ SELECT nomeAzienda, viaAzienda, capAzienda, localitaAzienda, partitaIVA FROM tempAziende GROUP BY partitaIVA ‘istruzione per filtrare i dati dalla tabella tempAziende ed inserirli nella tabella aziende truncateTempAziende = “TRUNCATE TABLE tempAziende” ‘istruzione per cancellare i dati di transito presenti all’interno della tabella tempAziende conn.Execute importAziende ‘esecuzione dell’istruzione SQL definita nella variabile importAziende conn.Execute truncateTempAziende ‘esecuzione dell’istruzione SQL definita nella variabile truncateTempAziende set fileFSO = nothing ‘libero la memoria occupata dall’istanza fileFSO %>
Con le istruzioni precedenti la procedura inserisce, riga per riga, i dati relativi alle aziende presenti nel file CSV nella tabella tempAziende, quindi fa in modo di filtrare i dati dalla tabella tempAziende ed eliminare i record duplicati tramite l’istruzione GROUP BY (utilizzando DISTINCT, se i dati non sono “perfetti”, si rischia di non eliminare completamente i duplicati), quindi questi dati filtrati sono inseriti nella tabella aziende tramite un’unica istruzione SQL, “INSERT INTO SELECT”; infine, vengono cancellati i dati dalla tabella tempAziende essendo questi dati di natura temporanea. A questo punto bisogna inserire i dipendenti nella tabella “dipendenti”. In primo luogo, dovrò riaprire il file “filecsv.csv” tramite il solito ciclo, ricreare l’array per ogni riga del file csv, ed “estrarre” dall’array i dati relativi ai dipendenti, e la partita IVA dell’azienda corrispondente per poter associare il dipendente all’azienda; come si può vedere, la prima parte del codice è identica alla parte precedente:
<% set fileFSO = server.CreateObject(“Scripting.FileSystemObject”) ‘crea una nuova istanza dell’oggetto FileSystemObject if fileFSO.FileExists(percorso) then ‘utilizzo – all’interno di una struttura condizionale if… then… else – il metodo FileExists per verificare se il file esiste set apriFile = fileFSO.openTextFile(percorso, 1) ‘utilizzo il metodo openTextFile per aprire il file csv in sola lettura (il numero 1 significa l’apertura del file in sola lettura) do until apriFile.AtEndOfStream ‘uso un ciclo per leggere riga per riga il file fino ad arrivare alla fine tramite la proprietà AtEndOfStream rigaFile = apriFile.ReadLine ‘uso il metodo ReadLine per leggere una riga del file di testo e trasformarla in stringa arrRiga = split(rigaFile, “;”) ‘tramite la funzione split trasformo la stringa ottenuta tramite il metodo ReadLine in un array vCognome = arrRiga(0) ‘assegno alla variabile vCognome il valore relativo al nome dell’azienda pescato nell’array creato precedentemente vCognome = replace(vCognome, “’”, “””) ’sostituzione dell’apice con un doppio apice per permettere l’inserimento nella tabella senza problemi vNome = arrRiga(1) vNome = replace(vNome, “’”, “””) vVia = arrRiga(2) vVia = replace(vVia, “’”, “””) vCAP = arrRiga(3) vLocalita = arrRiga(4) vLocalita = replace(vLocalita, “’”, “””) vCodiceFiscale = arrRiga(5) vPartitaIVAAzienda = arrRiga(10) %>
Ottenuti i dati che ci servono, per ogni dipendente bisogna ricavare l’azienda di appartenenza, nel dettaglio significa fare una query sulla tabella Aziende cercando l’idAzienda corrispondente alla Partita IVA ricavata in precedenza. Se si fosse impostata la partita IVA come chiave primaria non sarebbe stato necessario dover fare questa query, ma in alcuni casi i dati che vengono forniti non sono poi così ben bonificati… Una volta ottenuto l’ID dell’azienda, è possibile inserire il dipendente nella tabella Dipendenti completo di Foreign Key, indispensabile per poter mettere in relazione la tabella Aziende con la tabella Dipendenti.
<% queryAzienda = “SELECT idAzienda FROM aziende WHERE PI = ‘” & vPartitaIVAAzienda & “’” ‘definizione della query per ottenere l’idAzienda in base alla Partita IVA set rsAzienda = server.CreateObject(“ADODB.Recordset”) ‘istanziazione recordset rsAzienda.Open queryAzienda, conn, 3, 3 ‘apertura recordset vidAzienda = rsAzienda(“idAzienda”) ‘assegnazione dell’ID dell’azienda alla relativa variabile importIscritti = “INSERT INTO iscritti (cognome, nome, via, CAP, localita, codiceFiscale, idAzienda) VALUES ” & _ (’” & vCognome & “’, ‘” & vNome & “’, ‘” & vVia & “’, ‘” & vCAP & “’, ‘” & vLocalita & “’, ‘” & vCodiceFiscale & “’, ” & vidAzienda & “)” ‘definizione dell’istruzione di inserimento dipendente conn.Execute importIscritti ‘esecuzione dell’inserimento dipendente loop ‘termine del ciclo end if ‘termine struttura condizionale set fileFSO = nothing ‘libero la memoria occupata dall’istanza fileFSO rsAzienda.close ‘chiusura del recordset set rsAzienda = nothing ‘libero memoria conn.close ‘chiusura connessione al database set conn = nothing ‘libero memoria %>
Ora sono inseriti anche i dipendenti con indicata anche la loro azienda d’appartenenza. Siccome non sono esattamente un esperto di questi argomenti, ho affrontato la questione a modo mio, quindi molto probabilmente esistono modi più brillanti di risolvere il problema, nel mio caso questa soluzione ha l’indubbio pregio di funzionare.