Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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.

1 Solution

Accepted Solutions
Not applicable

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

LOAD [Field 1],

          [Field 2],

          [Field 3],

          [Field 4]

FROM

$(filefullpath)

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

NEXT

14 Replies
MVP
MVP

Re: Issues loading multiple Excel files

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Issues loading multiple Excel files

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

Not applicable

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.

Not applicable

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.

Not applicable

Issues loading multiple Excel files

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

Not applicable

Re: Issues loading multiple Excel files

TableName:

LOAD *

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

Not applicable

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

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

LOAD [Field 1],

          [Field 2],

          [Field 3],

          [Field 4]

FROM

(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.

Not applicable

Re: Issues loading multiple Excel files

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

LOAD [Field 1],

          [Field 2],

          [Field 3],

          [Field 4]

FROM

$(filefullpath)

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

NEXT

Hope this help,

Anosh

Not applicable

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

LOAD [Field 1],

          [Field 2],

          [Field 3],

          [Field 4]

FROM

$(filefullpath)

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

NEXT

Community Browser