11 Replies Latest reply: Nov 19, 2012 3:36 AM by Johannes Sunden RSS

    Excel multi load

      Hi,

       

       

      I am loding data from excel month wise and i have month wise excel file with same column on that.

       

      Qualify *;

       

      Table1:

       

      load

       

      Field1,

      Field2,

      Field3

      ...

      Field7

       

      From January.xls

       

      load

       

      Field1,

      Field2,

      Field3

      ...

      Field7

       

      From February.xls

       

      upto december and i want to create a single table from this 12 excel file but when i load then 12 single file is created.

       

       

      Thanks

        • Re: Excel multi load
          Johannes Sunden

          Something along these lines should work. Skip the Qualify part as well:

           

          FOR Each File in filelist ('Folder\*.xls')

           

          LOAD

          '$(File)' as Name,

          Field1,

          Field2,

          Field3,

          ...

          Field7

          From '$(File)'.xls

           

          NEXT File

            • Re: Excel multi load

              Hi,

               

              Thanks i provide you 3 sample excel file like wise i have 12 excel files how to make a single file with this 12 excel files.

               

              Thanks,

                • Re: Excel multi load

                  Hi,

                   

                  Please help me for above files its urgent.

                   

                  Thanks,

                    • Re: Excel multi load
                      Johannes Sunden

                      Here you go.

                       

                      1. Put the three Excel files in a folder.

                      2. Create a new QlikView file in the same folder.

                      3. Paste this script in the new QlikView file and reload:

                       

                      FOR Each File in filelist ('*.xls')

                       

                      LOAD

                      '$(File)' as Name,

                      [Txn Date],

                           [Value Date],

                           Description,

                           [Ref No.],

                           Debit,

                           Credit,

                           Balance

                      FROM

                      [$(File)]

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

                       

                      NEXT File

                        • Re: Excel multi load

                          Hi,

                           

                          Your script runs perfectly but there is Qualify *; is written above the for each file thats why it created three tables

                           

                           

                          Qualify *;

                          FOR Each File in filelist ('*.xls')

                           

                          LOAD

                          '$(File)' as Name,

                          [Txn Date],

                               [Value Date],

                               Description,

                               [Ref No.],

                               Debit,

                               Credit,

                               Balance

                          FROM

                          [$(File)]

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

                           

                          NEXT File

                           

                           

                          Thanks

                            • Re: Excel multi load
                              Johannes Sunden

                              Remove the Qualify part. No point in having three separate tables when the data structure is the same and all that changes is the month

                                • Re: Excel multi load

                                  Hi,

                                   

                                  You are great, But Qualify *; syntax is put in the above of all this load because i load this table independently reason there is another single table with same fields but i dont want to connect with this table. If i remove Qualify synthetic key table is created please help for this.

                                   

                                  Thanks

                                    • Re: Excel multi load
                                      Johannes Sunden

                                      Then just rename the fields. Example below with a few fields renamed:

                                       

                                      FOR Each File in filelist ('*.xls')

                                       

                                      LOAD

                                      '$(File)' as Name,

                                      [Txn Date] as TransactionDate,

                                           [Value Date] as NewValueDate,

                                           Description as SomeOtherDescription,

                                           [Ref No.],

                                           Debit,

                                           Credit,

                                           Balance

                                      FROM

                                      [$(File)]

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

                                       

                                      NEXT File

                              • Re: Excel multi load

                                Hi,

                                 

                                Since all your files seem to have same column names and similar type of data, to make them a single excal file using QlikView you can do this:

                                 

                                1. Load all the twelve excel files into a QVW.

                                2. Since all the column names are same, these will automatically concatenate to form one single table for all 12 files. (Optionally, you could use the concatenate keyword after each file load.

                                3. Now add all these columns into a table box and export it to Excel.

                                 

                                This way you will have a table box (similar to your data in Excel) in QlikView which has data for all 12 months in one place.

                                 

                                All_Months_Data:

                                 

                                //Loading January Excel file

                                 

                                Load

                                [Txn Date],

                                [Value Date]

                                Description

                                Ref No.

                                Debit

                                Credit

                                Balance

                                from ExcelFileForJanuary;

                                 

                                concatenate

                                 

                                //Loading February Excel file

                                 

                                Load

                                [Txn Date],

                                [Value Date]

                                Description

                                Ref No.

                                Debit

                                Credit

                                Balance

                                from ExcelFileForFebruary;

                                 

                                concatenate

                                 

                                //Loading March Excel file

                                 

                                Load

                                [Txn Date],

                                [Value Date]

                                Description

                                Ref No.

                                Debit

                                Credit

                                Balance

                                from ExcelFileForMarch;

                                .

                                ...

                                ......

                                and so on for all the months (as long as they have same column names).

                                 

                                 

                                Hope that helps.

                                 

                                Regards,

                                 

                                -Khaled.

                                  • Re: Excel multi load

                                    Hi,

                                     

                                    But qualify *; syntax is put in the above of all this load because i load this table in dependently reason there is another single table with same fields but i dont want to connect with this table.

                                     

                                    Thanks

                                      • Re: Excel multi load

                                        Hi,

                                         

                                        Johannes Sunden has a better solution for your previous question

                                         

                                        Regarding the Qualify statement, you can do something like this:

                                         

                                        First load the Excel files without the Qualify * statement, and then do a resident load of the resultant table (12 excel tables concatenated) and drop the original load. You could use a qualify keyword before Resident load to avoid this table from associating itself with other tables in the script.

                                         

                                        I am not sure if this is the best solution, but it will certainly serve the purpose

                                         

                                        AllTables_Old:

                                         

                                         

                                        LOAD [Txn Date],

                                            
                                        [Value Date],

                                            
                                        Description,

                                            
                                        [Ref No.],

                                            
                                        Debit,

                                            
                                        Credit,

                                            
                                        Balance

                                        FROM

                                        [C:\Downloads\300-01-2012.xls]

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



                                        Concatenate



                                        LOAD [Txn Date],

                                            
                                        [Value Date],

                                            
                                        Description,

                                            
                                        [Ref No.],

                                            
                                        Debit,

                                            
                                        Credit,

                                            
                                        Balance

                                        FROM

                                        [C:\Downloads\300-02-2012.xls]

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



                                        QUALIFY *;

                                        New:

                                        Load *

                                        Resident AllTables_Old;

                                        Drop Table AllTables_Old;

                                         

                                        Hope that helps.

                                         

                                        Regards,

                                         

                                        -Khaled.