Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to load 5 files from the folder of 1000 files.
I have stored file names in vFilenames.
vFilenames = abg, dhjk, qwe, qwee, gsdf;
How can i load only these 5 files from the folder, instead of loading all files and then filtering by the filebasename()?
FOR EACH File in FileList('$(vFolder)\$(vFilenames).QVD') //Storing filenames in the variable 'File'
Table1:
LOAD *
FROM [$(File)] (qvd);
NEXT
The above is not working.
Any help is highly appreciated.
How about something like this?
LET NumRows=FieldValueCount('DateKey');
SET vFolder = '\\Folder\';
FOR i=1 to $(NumRows)
LET vFilelist = peek('DateKey',$(i)-1,'YourFileListTable');
Table1:
LOAD *
FROM [$(vFolder)\$(File).qvd] (qvd);
NEXT
Hi,
It is not reading the file name into the Load * statement
Sorry. I copied some of yours and didn't rename some things. I think this fixes it.
LET NumRows=FieldValueCount('DateKey');
SET vFolder = '\\Folder\';
FOR i=1 to $(NumRows)
LET vFile = peek('DateKey',$(i)-1,'YourFileListTable');
Table1:
LOAD *
FROM [$(vFolder)\$(vFile).qvd] (qvd);
NEXT
Getting the same issue Wallo.
Table1:
LOAD *
FROM [\\NewFolder\New Files\file1,file2,file3,file4,file5,file6,file7.qvd] (qvd);
Remove the Concat and then try this
LET NumRows=FieldValueCount('%DateKey');
SET vFolder = '\\Folder\';
FOR i=1 to $(NumRows)
LET vFile = FieldValue('%DateKey', $(i));
Table1:
LOAD *
FROM [$(vFolder)\$(vFile).qvd] (qvd);
NEXT
Just looking at it, Sunny's should work.
Here's my attempt. I tested it and it worked for me.
FileList:
LOAD * INLINE [
DateKey
20160101
20160102
20160103
];
LET NumRows=FieldValueCount('DateKey');
set vFolder = '\\Folder\';
FOR i=1 to $(NumRows)
LET vFile = peek('DateKey',$(i)-1,'FileList');
let vQVD= (vFolder) & '\'& $(vFile);
Table1:
LOAD *
FROM $(vQVD)(qvd);
NEXT
And of course as soon as I'm happy with my answer I notice something missing.
You'll probably need to add the file extension unless it's going to be in your file list table.
I would just do it like this:
let vQVD= (vFolder) & '\'& $(vFile) & '.qvd';
Thanks a lot Wallo
It worked.
Sunny,
it worked:)
Thanks a ton for your time and patience bro.
Any lime light on how Concat was interrupting??
Concat is just putting together a list which might have worked (with some tweaks, I guess) if we wanted to go our original route of this
SET vFilenames = 'abg', 'dhjk', 'qwe', 'qwee', 'gsdf';
FOR EACH File in $(vFilenames)
Table1:
LOAD *
FROM [$(vFolder)\$(File).qvd] (qvd);
NEXT
Here I do want a comma separated list of all the filenames in the for each loop.
But, atkinsow came up with another way (and probably a better method) to do this. I couldn't think about this because I was too much invested in the first method. but the FieldValue() and FieldValueCount() method doesn't require a concatenated list of filenames. FieldValue() function is able to pick one fieldValue at a time from a field in your script. So, this is why you don't need the concatenation. This helps because Concat is a aggregation function which unnecessarily slow down your reload (when you have lot of data).
Best,
Sunny