Se avete dati in un file di testo, potete facilmente caricarli in una o più tabelle di un database.
In MySQL database (o MariaDB), usando il comando “load data infile”, si possono caricare dati da un file di testo alle tabelle.
Il comando load data infile fornisce diverse opzioni flessibili per caricare vari formati di dati da file di testo alle tabelle.
I seguenti esempi di caricamento dati sono trattati in questo tutorial:
- Esempio base per caricare i dati da un file di testo
- Carica i dati usando l’opzione “Campi terminati da”
- Carica i dati usando l’opzione “Chiuso da”
- Usa il carattere di escape nei dati del file di testo
- Carica i dati usando l’opzione “Linee terminate da”
- Ignora il prefisso di linea nel file di caricamento usando “A partire da” Opzione
- Ignorare la linea di intestazione dal file di caricamento
- Caricare solo colonne specifiche (e ignorare le altre) dal file di caricamento
- Utilizzare le variabili durante il caricamento con l’opzione “Set”
- Scrivere uno script Shell per caricare i dati dal file di testo
Esempio base per caricare i dati dal file di testo
Nel seguente esempio, il file employee1.txt ha i valori dei campi separati da tab.
# cat employee1.txt 100 Thomas Sales 5000200 Jason Technology 5500300 Mayla Technology 7000400 Nisha Marketing 9500500 Randy Technology 6000
Di default, il comando load data infile usa TAB come delimitatore di campo predefinito.
Primo, vai al database dove vuoi caricare il file di testo. In questo esempio, caricheremo il file employee1.txt nella tabella employee situata sotto il database mysql thegeekstuff.
USE thegeekstuff;
Il seguente comando MySQL caricherà i record dal file employee1.txt nella tabella employee come mostrato di seguito. Questo comando non usa nessuna opzione extra.
LOAD DATA INFILE 'employee1.txt' INTO TABLE employee;
Nota: Nell’esempio precedente, il comando presuppone che il file employee1.txt si trovi nella directory del database. Per esempio, se state eseguendo il comando di cui sopra nel databasegeekstuff, allora mettete il file sotto: /var/lib/mysql/thegeekstuff/
Il seguente sarà l’output del comando di cui sopra.
Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
In quanto sopra:
- La prima linea “Query OK” dice che la query è stata eseguita senza alcun errore. Dice anche che un totale di 5 righe sono state caricate nella tabella. Questa mostra anche il tempo impiegato per caricare i dati dal file di testo alla tabella in secondi.
- La seconda linea mostra il totale delle righe caricate, quante sono state saltate e quanti record hanno mostrato avvertimenti durante il caricamento.
Dopo che i dati sono stati caricati, questo è ciò che vedremo nella tabella dei dipendenti.
Nota: Se vuoi fare il backup e ripristinare l’intero database MySQL, usa il comando mysqldump.
Carica i dati usando l’opzione “Fields terminated by”
Nel seguente esempio, nel file di input employee2.txt, i valori dei campi sono separati da virgole.
Per caricare i suddetti record nella tabella employee, usa il seguente comando.
Durante il caricamento, usando l’opzione “FIELDS TERMINATED BY”, puoi specificare il delimitatore di campo con la virgola come mostrato qui sotto.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',';
Anche in questo caso, usa questa opzione solo quando i valori dei campi sono separati da qualcosa di diverso da TAB. Se i campi sono terminati da due punti, userai la seguente opzione nel comando di cui sopra:
FIELDS TERMINATED BY ':';
Se sei nuovo di MySQL leggi questo: MySQL Tutorial: Installare, Creare DB e Tabelle, Inserire e Selezionare Record
Questi sono alcuni errori di base che potrebbero accadere durante il caricamento di MySQL
Errore 1: Se il file di testo non si trova nella directory corretta, potresti ottenere il seguente messaggio di errore “ERROR 13 (HY000) Can’t get stat of (Errcode: 2)”.
Inoltre, puoi specificare il percorso completo del file nel comando load data infile come mostrato sotto. Se lo fate, assicuratevi che il file sia accessibile a mysql. In caso contrario, cambiate opportunamente la proprietà a mysql. Altrimenti, otterrete il messaggio di errore load data infile permission denied.
MariaDB > LOAD DATA INFILE '/data/employee2.txt' INTO TABLE employee;
Errore 2: se non specificate i campi corretti terminati da, allora vedrete alcuni problemi nel caricamento. In questo esempio, solo il primo campo “id” è stato caricato. Il valore di tutti gli altri campi è NULL. Questo perché il seguente comando non specifica l’opzione campo terminato da, dato che il file di input ha la virgola come delimitatore di campo.
Carica i dati usando l’opzione “Enclosed by”
Nel seguente esempio, il file di testo di input ha i valori dei campi di testo racchiusi da virgolette doppie, cioè i valori di nome e dipartimento hanno i doppi apici intorno.
In questo caso, usa l’opzione “enclosed by” come mostrato sotto.
LOAD DATA INFILE 'employee3.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Il comando di cui sopra caricherà correttamente i record come mostrato di seguito dal comando mysql select:
Si prega di notare che quando si combinano campi terminati da e campi racchiusi da, non si deve usare la parola chiave “FIELDS” due volte come mostrato di seguito, che mostrerà il seguente messaggio di errore:
FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"';
Questo mostrerà il seguente errore “ERROR 1064 (42000)”:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS ENCLOSED BY '"'' at line 4
Usare il carattere di escape nei dati del file di testo
Diciamo che avete una virgola nel valore di un campo particolare.
Per esempio, nel seguente esempio, il nome del 2° campo ha il valore in questo formato: “nome, cognome”.
Se carichi il file di cui sopra usando il seguente comando, vedrai che visualizzerà “10 avvisi”
I record non vengono caricati correttamente anche perché c’è una virgola nel valore di uno dei campi.
File corretto: Per risolvere il problema di cui sopra, usa back-slash (\) davanti alla virgola nel valore del campo name come mostrato sotto.
Il seguente funzionerà questa volta senza alcun errore, poiché abbiamo \ come carattere di escape.
Puoi anche usare un carattere di escape diverso come mostrato sotto. In questo esempio, stiamo usando ^ come carattere di escape invece del predefinito \.
In questo caso, usa l’opzione “ESCAPED BY” come mostrato qui sotto.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\^'
Nota che alcuni caratteri non possono essere usati come carattere di escape. Per esempio, se usi % come carattere di escape, otterrai il seguente messaggio di errore.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\%'ERROR 1083 (42000): Field separator argument is not what is expected; check the manual
Carica i dati usando l’opzione “Lines terminated by”
Invece di avere tutti i record su una linea separata, puoi anche averli sulla stessa linea.
Nel seguente esempio, ogni record è separato dal simbolo |.
Per caricare il file di cui sopra, usa l’opzione lines terminated by come mostrato di seguito.
LOAD DATA INFILE 'employee5.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '|';
Il comando di cui sopra caricherà i record da employee5.txt come mostrato di seguito.
Questi sono un paio di punti da tenere a mente:
- Se il file di input viene da una macchina Windows, allora potresti voler usare questo: LINES TERMINATED BY ‘\r\n’
- Se stai usando un file CSV per caricare i dati nella tabella, allora prova uno di questi 1) LINES TERMINATED BY ‘\r’ 2) LINES TERMINATED BY ‘\r\n’
Ignore Line Prefix in Upload File Using “Starting By” Option
Puoi anche avere qualche prefisso ai record nel tuo file di testo di input che può essere ignorato durante il caricamento.
Per esempio, nel seguente file employee6.txt, per il 1°, 2° e 5° record, abbiamo “Data:” all’inizio della linea. Puoi caricare solo questi record ignorando il prefisso di riga.
Per ignorare il prefisso di riga e caricare questi record, (per esempio: “Data:” nel file sopra), usa l’opzione “lines starting by” come mostrato qui sotto.
LOAD DATA INFILE 'employee6.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES STARTING BY 'Data:';
Quello che segue è l’output del comando di cui sopra:
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
Come vedi sotto, il comando di cui sopra ha caricato solo i record che iniziavano con il prefisso “Data:”. Questo è utile per caricare selettivamente solo i record che hanno un certo prefisso.
Ignorare la riga di intestazione dal file di caricamento
Nel seguente file di testo di input, la prima riga è la riga di intestazione, che ha il nome delle colonne.
Durante il caricamento, vogliamo ignorare la prima riga di intestazione dal file employee7.txt. Per questo, usa l’opzione IGNORE 1 lines come mostrato sotto.
LOAD DATA INFILE 'employee7.txt' INTO TABLE employee FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Come vedi dal seguente output, anche se il file di input ha 6 linee, ha ignorato la prima linea (che è la linea di intestazione) e ha caricato le restanti 5 linee.
Carica solo una colonna specifica (e ignora le altre colonne) dal file di caricamento
Nel seguente esempio, abbiamo valori solo per tre campi. Non abbiamo la colonna dipartimento in questo file di esempio.
# cat employee8.txt 100,Thomas,5000200,Jason,5500300,Mayla,7000400,Nisha,9500500,Randy,6000
Per caricare i valori dal record di input in una colonna specifica della tabella, specificare i nomi delle colonne durante il caricamento dei dati infile come mostrato sotto. L’ultima riga del seguente comando ha i nomi delle colonne che dovrebbero essere usati per caricare i record dal file di testo di input.
LOAD DATA INFILE 'employee8.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, salary);
Siccome non abbiamo specificato la colonna “dept” nel comando precedente, vedremo che questa colonna è NULL come mostrato sotto.
Ancora una volta, tenete presente che quando non specificate l’elenco delle colonne, il comando si aspetta che tutte le colonne siano presenti nel file di input.
Inoltre, se non specifichi l’elenco delle colonne nell’ultima riga, otterrai un errore di sintassi come mostrato di seguito.
Usa le variabili durante il caricamento con l’opzione “Set”
Per questo esempio, usiamo il seguente file employee2.txt.
In questo esempio, vogliamo aumentare lo stipendio di 500 prima di caricarlo nella tabella. Per esempio, lo stipendio di Thomas (che è il 1° record) è 5000. Ma, durante il caricamento vogliamo incrementarlo di 500 a 5500 e aggiornare questo valore incrementato nella tabella.
Per questo, usate il comando SET e usate lo stipendio come variabile e fate l’incremento come mostrato qui sotto.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, dept, @salary) SET salary = @salary+500;
Come vedete dal seguente output, la colonna stipendio è stata incrementata di 500 per tutti i record durante il caricamento dei dati dal file di testo.
Scrivere uno script Shell per caricare i dati da un file di testo
A volte si potrebbe voler caricare i dati da un file di testo automaticamente senza dover accedere al prompt mysql ogni volta.
Diciamo che vogliamo mettere il seguente comando in uno script di shell ed eseguirlo automaticamente sul database di thegeekstuff.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','
Per eseguire il caricamento dalla linea di comando, si usa l’opzione -e nel comando mysql e lo si esegue dal prompt di linux come mostrato qui sotto.
# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','" \ -u root -pMySQLPassword thegeekstuff
Oppure, si può mettere questo all’interno di uno script di shell come mostrato qui sotto. In questo esempio, lo script di shell load-data.sh ha il comando mysql di cui sopra.
Dare il permesso di esecuzione a questo script load-data.sh, ed eseguirlo dalla riga di comando, che caricherà i dati automaticamente nella tabella. Potete anche programmare questo come cronjob per caricare automaticamente i dati dal file alla tabella ad un intervallo programmato.
# chmod u+x load-data.sh# ./load-data.sh
Se ti è piaciuto questo articolo, potrebbe piacerti anche..
|
|
 |
 |
 |
 |