Load Xlsm file every month (append)

[IgorGol]

Hi,

I have a  excel file with name "Data 01/01/2020" in a folder

In next month I will have also a file "Data 01/02/2020" in the same folder

I want to append them and every month to add the data from next file to previous data that allready loaded

So:

1. What should I write in the script so that will work?

2. How I make this process automatic?

Thanks a lot!

[Prashant_Naik]

Hi,

You can write this.

Tmp_tbl:

Load * from date*.xlsx;

Thie date* denotes that read all the files in the specific folder starting from date keyword. 

And it will read all the files that will get uploaded in the folder with the date string as first keyword.

Regards,

Prashant

[IgorGol]

Hi,

its located in library/folder and I tried it , didn't work

what I am doing wrong?

Tmp_tbl:

Load *

FROM [lib://Folder/Date*.xlsm];

[tm_burgers]

I think you used "Date*" but if your data files all follow the convention "Data  mm-dd-yyyy" then you need to use the below:

Tmp_tbl:

Load *

FROM [lib://Folder/Data*.xlsm];

p.s. - if your data doesn't have a date-field you can add the date from your filename as a field within the data set - 

Right(FileBaseName(), 10) AS DataFileDate

[Prashant_Naik · Accepted solution]

Hi,

I consider you also have mention the excel sheet in which your actual data is residing, like this

[Sheet1]:
LOAD *
FROM [lib://AttachedFiles/Book*.xlsm]
(ooxml, embedded labels, table is Sheet1);

Regards,

Prashant

[IgorGol]

Thanks!

1. So my script should be : 

Tmp_tbl:

Load *,Right(FileBaseName(), 10) AS DataFileDate

FROM [lib://Folder/Data*.xlsm];

?

2. why you took 10 ? Right(FileBaseName(), 10) 

3. Is there a way to to take a date from file details? "Date modified" ?

Thanks!

[Prashant_Naik]

Yes,

You can take the Modified date of the file from the function filetime() and applying Date() function on it like

Load *,

Date(filetime()) As Datefield      //this will give date of the file modified on

FROM [lib://AttachedFiles/Book*.xlsm]
(ooxml, embedded labels, table is Sheet1);

Filetime() function 

Filebasename function 

Regards,

Prashant

[tm_burgers]

Yes, you can use the modified date as Prashant said - as sometimes I open my excel files later on and could potentially make changes, but don't want that to effect the "date" of the data - my solution is to take the 10 chars at the right of my file names, which are the date.