Ultimo aggiornamento: 14-Dic-2019

30 Settembre 2017MarkOne

Importare file di testo in DB2

Un’attività molto comune per chi lavora con DB2 for i è trovarsi nella necessità di importare informazioni da fonti dati esterne e immagazzinarle in tabelle del DB2.

Esistono vari metodi e vari contesti in cui si rende necessario acquisire dati da fonti esterne al DB2. In questo articolo focalizziamo l’attenzione sull’importazione di dati partendo da file di testo di tipo CSV.

Un file CSV (Comma-Separated Value) è un file di testo in cui ogni riga rappresenta un record e all’interno di ogni riga i “campi” sono separati tramite un carattere speciale.

Il documento di riferimento che descrive il formato CSV è la RFC 4180 che recita così nella parte introduttiva:

The comma separated values format (CSV) has been used for exchanging and converting data between various spreadsheet programs for quite some time. Surprisingly, while this format is very common, it has never been formally documented […] This RFC documents the format of comma separated values (CSV) files and formally registers the “text/csv” MIME type for CSV in accordance with RFC 2048.

Carattere separatore

Il carattere con cui vengono delimitati i campi può essere ovviamente la virgola “,”, in accordo con la denominazione stessa del formato, ma possono essere utilizzati altri caratteri. I più comuni sono: tabulazione, punto e virgola “;”, pipe “|”, spazio ” “.

La scelta del carattere separatore di campo è una scelta che si subisce, nel caso in cui si riceva il file da una fonte esterna che non può essere controllata da noi e quindi ci si deve adeguare.

Nel caso in cui si abbia la possibilità di scegliere il carattere separatore personalmente prediligo o il pipe o la tabulazione; ovvero caratteri che molto raramente potranno essere presente nel contenuto dei campi.

Principali regole per un file csv “well-formed”

Come evidenzia la RFC 4180 NON esiste uno standard riconosciuto per il formato CSV, quindi esiste una varietà di interpretazioni. In ogni caso la RFC 4180 tenta di definire alcune regole per scrivere “bene” un file csv. Sintetizzo le regole principali:

  1. ogni riga rappresenta un record e ogni riga deve terminare con i caratteri CRLF
  2. opzionalmente la prima riga del file può contenere le intestazioni di colonna
  3. in ogni riga possono essere presente uno o più campi. In ogni caso tutte le righe devono contenere lo stesso numero di campi
  4. opzionalmente ogni campo può essere racchiuso tra doppi apici “. (N.B. Microsoft Excel non usa i doppi apici)
  5. se i campi vengono racchiusi tra doppi apici e un campo contiene un doppio apice, questo deve essere raddoppiato

Non viene descritto nella RFC 4180, però nella prima riga di un file csv si può specificare il parametro

sep=<delimiter>

per indicare quale è il carattere separatore dei campi.

Per quanto non ho mai visto un file csv che contenga tale parametro.

Importazione di un file csv in DB2 for i

Per importare un file CSV in una tabella del DB2 bisogna innanzitutto copiarlo in una cartella nel file system “root” e poi utilizzare il comando CPYFRMIMPF.

Il comando CpyFrmImpF copia un “import file” (nel nostro caso il file CSV) in un file fisico/logico.

In linea generale si definisce “import file” un file creato allo scopo di copiare dati tra sistemi eterogenei.

Analizziamo i parametri più importanti di questo comando utili per importare un file csv in una tabella di DB2.

La tabella deve già esistere nel DB2 e i campi della tabella devono corrispondere con i campi presenti nel file CSV.

Il tipo dati dei campi della tabella del DB2 deve essere compatibile con il contenuto del file CSV.

Ovvero se per esempio il 3º campo della tabella è definito numerico lungo 5 di cui 2 decimali, il contenuto del 3º campo in tutte le righe del file CSV deve essere o nullo oppure contenere un numero valido con al massimo 3 interi e 2 decimali e deve esserci il carattere separatore dei decimali (o la virgola o il punto).

Per ogni errore che il DB2 riscontra cercando di importare il contenuto dei vari campi del file CSV viene registrato un messaggio nel joblog del lavoro e la riga interessata dall’errore viene scartata. Nel messaggio presente nel joblog viene nella maggior parte dei casi specificato esattamente su quale campo è stato riscontrato il problema.

N.B. L’importazione NON si interrompe alla prima riga che presenta un errore, ma prosegue a importare le righe successive.

