Talend: How to import this csv file in SQL?

Question

I receive CSV files from BP, regarding our fuel and tol transactions.

The file begins always with these 7 lines of information, which is not included in the details.

How can I import this data into 1 table in SQL using Talend?

I know how I can import data from a csv file into SQL, but how can I add the information in de header (Factuur number, Factuur Date, ...) to every row in de details.

"Factuur","12345678"
"Datum","27/02/2012"
"Totaal:","1"
"Totaal netto:","99,18"
"Totaal bruto:","107,13"
"Verzamel factuurnummer:","1234567890A"
"Valuta:","EUR"
"TXN tijd (dd/mm/jj)","TXN Nr","Kostenplaats naam","Truncated...
"11/02/2012 02:22";"00000222";"";"722222";"Truncated...
"12/02/2012 02:22";"00000223";"";"722222";"Truncated...
"13/02/2012 02:22";"00000224";"";"722222";"Truncated...

An imported row in SQL should look like:

Factuur  Datum      ...   TXT tijd        TXN Nr.   ...
12345678 27-2-2012        11-2-2012 02:22 00000222
12345678 27-2-2012        12-2-2012 02:22 00000223

Answer 1

We can store the data of the header as global variables then use them in Tmap as usual:

• TfileInputDelimited_1 : schema(key,value), separator "," and limit 7
• tJavaRow: globalMap.put(input_row.key, input_row.value);
• tFileInputDelimited_3 : schema(TXN_tijd, TXN_Nr, Kostenplaats_naam, ..), separator ";" and header 8
• Tmap:

And it works fine !