Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Load multiples files with different headers

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?

5 Replies
MVP
MVP

Re: Load multiples files with different headers

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

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

Not applicable

Re: Load multiples files with different headers

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

How can I deal with it ?

Re: Load multiples files with different headers

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.

Not applicable

Re: Load multiples files with different headers

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

Community Browser