14 Replies Latest reply: Jun 12, 2013 9:08 AM by Satti Aluri RSS

    Issues loading multiple Excel files

      Hi,

       

      I've been experiencing some issues in loading multiple excel files.  The script freezes and doesn't respond when I try to do this. At the moment, I have 4 files to load into QlikView.  However, if I just load one excel file, then it works fine.  The files are exactly the same with the same column headings.  Each seperate excel represents a different month.

       

      Has anyone experienced similar issues?

       

      Any help would be greatly appreciated.

       

      Many thanks.

       

      Rajiv.

        • Re: Issues loading multiple Excel files
          Jonathan Dienst

          Hi

           

          I suspect that your load is creating a separate table for each Excel file, resulting in a complex synthetic key. This can cause the script to freeze or even crash if the key and dataset are large enough. You don't see this with a single file because then only one table is created and no synthetic key.

           

          If that does not help, then I suggest that you post your application, a sample of it or your script for more help.

           

          Regards

          Jonathan

          • Issues loading multiple Excel files
            Anosh Nathaniel

            Hi Rajiv,

             

            Are you loading all these excel file in a single table in Qlikview? As column headings are same they would be concatinated into single Qlikview table. Have you written a loop over monthly excel file to load data into single table?

            Please post a sample of your Qlikview application so that it will help.

             

            Hope this help,

            Anosh

              • Re: Issues loading multiple Excel files

                Hi Anoush,

                 

                Thanks for getting back to me.  I no longer have the script as I put all the data into a single CSV file, which works but is not ideal as it requires to manipulate the data.  What does the loop do over the the monthly excel to load data into a single table?

                 

                Many thanks.

                 

                Rajiv.

                  • Issues loading multiple Excel files
                    Anosh Nathaniel

                    Hi Rajiv,

                     

                    Sometimes we need to load multiple excel sheets having some naming convension like

                    Mar2012, Apr2012, May2012 ... etc. We don't know the number of excel which is coming from source or every month we get data in excel having monthyear as excel name. In this scenario we do not hardcode the excel name in our Qlikview script rather than we write logic in QV script to generate this name and load the corresponding excelsheet everymonth whenever it arrives.

                    Sometimes the case is, that we get data but in a single excel having different tabs. For that also we do the same thing.

                     

                    Once we have all the names of tabs or excel sheet, We loop over them and fetch data from it in Qlikview script.

                     

                    Hope this help,

                    Anosh

                  • Re: Issues loading multiple Excel files

                    Hi Anoush,

                     

                    Thanks for getting back to me.  I no longer have the script as I put all the data into a single CSV file, which works but is not ideal as it requires to manipulate the data.  What does the loop do over the the monthly excel to load data into a single table?

                     

                    Many thanks.

                     

                    Rajiv.

                  • Re: Issues loading multiple Excel files

                    TableName:

                    LOAD *

                    FROM \*.xls (biff, embedded labels);

                      • Re: Issues loading multiple Excel files

                        Hi,

                         

                        Thanks for your help. I'm still having issues and I am not quite sure what the loop does.

                         

                        This is the script I am current using:

                         

                        LOAD [Field 1],

                                  [Field 2],

                                  [Field 3],

                                  [Field 4]

                        FROM

                        [Z:\My Documents\Folder 1\Folder 2\01 January 2012 - Raw Data.xls]

                        (biff, embedded labels, table is [Data$]);

                         

                        LOAD [Field 1],

                                  [Field 2],

                                  [Field 3],

                                  [Field 4]

                        FROM

                        [Z:\My Documents\Folder 1\Folder 2\02 February 2012 - Raw Data.xls]

                        (biff, embedded labels, table is [Data$]);

                         

                        and so on....

                         

                        The [Data$] tends to change from month to month - but I go into the excel file and rename this worksheet to "Data".  There are multiple worksheets in the file, but I only select the worksheet called "Data" to feed into QlikView.

                         

                        Any help would be greatly appreciated.

                         

                        Thanks.

                         

                        Rajiv.

                          • Re: Issues loading multiple Excel files
                            Anosh Nathaniel

                            Hi Rajiv,

                             

                            See the below example of looping over start date to end date and fetching data from multiple excel sheets based on number of days between start date and end date.

                             

                            SET ThousandSep=',';

                            SET DecimalSep='.';

                            SET MoneyThousandSep=',';

                            SET MoneyDecimalSep='.';

                            SET MoneyFormat='$#,##0.00;($#,##0.00)';

                            SET TimeFormat='h:mm:ss TT';

                            SET DateFormat='DD/MM/YYYY';

                            SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

                            SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                            SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                             

                            //Provide date range to fetch data from multiple excel sheets

                            set startDate = '01/01/2012';

                            set endDate= '01/02/2012';

                             

                            let a=Interval(endDate-startDate,'d');

                            for i=0 to a;

                             

                             

                            let filename = day(Date(startDate+i))&' '&Date(Date(startDate+i),'MMMM')&' '&YEAR(Date(startDate+i))&' - Raw Data.xls';

                            let filefullpath = ('[Z:\My Documents\Folder 1\Folder 2\'&filename&']');

                            LOAD [Field 1],

                                      [Field 2],

                                      [Field 3],

                                      [Field 4]

                            FROM

                            $(filefullpath)

                            (biff, embedded labels, table is [Data$]);

                             

                            NEXT

                             

                             

                             

                            Hope this help,

                            Anosh

                              • Re: Issues loading multiple Excel files

                                Thanks Anoush.  I've slightly modified the code which works for me:

                                 

                                 

                                SET ThousandSep=',';

                                SET DecimalSep='.';

                                SET MoneyThousandSep=',';

                                SET MoneyDecimalSep='.';

                                SET MoneyFormat='$#,##0.00;($#,##0.00)';

                                SET TimeFormat='h:mm:ss TT';

                                SET DateFormat='DD/MM/YYYY';

                                SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

                                SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                                SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                                 

                                //Provide date range to fetch data from multiple excel sheets

                                set startDate = '01/01/2012';

                                       endDate= addmonths(today(),-1);

                                 

                                let a=Interval(month(endDate)-month(startDate),'m');

                                for i=0 to a;

                                 

                                 

                                let filename = Date(addmonths(startDate,i),'MM')&' '&Date(addmonths(startDate,i),'MMM')&' '&Year(Date(endDate))&' - Raw Data.xls';

                                let filefullpath = ('[Z:\My Documents\Folder 1\Folder 2\'&filename&']');

                                LOAD [Field 1],

                                          [Field 2],

                                          [Field 3],

                                          [Field 4]

                                FROM

                                $(filefullpath)

                                (biff, embedded labels, table is [Data$]);

                                 

                                NEXT