5 Replies Latest reply: Jan 27, 2016 9:12 AM by Thomas Ayoub RSS

    Load multiples files with different headers

    Thomas Ayoub

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

        • Re: Load multiples files with different headers
          Massimo Grossi

          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
            Tamil Nagaraj

            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
                Thomas Ayoub

                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 ?