topic Loading Excel file with data in Qlikview automatically in QlikView

Loading Excel file with data in Qlikview automatically

Mon, 21 Jan 2013 10:10:35 GMT

Hi everyone,

I have developed a qlikview app where in the data is in excel files for each month. Excel file of each month is loaded in the app seperately.

Is there a way the app pulls the new excel file that is generated each month when the excel file is dumped in the server?. I heard that there is a code that goes into the load script using a WILDCARD function. Not sure if its true.

The only other solution is to manually load the new excel file of that month into Qlikview app which is not what I am looking for.

Can I get some help on this please.

Regards,

Koushik.

Re: Loading Excel file with data in Qlikview automatically

swuehl — Mon, 21 Jan 2013 11:15:52 GMT

Yes, it should be possible to load the different files using a wildcard in the file name.

If you excel files have same structure, say two fields FIELDA, FIELDB, and your files are named like DATA_201212,

then you would load 1 file like

LOAD

FIELDA,

FIELDB

FROM

[DATA_201212.xls]

(biff, embedded labels, table is Sheet1$);

Then use a wildcard (asterisk) to load all excel files matching the file name in the working directory:

LOAD

FIELDA,

FIELDB

FROM

[DATA_*.xls]

(biff, embedded labels, table is Sheet1$);

Re: Loading Excel file with data in Qlikview automatically

Mon, 21 Jan 2013 11:54:28 GMT

The structure is the same but the names are different.

Example: In the app. I got the excel data from jan to dec 2011 and they are named as Data_Jan_2011 , Data_feb_2011 and so on. I have loaded them seperately. I cant merge all of them.

Re: Loading Excel file with data in Qlikview automatically

swuehl — Mon, 21 Jan 2013 13:05:08 GMT

Have you tried something like

[Data*.xls]

as file name in your LOAD statement? I am not sure what you mean with: I can't merge all of them.

Re: Loading Excel file with data in Qlikview automatically

Mon, 21 Jan 2013 13:20:03 GMT

Thanks Swuehl.

What I meant was. The data is in seperate excel sheets for each month with file names Data_jan_2011, Data_Feb_2011 untill December and have loaded them individually rather than merging all the excel files into one and loading it.

Re: Loading Excel file with data in Qlikview automatically

Mon, 21 Jan 2013 13:59:10 GMT

Hi,

You may try a loop

declare a variable like

ShName = 'jan_2011', 'feb-2011'.... (you can even do it autimatically with month and year based from a start to an end, that makes it automatocally dynamic).

then create a loop

FOR EACH v IN ShName

LOAD ...... FROM YourFile.xls (biff, embedded labels, table is $(v)$;

best regards

christian

Re: Loading Excel file with data in Qlikview automatically

AbhijitBansode — Tue, 22 Jan 2013 18:07:47 GMT

[Data*.xls] will work perfectly even if u have separate excel file with the naming convention u have specified.