Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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.
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.
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
TableName:
LOAD *
FROM \*.xls (biff, embedded labels);
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.
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
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