load files with specific names from folder through for loop

[markgraham123]

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.

[Anonymous]

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

[markgraham123]

Hi,

It is not reading the file name into the Load * statement

[Anonymous]

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

[markgraham123]

Getting the same issue Wallo.

Table1:

      LOAD *

  FROM [\\NewFolder\New Files\file1,file2,file3,file4,file5,file6,file7.qvd] (qvd);

[sunny_talwar · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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';

[markgraham123]

Thanks a lot Wallo

It worked.

[markgraham123]

Sunny,

it worked:)

Thanks a ton for your time and patience bro.

Any lime light on how Concat was interrupting??

[sunny_talwar]

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