Importare CSV in MySQL

Per importare un file CSV in MySQL si usa il comando LOAD DATA:

LOAD DATA INFILE '/path/to/file.csv'
    INTO TABLE dbname.table
    FIELDS TERMINATED BY '|'
    ENCLOSED BY '"'
    LINES TERMINATED BY 'STRING\n'
    IGNORE 1 LINES;

in cui bisogna notare alcune righe opzionali, come

  • FIELDS TERMINATED BY: per specificare il separatore di campo
  • LINES TERMINATED BY: per specificare il terminatore di riga
  • IGNORE nnn LINES: per specificare quante righe del file CSV ignorare all’ inizio del file (utile se il file contiene anche le intestazioni)

Se l’ importazione riporta problemi o warnings, probabilmente il CSV ha una formattazione dei dati che MySQL non gradisce: mi è capitato che i campi DATETIME fossero ‘DD/MM/YYYY HH:MM:SS’ invece che ‘YYYY-MM-DD HH:MM:SS’, o che campi numerici fossero stringhe vuote.

Per risolvere simili problemi ci sono i comandi awk e sed.

Per correggere le date:

sed 's/\([0-9]\{2\}\)\/\([0-9]\{2\}\)\/\([0-9]\{4\}\)/\3-\2-\1/g' file.csv

I range dei gruppi di numeri sono racchiusi da \{ e \}.

Per correggere i campi vuoti

Con un if cerco il campo vuoto (notare l’ escape delle virgolette) e lo riscrivo come “\N”, quindi stampo l’ intera riga.
Con -v passo un parametro ad awk; se ne possono passare più di uno, mettendo altri -v.
OFS specifica il separatore di campo per l’ output.

awk -F'|' -v OFS='|' '{if($8=="\"\"") $8="\"\\N\""; print}' file.csv

Devo trovare un modo per eseguire questa operazione su più campi contemporaneamente, specificando il numero della colonna.

Campi vuoti gestiti in MySQL

Invece che awk si può usare lo stesso comando LOAD DATA per impostare una condizione di test e modificare il valore di un campo.
Però bisogna indicare a MySQL quali campi assegnare alle variabili e quali no, cioè bisogna indicare l’ elenco di tutte le colonne della tabella, cosa che può essere scomoda se ce ne sono molte: conviene allora tornare a sed e awk.

LOAD DATA INFILE '/path/to/file.csv'
    INTO TABLE dbname.table
    FIELDS TERMINATED BY '|'
    ENCLOSED BY '"'
    IGNORE 1 LINES
    (id, nome, @colx, indirizzo, @coly, smtg, @colz, ... )
    SET
    colx = (CASE WHEN @colx='' THEN null ELSE @colx END),
    coly = (CASE WHEN @coly='' THEN null ELSE @coly END),
    colz = (CASE WHEN @colz='' THEN null ELSE @colz END);

CAMPI MULTILINEA

Se i campi contengono del testo multilinea, sia awk che MySQL non possono interpretare correttamente il recordset a cui appartengono.
Bisogna modificare il file csv sostituendo il vero finelinea con una stringa da usare come nuovo finelinea.
Ma si deve fare attenzione: non è facile individuare i finelinea da sostituire.
Se per esempio i campi sono racchiusi da virgolette “”, si potrebbe usare una regex:

sed 's/"$/"STRINGA/' file.csv
# oppure: se il campo multilinea è in formato DOS
sed 's/\([^\r]\)$/\1STRINGA/' file.csv

Dopodichè, si indica ad awk o al comando LOAD DATA di MySQL quale è il nuovo finelinea:

awk -F'|' -v OFS='|' -v RS='STRINGA' -v ORS='STRINGA' '{... fa qualcosa ...}'

Notare che in sed il finelinea si indica con $.
Notare anche che in sed sostituiamo $ con STRINGA, ma poi in awk usiamo RS=’STRINGA\n’ !!

RIMUOVERE ULTIMA RIGA VUOTA

Se il csv contiene una riga vuota, questa viene sostituita con il finelinea STRINGA:
importandola in MySQL genera errori.
Per rimuoverla è semplicissimo:

sed '/^STRINGA$/d' file.csv

sed cancella ogni riga contenente la regexp indicata.

Script

#! /bin/bash

USAGE="Istruzioni: $0 [par1 par2 par3 ... parN]"
if [ "$#" == "0" ]; then
        echo "$USAGE"
        exit 1
fi

COMANDO="sed 's/\([0-9]*\)\/\([0-9]*\)\/\([0-9]*\)/\3-\2-\1/g' "$1" | "
COMANDO+="sed 's/"$/"FINELINEA/' | "

shift # avanziamo nella array degli argomenti
if [ $# -ge 1 ]; then
        COMANDO+="awk -F'|' -v OFS='|' -v RS='FINELINEA' -v ORS='FINELINEA' '{"
        while (("$#" )); do
                FIELD="$"$1
                COMANDO+='if('$FIELD'=="\"\"") '$FIELD'="\"\\N\""; '
                shift
        done
        COMANDO+="print}' | "
fi
# elimina eventuali righe vuote
COMANDO+="sed '/^FINELINEA$/d'"

# lancia il comando contenuto nella stringa
eval $COMANDO

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...