Load all files from folder

[viveksingh]

HI All,

i have a folder path where every day file will be placed. Lets say one file is placed with year_month_date as file name. If one file is placedin the folder it should load and for tomorrow one more file will be placed with date as tomorrow. tgen it should load yesterday’s file and today’s file. As the field name and field count is same, it will concatinate. Like this we will get one file every day So All files should load.

PLease help on this

‌

[andrey_krylov]

Hi. If all these files have the same structure then you can use a wildcard

LOAD * from [PathToFolder\*.xls](ooxml, embedded labels, table is Sheet1);

or like this

[Table]: LOAD * InLine [TempField];

for each FoundFile in filelist( 'PathToFolder\*.xls')

     If SomeCondition Then

       Concatenate(Table) LOAD * from [$(FoundFile)] (ooxml, embedded labels, table is Sheet1);

     EndIf

next FoundFile

[viveksingh]

‌Thanks for the reply Andrew.

All files are text files Which comes on daily basis.

lets say for today- 28/9/2018. Below is the file name

textfile_2018-sept-28.txt

for tomorrow we will get one more file. Which mean in the folder we have two files. One is yesterday file and other is today’s fil. We have to load both.

list of files available in the folder for tomorrow:

textfile_2018-sept-28.txt

textfile_2018-sept-29.txt

[andrey_krylov]

Try to put asterisk to the path

LOAD * FROM [PathToFolder\textfile_2018-sept-*.txt] (txt, codepage is 1251, embedded labels, delimiter is ' ', msq);

[viveksingh]

I tried the sane and working fine. But my concern is going forward the year will change like 2019 and month also changes. so for that i have created two variables one is for current year and other is for current month. How to add this variable in from path.

Let v_current_year= year(today());

Ler v_current_month=Num(month(today()));

[andrey_krylov]

If you want to load all files to the only table then slightly change the path

LOAD * FROM [PathToFolder\textfile_20*-*-*.txt] (txt, codepage is 1251, embedded labels, delimiter is ' ', msq);

or

LOAD * FROM [PathToFolder\textfile_$(v_current_year)-$(v_current_month)-*.txt] (txt, codepage is 1251, embedded labels, delimiter is ' ', msq);

but v_current_month in this case is numeric like 1, 2,...12 and files must be renamed as textfile_2018-9-28.txt

[rajivmeher]

Hi Vivek

Just want some clarification, do you want to load every files from the folder? Or you want to load only files for the current month or only files for last two days?

If you want to load all files than you can use the following:

LOAD *

FROM [FolderPath\textfile_*.txt](txt, codepage is 1251, embedded labels, delimiter is ' ', msq);

Regards

Rajiv.