Concatenate Excel files with names that change over time

[brf10043]

All,

I have a QV file that loads and concatenates data from multiple excel files.  Each file represents a month with names such as 2016_03_All, 2016_02_All and so on.  Each month I drop the earliest month and add in a new month.

My current load statement looks similar to this:

LOAD [Reporting Date],
[IP Address],
[DNS Name],
[NetBIOS Name],
[Short Name],
etc
FROM
[..\01_ExternalData\2015_04_All.xlsx]
(ooxml, embedded labels, table is entry);
Concatenate
LOAD [Reporting Date],
[IP Address],
[DNS Name],
[NetBIOS Name],
[Short Name],
etc
FROM
[..\01_ExternalData\2015_05_All.xlsx]
(ooxml, embedded labels, table is entry);

With my current approach I have edit the load file each month to remove the earliest month and add the new month.

Who do I edit my load scrip so that It loads each file from the source directory with out having to use change the file names each month.  The files names all look like 2015_05_All.xlsx, 2015_04_All.xlsx, 2015_03_All.xlsx, etc

There is also another Excel file in the source directory but the name is completely different.

Suggestions?

[sunny_talwar · Accepted solution]

What if you try this:

LOAD [Reporting Date],
[IP Address],
[DNS Name],
[NetBIOS Name],
[Short Name],
etc
FROM
[..\01_ExternalData\*_All.xlsx]
(ooxml, embedded labels, table is entry);

[marcus_sommer]

Another possibility to the suggested wildcard-load from Sunny is to use a loop maybe with a filelist-loop which could include further checks like an if-loop, see to this topic: Loops in the Script.

- Marcus

[brf10043]

Once again, Thanks Sunny!  You are becoming my personal SME!