topic Re: Load multiples files with different headers in QlikView

Load multiples files with different headers

Tue, 26 Jan 2016 08:46:13 GMT

Hi,

First of all, I hope I posted this question at the right place.

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

(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?

Re: Load multiples files with different headers

maxgro — Tue, 26 Jan 2016 18:18:01 GMT

maybe

let concat = ' X: ';

for each file in 'A.txt', 'B.txt'

$(concat)

LOAD SubField(FileName(), '-', 1) as Regul, *

FROM [$(file)]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

let concat = ' concatenate (X) ';

NEXT;

Re: Load multiples files with different headers

tamilarasu — Wed, 27 Jan 2016 03:58:00 GMT

Hi Thomas,

You can try the below one as well,

Sub ScanFolder(Root)  
  
 FOR EACH FileExtension in 'csv'  
      FOR EACH FoundFile in FileList( Root & '\*.' & FileExtension)

 $(vConcatenate)
  LOAD *,
  FileBaseName() as FileName
  FROM 
  [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is '|', msq);; 
     
  SET vConcatenate = Concatenate (Data); 
  
     NEXT FoundFile  
  NEXT FileExtension  
  
END SUB  

SET vConcatenate= Data: ;  
CALL ScanFolder('C:\Users\Tamil.Nagaraj\Desktop\Test') ;

Re: Load multiples files with different headers

Wed, 27 Jan 2016 10:10:16 GMT

Thanks for you answer.

I've adapted your code to this:

Sub ScanFolder(Root)
FOR EACH FileExtension in 'csv'
FOR EACH FoundFile in FileList( Root & '\*.' & FileExtension)
Data:
LOAD *,
FileBaseName() as FileName
FROM
[$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is '|', msq);;
SET vConcatenate = Concatenate (Data);
NEXT FoundFile
NEXT FileExtension
END SUB
SET vConcatenate= Data: ;
CALL ScanFolder('C:\Path\') ;

Because when I put $(vConcatenate) before LOAD I got a lot of errors. When I try to execute the script it throw the following error for every file:

Field names must be unique within table
Sub ScanFolder(Root)  
  
 FOR EACH FileExtension in 'csv'  
      FOR EACH FoundFile in FileList( Root & '\*.' & FileExtension)

 $(vConcatenate)
  LOAD *,
  FileBaseName() as FileName
  FROM 
  [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is '|', msq);; 
     
  SET vConcatenate = Concatenate (Data); 
  
     NEXT FoundFile  
  NEXT FileExtension  
  
END SUB  

SET vConcatenate= Data: ;  
CALL ScanFolder('C:\Users\Tamil.Nagaraj\Desktop\Test') ;

How can I deal with it ?

Re: Load multiples files with different headers

tamilarasu — Wed, 27 Jan 2016 13:52:42 GMT

Hi Thomas,

Forgot to mention about the red underlines. Do not worry about the lines. The script engine shows those lines as error. But the script will work as expected. Just change the path alone and run the application. Let me know.

Re: Load multiples files with different headers

Wed, 27 Jan 2016 14:12:39 GMT

I've got a faster & working answer here. Thanks anyway