Load multiples files with different headers

Question

I've succeed to load data from multiple files using this script:

SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

Data:
LOAD *, SubField(FileName(), '-', 1) as Regul, FileName() as fileName FROM
[P:\Some\Path\*.csv]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

It works pretty well.

Now I'd like to load file that have different header names, let's say that file A got the following:

HeadA | HeadB | HeadD
0     | 33    | 72

And File B:

HeadB | HeadC | HeadD
60    | 40    | 30

And I'd love QlikView to interpret it as:

Headers: HeadA | HeadB | HeadC | HeadD
FileA:   0     | 33    | null  | 72
FileB:   null  | 60    | 40    | 30

Is there a way to do so (The current script hanged for 12h with just 60ko of data...)? Or do I have to manually merge my headers?

score 1 · Accepted Answer · answered Jan 27 '16 at 11:16

The concatenate load ... function will do what you want.

The trick is to make a dummy table with a column you're not going to use first (this is so that it will have nulls in any columns you might use) and then let *.xls portion run.Ifthe extra line bothers you you could run through the table again after it is finished and apply a where isnull(NotTheHeader) clause

 Data:
 load 1 as NotTheHeader AutoGenerate(1);

 Concatenate 
 Load *, SubField(FileName(), '-', 1) as Regul, FileName() as fileName from
 [P:\Some\Path\*.csv]
 (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Load multiples files with different headers

1 Answers1