Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi Marus Sommer,
Thanks for the reply....
I can n't understand can you please explain with example ....
Thanks & Regards,
Ramu.
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
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
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.
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
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