Multiple subfolder csv's load

[pratham39]

Hi,

I have below folder subfolder structure,

lib://year=2021/month=01/day=01/Filename.csv

lib://year=2021/month=01/day=02/Filename.csv

.

.

lib://year=2022/month=01/day=02/Filename.csv

.

.

lib://year=2023/month=03/day=07/Filename.csv

likewise almost 2 years data in such folder structure,

please help me to write automated code which will load and concat all the data like (for loop) or another 

Thanks

[neerajthakur]

for each file in filelist('lib://QVD/*.qvd')

YourTableName:

load *
from [$(file)](qvd);

next file

Exit Script;

or this

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              $(vConcatenate)

                              LOAD *, '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

          for each SubDirectory in dirlist( Root & '\*' )

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('C:\QVD\F') ;

[pratham39]

Hi @neerajthakur 

Thanks for ur input...

I think this will work but I am not sure how to write exact code in my situation ...

like if I try your first code-

------------------------------------------------------------------

for each file in filelist('lib://QVD/*.qvd')

YourTableName:

load *
from [$(file)](qvd);

next file

Exit Script;

----------------------------------------------------------------

Should i write all csv names ?

please help me in detail so i can close this thing 

very appreciate your help

Thanks

[neerajthakur]

No need to write all csv name as * will pick all the files with the extension specified in script replace .qvd with .csv and it will work for all the files in that folder.

[pratham39]

I have S3 connection->Data-> and then year month day wise folders,

so as per you told , i tried writing below code,

but in output i got

Can you please correct

I am doing something wrong i guess

Thanks

[DiegoF]

I think that if I understood correctly, your path is: lib://year=2021/month=01/day=01/Filename.csv, so you need to write all the path until the .csv file, because it´s gonna iterate each year and month (1,2,3...) you should use something like this:

for each file in filelist('lib://AWS_S3/Data/year=*/month=*/*.qvd')

YourTableName:

load *
from [$(file)](qvd);

next file

Exit Script;

I think that this is not gonna work tho, because idk if it will iterate correctly, because you have year and month that have to iterate (year from 2021 to 2023, and month all the time 12 months per year) you should probably use more for's and set a variable that will iterate the number of times you wish, in this case, year only 2 times, and month 12 times.

[pratham39]

@DiegoF not working .

@neerajthakur please suggest..

Thanks

[neerajthakur]

Show me script by loading only one file from your connection first

[pratham39]

Hi @neerajthakur ,

Please see below script and data load completed view

Thanks

[pratham39]

Hi @neerajthakur ,

trying second code suggested by you.

Should I create Root variable ?

Thanks