Pick the latest excel file from folders

[#1]

Hi All,

How to  pick the Last Excel file from folders and then load.

My folder structure is like below.

2014

   file1

   file2  ....

   file n

2015

   file1

   file2

   file n

I want to pick the file n from 2014 folder and file n from 2015 like that dynamically ... how can i achieve the above scenario

Please help me ASAP.

Thanks & Regards,

Ramu.

[marcus_sommer · Accepted solution]

For this you could use filelist and dirlist (great example in the help by searching to for each) to run through your folder and files and check various things like filetime, size, records, fields and some more. Often do you will need a two-step strategy for this - first check and second the real execution. But it's only the first/last file you could take the suggestion from Rob:

Re: Re: Oldest Excel file LOADING

Re: How to get the last updated QVD?

- Marcus

[#3]

Hi Marus Sommer,

Thanks for the reply....

   I can n't understand can you please explain with example ....

Thanks & Regards,

Ramu.

[evan_kurowski]

Hello Marcus, Ramu,

Not trying to re-open the thread or anything, but wanted to request some feeback on my own issue regarding this.

I too have been trying to detect the time of source files using FileList() iteration with the FileTime() function.  This had always worked very consistently for me up till the present, until I encountered an example in which the file times of network content started varying from what was being reported in Windows explorer.

If I opened explorer and went to  \\networkserver\flat_file_repository, the Windows modification time might say 5:12pm, but the QlikView file time would read in exactly 1 hour earlier for 4:12pm.

So I started to contemplate the network server perhaps being in a different timezone (often the case), but if that explains the difference between Explorer's modification time & QlikView's FileTime(), how do I get these two back on sync?

If anyone has any thoughts on this, please drop by in this other thread

[#5]

Hi Ramu ,

Below code would work .

set Path=D:\Qlik_materials\data;

/* i am assuming above path as base path where my year wise folder contains files */

Sub GetFolder(Path)

  for each Mainfolder in DirList(Path)

  for Each folder in DirList(Mainfolder&'\*')

  Call GetFile(folder);

  Latest :

  LOAD [File Path],Max([File Time]) as [Max Time] Resident Files Group By [File Path];

  Left Join(Latest)

  LOAD [File Time] as [Max Time],[File Name] Resident Files;

  DROP Table Files;

  let vName=Peek('File Name',0,'Latest');

  DROP Table Latest;

  Dataset :

  LOAD * ,'$(vName)' as [Src File]From '$(vName)';

  let vName='';

  Next

  Next

End Sub

Sub GetFile(folder)

  For Each ext in 'txt'

  For Each file in Filelist(folder&'\*.'&ext)

  Files:

  LOAD  '$(folder)'   As [File Path], '$(file)' as [File Name] ,FileTime('$(file)') as [File Time]  AutoGenerate(1) ;

  next

  next

End Sub

CALL GetFolder('$(Path)');

- Nitesh Chavan

[#6]

Hi Nithesh Chavan,

Thanks for the reply....

It's not working Nitesh and the years folder contains lot of excel files i want to load last file in every years folder dynamically .... can you please help me....

Thanks.

Ramu.

[marcus_sommer]

I couldn't it really better explain as the various examples within the links it does. Dirlist and Filelist loop through the specified folders and pick up each or folder/file or which with a certain condition and those could be further checked or read on various properties.

Helpful might be for you to play around with this feature not only with your current task else in general to create tables which contain some meta-data to your data-files maybe like Gysbert it does in one of the links. The feature of dirlist/filelist is quite important and will be often needed therefore it's worth to invest some time to understand it well and build some templates (within a sub-routine or an include-variable) which you could easy use and adjust by later challanges.

- Marcus

[#8]

Dear Ramu ,

Code works fine , if the source file  of .txt extension .

In your case you are referring excel file as source file , so you have to make some changes

1 . Change file type extension in  GetFile  sub routine

Sub GetFile(folder)

  For Each ext in 'xlsx'

.

.

remaining code as it is

2, As we are fetch data from excel files , in from clause we have mention meta details for excel file , so make below changes in GetFolder sub routine

Sub GetFolder(Path)

.

.

.

Dataset :

  LOAD * ,'$(vName)' as [Src File]From '$(vName)'  (ooxml, embedded labels, table is Sheet1) ;

.

.

.

End sub

Happy learning .

- Nitesh Chavan