Passa ai contenuti principali

MySQL - Importare un file CSV da Console


Nella gestione dei database spesso importiamo (ed esportiamo) direttamente i dati in formato SQL all'interno dei nostri backup.

Ma non sempre ci è permesso, poiché in alcuni casi il file messo a disposizione è quello ponte in formato CSV.

Il programma PHPMyAdmin (come pubblicato in precedenza in uno degli articoli per il dEC System IV), può importare i dati (entro i limiti del 128MB che abbiamo impostato nel PHP.INI di versione), ma la sua trasposizione tra formato ponte a formato SQL può essere lungo e complesso.

Addirittura sui sistemi MySQL di Aruba.it con file di notevoli dimensioni (in termini di record più che di dati in se) si può arrivare anche al Gateway timeout senza aver caricato tutto il pacchetto dati.

Il caso specifico a cui mi riferisco è relativo all'archivio dati di PoWo (Plant of the World della Royal Botanic Garden Britannica) che condivide il file del suo archivio dati in formato ponte CSV e non in formato SQL standard.

Le motivazioni alla base possono essere molteplici, ma in generale un plain-text come CSV è fruibile su diversi dispositivi (MySQL e cloni, Access, SQLServer, ecc)


Come importare un pacchetto dati CSV in MySQL

Il persupposto base è quello di poter usare un server (tipo CentOS, AlmaLinux, Ubuntu, ecc) sul quale sono installati un programma FTP e, ovviamente, un DataBase MySQL o fork (MariaDB, PerconaDB, ecc)

Col programma FTP caricare nella cartella remota /var/lib/mysql/piante (dove piante è il nome della collezione dati, ma potrebbe chiamarsi anche pippo ecc) il file CSV da voler importare



