Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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') ;
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
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.
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
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.
Show me script by loading only one file from your connection first