Analizziamo in dettaglio i parametri più importanti del comando CPYFRMIMPF:

  • RCDDLM: specifica il carattere di fine riga utilizzato nel file CSV. Normalmente il carattere di fine riga è *CRLF (ovvero carriage return + line feed). Se si hanno dei dubbi su quale sia il carattere di fine riga si può visualizzare il file di testo in modalità esadecimale. Il codice esadecimale per il carattere CR è 0D, invece per il carattere LF è 0A.
  • DTAFMT: *DLM specifica che si sta importando un file di testo delimitato
  • STRDLM: se nel file csv i campi alfanumerici sono racchiusi tra doppi apici specificare *DBLQUOTE, altrimenti specificare *NONE.
  • STRESCCHR: specifica il carattere escape. Per default il carattere escape coincide con il delimitatore di stringa (parametro STRDLM). Impostare questo parametro a *STRDLM ottempera alla regola n. 5 descritta nel paragrafo precedente.
  • RMVBLANK: specifica se i caratteri blanks devono essere rimossi. Consiglio di utilizzare il valore *BOTH (= Gli spazi iniziali e di coda vengono eliminati)
  • FLDDLM: specifica il delimitatore di campo. Il valore di default è la virgola. Per specifica come delimitatore di campo la tabulazione usare il valore speciale *TAB. Non può essere lo spazio.
  • DECPNT: specifica il separatore dei decimali. Attenzione che il valore di default è *PERIOD (ovvero il punto). Per usare la virgola come separatore dei decimali indicare *COMMA.
  • RPLNULLVAL: se la tabella di destinazione del DB2 non consente i valori nulli e nel file CSV esistono dei campi nulli (ovvero esistono dei separatori di campo consecutivi senza neanche uno spazio tra di essi) bisogna impostare questo parametro a *FLDDFT per fare in modo che il valore nullo venga sostituito con il valore di default congruente con il tipo dati del campo del DB2
  • RMVCOLNAM: se la prima riga del file CSV contiene le intestazioni di colonna, bisogna specificare *YES affinché venga esclusa dall’importazione.

Utilizzo di un file per registrare gli errori

Il comando CpyFrmImpF consente di copiare in un file degli “errori” le righe del file CSV che non sono state importate. E’ una possibilità forse poco conosciuta, ma molto comoda per identificare più facilmente le righe che vengono scartate dall’importazione. Molto utile soprattutto quando il file csv contiene centinaia o migliaia di righe.

I parametri da usare per gestire il file degli errori sono:

  • ERRRCDFILE: specifica il file in cui verranno scritti i record errati. Consiglio di creare un file sorgente di lunghezza sufficiente a contenere un’intera riga del file CSV. P.es. se il file CSV può avere righe di lunghezza massima 500 caratteri, il file sorgente dovrà essere creato con lunghezza 500 o superiore:
CRTSRCPF FILE(QGPL/ERRORI) RCDLEN(550) MBR(*FILE) SIZE(*NOMAX)
  • ERRRCDOPT: consiglio di specificare *REPLACE così che ad ogni esecuzione del comando CPYFRMIMPF il file degli errori venga sostituito.

Informazioni aggiuntive

  • i campi del file di testo che sono più lunghi del corrispondente campo della tabella verranno troncati
  • due delimitatori di campo consecutivi oppure due delimitatori di stringa consecutivi oppure un delimitatore di campo seguito da un delimitatore di record significa che il campo corrispondente della tabella sarà nullo

Come visualizzare il carattere di fine riga

Se si hanno dubbi su quale sia il carattere di fine riga di un file csv lo si può aprire e controllare gli ultimi caratteri della riga visualizzandoli in modalità esadecimale.

Aprire il file csv memorizzato in una cartella su IFS con il comando DSPF oppure usando l’opzione 5=Visualizzazione dal comando WRKLNK.

Spostare la visualizzazione verso destra fino a mostrare la parte finale della riga: digitare nel posizionamento per colonna l’ultima colonna del file di testo. Quindi premere F10=Visualizzazione esadecimale

Importare file di testo a colonne fisse (fixed data)

Nel caso in cui si debba importare nel DB2 file di testo a colonne fisse, può tornare comunque utile utilizzare il comando CPYFRMIMPF.

In aggiunta a quanto scritto finora è necessario fornire un file di definizione del file da importare ovvero un file FDF (field definition file).

Ogni riga del file FDF contiene le seguenti informazioni:

  • nome del campo (corrispondente al nome campo di sistema del file fisico scritto in maiuscolo)
  • posizione iniziale nel file di testo
  • posizione finale nel file di testo
  • posizione dell’indicatore di valore nullo nel file di testo (se il file di testo non prevede l’indicatore del valore nullo specificare 0)

I 4 valori devono essere separati da uno spazio.

Altre informazioni utili relative al file FDF:

  • le righe vuote sono ignorate
  • le righe commento sono precedute da doppio trattino —
  • l’ultima riga può essere *END, le righe successive verranno ignorate (ovvero saranno considerate come commenti)
  • non è necessario specificare nel file FDF tutti i campi del file fisico. I campi non definiti nel file FDF verranno valorizzati con il valore di default
  • l’ordine in cui sono scritti i campi nel file FDF non è importante
  • invece del nome campo si può specificare la keyword *COL. In questo caso però il file FDF deve contenere i campi in ordine esatto.
  • il file FDF deve essere scritto o in un file sorgente oppure in un file fisico con un solo campo

L’indicatore di valore nullo nel file di testo deve avere i valori Y o N.

Esempio file FDF:

-- codice articolo
CODART 1 15 0
-- descrizione articolo
DESART 16 50 0
-- prezzo di listino
PREZZO 51 13 0
*END
queste righe dopo *END saranno ignorate

Bibliografia