topic Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd in QlikView

Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 05:36:59 GMT

Dear Team ,

I have 12 files like AssetTrackingAug1.xlsx , AssetTrackingSep1.xlsx , AssetTrackingOct1.xlsx ,etc .

1. I want to load this dynamically and save it into QVD.

2. if AssetTrackingAug1.xlsx file modified later then this should pickup for loading

Thanks

Vikas

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

Anonymous — Fri, 18 Dec 2015 05:48:44 GMT

Hi,

Maybe this would be helpful to you.....

Load multiple excel files from Multiple excel sheetsinto QV‌

Regards,

Bhavini S Mehta.

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

Kushal_Chawda — Fri, 18 Dec 2015 05:57:08 GMT

How would you track the change in file?

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 05:59:09 GMT

we load file name with date into qvd like Filename_QVD_History.QVD and check it with new file ?

Vikas

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 05:59:45 GMT

I want to load hole file again by doping old if date is changed

Vikas

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

Anonymous — Fri, 18 Dec 2015 06:05:29 GMT

Hi,

I think you need to apply Incremental load (Insert, Update and Delete) for this.

Below is the link which might help you:

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

And For loading Multiple Excels this would be useful.....

Load multiple excel files from Multiple excel sheetsinto QV

Regards,

Bhavini S Mehta.

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 06:35:52 GMT

Dear All,

I have created qvw file for the same but needs some improvements

Vikas

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

Kushal_Chawda — Fri, 18 Dec 2015 06:47:09 GMT

I think you need to append the Timestamp when file gets modified for eg Filenanme_2015-12-17 12:00:00 so that we can load the timestamp from filename & compare it with filename again.

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 06:49:29 GMT

yes you are right and and after comparing it should take new excel sheet data. Can you help to get the same with qvw.

Vikas

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 07:05:40 GMT

maxgro‌ Can you please help to achieve the same

Vikas

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 08:07:42 GMT

Does Any one have solution for this ??

Vikas

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

Peter_Cammaert — Fri, 18 Dec 2015 09:57:38 GMT

There are two script functions that may be useful:

FileTime(filename) = reports the last modification time of the specified file (e.g. your Excel files)
QvdCreateTime(filename) = returns the creation timestamp of the Qvd file specified (e.g. your QVD). This tilestamp is read from the XML-header, not from the file system

Compare the two for all files matching a file name pattern (e.g. AsseTracking????.xlsx) and reload the Excel files that are newer than your QVD. You can use a FOR EACH loop to do that.

Best,

Peter

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 10:09:56 GMT

Idea is good , searching for code.

Thanks

Vikas

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Fri, 18 Dec 2015 10:17:03 GMT

can you help me in build this code ?

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

PradeepReddy — Sat, 19 Dec 2015 06:23:47 GMT

Hi Vikas,

Please find the attachment. It might be helpful.

Note:

For each file the sheet name is consistent. based on this assumption, In October file, I changed the Sheet name.

Thanks,

Pradeep

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

Kushal_Chawda — Sat, 19 Dec 2015 10:17:05 GMT

Hi Vikas,

Please find the attached QVW file.

Open QVW file and go to script,

Replace the Excel file path and QVD path with your actual path (Variables are created in Main Tab)

I have divided scripts in Three parts

1 Tab - Load all excel file Code. This is first part of the code which will create the QVD file for all the excel files currently available in folder.

Note- You should comment this code once QVD is created to check the New or updated file on next reload.

2 Tab - Code to check if any New file is arrived in folder. If new file is arrived it will load Only New file and will Overwrite the existing QVD with New file data.

3 Tab - Code to check if existing file is update or not using Filetime() function. If file is updated it will load only updated file and will overwrite the existing QVD with updated data.

I hope this will help.

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

Kushal_Chawda — Sun, 20 Dec 2015 15:29:09 GMT

Hi Vikas,

Please find the updated application which works for me with the scenario I have mentioned in my above post

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Mon, 21 Dec 2015 04:10:42 GMT

Thanks Kush !!

Re: Load excel files dynamic and check whether excel file is changed or modify if yes load into qvd

vikasmahajan — Mon, 08 Feb 2016 11:49:29 GMT

For third part it is taking to much time , can we have timestamps logic with file. if file time is changed then delete records from qvd and take whole new file into qvd.

Vikas