Load Files based on data in a Field

[shane_spencer]

I've got a script to loop through a number of folders to load some spreadsheets. Currently I can either loop through all folders

i.e.

for each dir in DirList('..\SOURCE\*')

or loop through specific folders

i.e.

for each dir in 'OCT','NOV'

What I would like to do is load only specific folders based on the contents of a field

i.e.

Please see the attached example of my work so far and some same data. Can anyone please help?

[balar025 · Accepted solution]

Hi ,

P;ease find the below change in code:

FolderList:

LOAD concat(chr(39)&Folders&chr(39),',') as FolderList

FROM [Folders.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vListOfFolder = Peek('FolderList',0,'FolderList');

for each dir in $(vListOfFolder)

for each datafile in FileList('..\SOURCE\$(dir)\DATA.xlsx')

Monthly:

LOAD *,

// '$(datafile)' as filepath,

'$(dir)' as Month

//FROM [DATA.xlsx]

//FROM [..\SOURCE\$(dir)\*.xlsx]

FROm $(datafile)

(ooxml, embedded labels, table is Sheet1);

next datafile

next dir

Regards,

Ravi Balar

[sunny_talwar]

You can try this

FOR i = 1 to FieldValueCount('Folders')

LET vFolder = FieldValue('Folders', $(i));

LET datafile = '..\SOURCE\$(vFolder)\DATA.xlsx';

Monthly:

LOAD *,

'$(dir)' as Month

FROM $(datafile)

(ooxml, embedded labels, table is Sheet1);

NEXT i

[balar025]

Hi Sunny,

As per observation, in your script it will not handle if folder is not present.

-Ravi Balar

[shane_spencer]

Thanks - I'll keep this as an example, but I think Ravi's answer is closer to what I was working on myself using concatenate though I was only about 25% there.

[sunny_talwar]

There are ways around that to make it work.... personally, I used to use Concatenate, but the reason i stopped using Concatenate is because it slows done the reload time. In this case the list to be concatenated is small, so it doesn't matter. But for a longer list (of say 15000 or 50000), its better to use FieldValue functions instead of Concat.

[sunny_talwar]

Like I mentioned above, for your data it might not make a difference, but FieldValue is def. something you would want to use because of its capability to look at DISTINCT values outside of a table .

Best,

Sunny

[shane_spencer]

Thank you. And thank you for the explanation of advantages too!

[sunny_talwar]

No problem at all