XLS select statement

[mb1824]

I have .xls files, each with 1 tab, where the table name is in the format CustomerReport_ddMMyyyyhhmmss

Is it possible to add a wildcard to the SELECT statement like the below (the below doesn't work)so I can input multiple tables without specifying every table name:

SELECT * FROM `CustomerReport_%$`

[BenMoss]

Is the sheet name always the same?

If so it is simple, we simply need to build in the wildcard part into the connection string piece within the input tool...

So...

C:\Users\benjnmoss\Desktop\CustomerReport_22022018.xls

which brings through just one file, would become

C:\Users\benjnmoss\Desktop\CustomerReport_*.xls

This will then bring through all files with that prefix with the specified sheet of that first file.

Does this make sense?

Ben

[mb1824]

Hi @BenMoss

the file name and the sheet name both vary depending on when it was exported from the source system...

So I have:

• ...\CustomerReport_25012018120352.xls (`CustomerReport_25012018120352$`)
• ...\CustomerReport_27022018130050.xls (`CustomerReport_27022018130050$`)

Your method:

• ...\CustomerReport_*.xls

Takes care of the varying file name, but what about the varying sheet name?

[mb1824]

I could manually rename the sheets 'Sheet1' or something generic but trying to avoid this extra step

[mb1824 · Accepted solution]

I found a solution, but it relies on the Filename containing the sheet name, which is fine for my use case. 

• I modified the Batch macro idea from this post The Ultimate Input Data to include a Formula before the Batch Macro that creates [Table] as the table name and then configured the Macro to choose [Table] as the control parameter. See below screenshot
• I also modified the Action within the Macro to "Replace a specific string" - see second screenshot. 

So the Macro will replace the specific string "CustomerReport_27022018130050" with whatever appears in [Table]

[BenMoss]

This is what I was going to suggest, though I would have perhaps used the dynamic input rather than the batch macro. Essentially they are doing the same thing mind!

Ben

[mb1824]

I tried to use the Dynamic Input but couldn't get it to work so switched to the batch macro

I retried Dynamic Input this morning and now have it working...not sure what I was doing wrong yesterday.