Creare (anche tramite l'interfaccia PHPMyAdmin ) a tabella sulla quale si vogliono caricare i dati, ovviamente conforme al tipo di dati del pacchetto.

Nel caso di PoWo, il file da oltre 400MB wcvp_taxon.csv, la prima riga indica i nomi della colonne della tabella:

taxonid|family|genus|specificepithet|infraspecificepithet|scientfiicname|scientfiicnameauthorship|taxonrank|taxonomicstatus|acceptednameusageid|parentnameusageid|originalnameusageid|namepublishedin|nomenclaturalstatus|taxonremarks|scientificnameid|dynamicproperties|references

Ovviamente prima di inserire i dati si deve necessariamente costruire la tabella (a mo' di esempio):

CREATE TABLE `wcvp_taxon` (

  `taxonid` int(10) NOT NULL,

  `family` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

  `genus` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

  `specificepithet` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  

  `infraspecificepithet` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  

  `scientfiicname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  

  `scientfiicnameauthorship` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  

  `taxonrank` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  

  `taxonomicstatus` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  

  `acceptednameusageid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,   

  `parentnameusageid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,   

  `originalnameusageid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,   

  `namepublishedin` varchar(255) CHARACTER SET utf8 DEFAULT NULL,     

  `nomenclaturalstatus` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  

  `taxonremarks` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  

  `scientificnameid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,   

  `dynamicproperties` varchar(255) CHARACTER SET utf8 DEFAULT NULL,   

  `references` varchar(255) CHARACTER SET utf8 DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;



Cliccando su Esegui a destra (non si vede nell'immagine) si crea al tabella.

Accediamo alla console ed andiamo alla cartella /var/lib/mysql/piante tramite il com,ando cd:


cd  /var/lib/mysql/piante



A questo punto dobbiamo accedere tramite console al database:

mysql -u [username] -p [e premere Invio poi mettere password quando richiesta]

Ovviamente username è generalmente, su un server root, e la password è quella scelta in fase di registrazione (se non cambiata)




Cambiamo il database con il comando use:

use piante


e siamo nel DB piante.

Prima di accedere, coma mostrato dalla prima immagine di console, siamo nella cartella remota  /var/lib/mysql/piante

A questo punto dobbiamo scrivere lo script per importare i dati da csv:


LOAD DATA INFILE 'wcvp_taxon.csv'

INTO TABLE wcvp_taxon

CHARACTER SET UTF8

FIELDS TERMINATED BY '|'

ENCLOSED BY ''

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;


La riga 3 dice il tipo di formato carattere da importare, mentre l'ultima dice di saltare l'ultima riga

La riga 4 dice come terminano i campi

La riga 5 chiede come sono definiti i dati (generalmente tramite ", ma in questo caso no)

La riga 6 chiede come termina la riga: \n indica che la riga finisce quando va a capo.


Il tempo di importazione è estremamente rapido:




Circa 10 secondi per 1.431.677 righe o record




A questo punto è possibile esportarla come SQL standard e/o lavorarci sopra




Commenti

Post più popolari

Windows 7 - Installare ed usare una stampante sul Print Server GetNet 3 Port 2 USB e 1 LPT

Su una rete di calcolatori (che sia di uso casalingo, Small Office o Medium Office) la perifrica più ricercata è la stampante. In molti casi la soluzione più semplice è quella di collegarla ad un PC che fa da server e da qui condividerla con tutta la rete. Spesso, però, ciò non è fattibile.  Per ovviare al problema esistono delle "scatrolette" che consentono di collegare la periferica ad uno switch o router (a seconda della grandezza della rete) chiamate Print Server. Alcuni Print Server (come quello oggetto del nostro post) possono usare più stampanti. Nel caso specifico il modello della GetNet può usare 1 stampante su Porta Parallela (LPT) e 2 stampanti (non multifunzioni) su porta USB 1.1 GetNet Print Server 1 Parallel e 2 USB Il modello in questione ha in dotazione un CD che dovrebbe essere installato, ma non è compatibile con Windows a 64 bit (che sia XP, Vista, 7 o 8), e da problemi con Windows Vista, 7, 8 Nel caso specifico illustre...

Importare la posta da Outlook Express a GMail

English translated post Condividi su Quanti di noi hanno sul PC la propria vita, fatta di E-Mail di lavoro, svago, di amori persi e a volte ritrovati? Soprattutto hanno il terrore di perdere tutti questi messaggi nel momento in cui il Computer (che sia esso PC, Mac o Linux) vada a farsi benedire? Si possono effettuare della copie di Backup dei vari Outlook, Outlook Express, Eudora, Thunderbird,... (io personalmente l'ho fatto). Ma siamo sempre lì: li perdo? il nuovo programma importerà i dati? Paura, paura, paura! Da ormai 4 anni, da quando è uscito GMail (ma anche la concorrenza non è male, tipo Yahoo Mail, Hotmail...) che ha posto al primo posto lo spazio illimitato, ho preferito "girare" tutti i miei vecchi account di posta su questa nuova piattaforma, anche e soprattutto perché ho sempre la posta con me, ovunque sia leggibile con qualunque sistema (o quasi). Credo che non eista persona di questo mondo moderno e collegato che non abbia perso, almeno una volta, nella ...

Installare lo scanner della Ricoh Aficio MP 1600L

Condividi Non so a quanti di Voi lettori sia capitato di dover installare su Windows una fotocopiatrice multifunzione come la Ricoh Aficio MP 1600L . A differenza dei classici prodotti cash-'n'-carry come le stampanti Epson, HP, Canon e tutte le altre da supermercato, questo genere di prodotti tipicamente Office, non hanno un sistema "comodo" di installazione come per le suddette "macchine da bancone" Di seguito c'è la soluzione che ho trovato (anche grazie ad un aiuto) che mio ha consentito di risolvere lo stesso problema su più computer Il driver in Italiano (anche perché la versione lingua è importante) per Windows XP si scarica cliccando qui . Premetto che considero la stampante collegata ad un PC via USB (il contenuto di questo post è applicabile anche alla multifuzione collegata ad un print server come TP-Link PSU210) Una volta installata stampante con il Driver di stampa DDST ricordando di mettere nelle impostazioni il check Il marigine lungo è v...