community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

XLS select statement

Asteroid

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_%$`

Alteryx Certified Partner
Alteryx Certified Partner

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

Asteroid

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?

Asteroid

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

Highlighted
Asteroid

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]

 

Alteryx_BM 1.JPG

 

Alteryx_BM 2.JPG

Alteryx Certified Partner
Alteryx Certified Partner

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

Asteroid

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.

 

Alteryx_XLS 1.JPG

Labels