Load multiples files with different headers

[#1]

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?

[maxgro]

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;

[tamilarasu]

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') ;  

[#4]

Thanks for you answer.

I've adapted your code to this:

1. Sub ScanFolder(Root)   
2.    
3. FOR EACH FileExtension in 'csv'   
4.       FOR EACH FoundFile in FileList( Root & '\*.' & FileExtension) 
5.  
6.    Data: 
7.   LOAD *, 
8.   FileBaseName() as FileName 
9.   FROM
10.   [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is '|', msq);;  
11.       
12.   SET vConcatenate = Concatenate (Data);  
13.    
14.      NEXT FoundFile   
15.   NEXT FileExtension   
16.    
17. END SUB   
18.  
19. SET vConcatenate= Data: ;   
20. 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 ?

[tamilarasu]

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.

[#6]

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