Qlik Community

QlikView Documents

Documents for QlikView related information.

Load latest Excel file to Qlikview

MVP
MVP

Load latest Excel file to Qlikview

Hi All,

The below script helps you in loading the latest data from the given folder based on the filename.  The below script helps you in loading the latest year automatically

Example: Filename formats

Budget 2014.xlsx

Budget 2015.xlsx

LET vFilePath = 'C:\Qlikview\Data';

FOR EACH file in FileList('$(vFilePath)\*.xlsx');   // Loops each excel file in the given Folder

LET vFileYear =  Left(Right(file, 9), 4);                    // Gets the year portion from the filename

LET vMaxYear = Rangemax(vFileYear, vMaxYear);  // Gets the max year for every iteration

NEXT

Data:

LOAD *

FROM

[$(vFilePath )\Budget $(vMaxYear).xlsx]      // Load Max year data

(ooxml, embedded labels, table is [Sheet1]);

Based on your requirement you can slightly change the script and use it in your Dashboard.

Hope this helps you.

Regards,

Jagan.

Comments
tripatirao
Contributor II

Dear Jagan,

Very good Post.

My excel are in the format of MIS Septmber 2016.xlsx,MIS October 2016.xlsx,MIS November 2016.xlsx

how can I load the recent file(MIS November 2016.xlsx) into qlikview and create qvd.

Regards

Tripati

MVP
MVP

Try this

LET vFilePath = 'C:\Qlikview\Data'; 

 

FOR EACH file in FileList('$(vFilePath)\*.xlsx');   // Loops each excel file in the given Folder 

LET vFileMonthYear =  DAte(Date#(Replace(Replace(file,'.xlsx'), 'MIS '), 'MMMM YYYY))';                    // Gets the year portion from the filename 

LET vMaxMonthYear = Rangemax(vFileMonthYear, vMaxMonthYear);  // Gets the max year for every iteration 

NEXT 

 

Data: 

LOAD * 

FROM 

[$(vFilePath )\MIS $(vMaxMonthYear).xlsx]      // Load Max Month data 

(ooxml, embedded labels, table is [Sheet1]);

Septmber 2016.xlsx,MIS October 2016.xlsx

Not applicable

Hi

I tried the same example.

But my app is searching for  the value- 19786 and then its throwing error.. not found

If my folders consist file Budget - 19786. Then its working fine.

Can u please suggest on the same

falgunip
New Contributor III

Very good information.

Thank you

Falguni

vardhancse
Valued Contributor II

Hi Jagan,

Thank you so much,

Can please let me know how to load the recent sheet in a excel work book.

MVP
MVP

You can replace the Sheet name dynamically using variables like we are doing for filename.

Data:

LOAD *

FROM

[$(vFilePath )\MIS $(vMaxMonthYear).xlsx]      // Load Max Month data

(ooxml, embedded labels, table is [$(vMaxMonthYear)]);

Hope this helps you.

Regards,

jagan.

passionate
Valued Contributor

Nice Post

Version history
Revision #:
1 of 1
Last update:
‎07-20-2015 11:10 PM
Updated by: