topic Re: Issues loading multiple Excel files in QlikView

Issues loading multiple Excel files

Thu, 17 May 2012 12:40:48 GMT

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

jonathandienst — Thu, 17 May 2012 15:13:35 GMT

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

Thu, 17 May 2012 15:36:42 GMT

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

Thu, 17 May 2012 16:40:36 GMT

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

Thu, 17 May 2012 16:40:45 GMT

Hi Anoush,

Many thanks.

Rajiv.

Issues loading multiple Excel files

Fri, 18 May 2012 05:26:25 GMT

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

Fri, 18 May 2012 06:17:46 GMT

TableName:

LOAD *

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

Re: Issues loading multiple Excel files

Thu, 24 May 2012 09:22:56 GMT

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.

Re: Issues loading multiple Excel files

Thu, 24 May 2012 18:13:18 GMT

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$]);

Hope this help,

Anosh

Re: Issues loading multiple Excel files

Fri, 25 May 2012 14:33:08 GMT

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$]);

Re: Issues loading multiple Excel files

Wed, 30 May 2012 14:46:12 GMT

Hi Anoush,

This script doesn't seem to work for CSV files.

i.e.

FROM

$(FileFullPath)

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

Do I need to amend the script?

Thanks.

Rajiv.

Re: Issues loading multiple Excel files

Wed, 30 May 2012 16:59:58 GMT

HI Rajiv,

Use the below script. I think you haven't changed the file name extension from xls to csv. See the bold part in below script.

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

let filefullpath = ('');

LOAD Data1,

Data2

FROM

$(filefullpath)

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

Hope this help,

Anosh

Re: Issues loading multiple Excel files

Thu, 31 May 2012 08:06:37 GMT

Hi Anoush,

Thanks for getting back to me. Yes, I amended the script in the beginning, replacing .xls with .csv and it didn't seem to work.

Have you tried it the above script? Does it work?

Thanks.

Rajiv.

Re: Issues loading multiple Excel files

Thu, 31 May 2012 12:14:41 GMT

Hi Rajiv, Yes it is working for me. Can you attach your qvw explaining your scenario and some excel sheets to load data. I will try to debug it and check.

Re: Issues loading multiple Excel files

Wed, 12 Jun 2013 13:08:45 GMT

Hi ,

For the first sheet1 i have fields : field1, field2

and for sheet2 : field3,field4

i mean , different sheets have different fields .

is it work with your logic??????

Thanks and Regards

Satti .