Uploading CSV files into database

Question

I have various input files with fixed number of rows which always begin with the following 7 lines of information:

"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…

Note: There are two different delimiters used comma(,) and semi-colon(;).

I want to import the above data into a SQL database table. I know the process of importing data from the source file into a database but my problem is how do I append the new information in the header rows of every relevant table?

The final output should look like below:

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

Please suggest how can I perform this.

Answer 1

In order add the header data into all the files, store them as global variables.

Try following the below steps:

1. Add tFileInputDelimited(x2), tJavaRow, tMap and tLogRow components in the workspace and connect them as shown below:

• In the tfileInputDelimited_1 component set the schema(key,value), separator (",") and limit 7.

• In the tJavaRow component write : globalMap.put(input_row.key, input_row.value);

• In the tFileInputDelimited_3 component set the schema (TXN_tijd, TXN_Nr, Kostenplaats_naam, … ), separator (";") and header 8

• Double click on tMap component to open the tMap editor. Then map the input tables with the output as shown below:

Hope this works for you.