Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS

Load latest Excel file to Qlikview

MVP & Luminary
MVP & Luminary

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
Creator II
Creator 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

0 Likes
MVP & Luminary
MVP & Luminary

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

0 Likes
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

0 Likes
Contributor III
Contributor III

Very good information.

Thank you

Falguni

0 Likes
Specialist III
Specialist III

Hi Jagan,

Thank you so much,

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

0 Likes
MVP & Luminary
MVP & Luminary

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.

0 Likes
Specialist
Specialist

Nice Post

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