Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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

View solution in original post

14 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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
Author

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
Author

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
Author

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
Author

TableName:

LOAD *

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

Not applicable
Author

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
Author

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
Author

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