How to retrieve the filename into a column using a File Iterator with the Excel Query

Matillion ETL
1

I have a GCP Storage bucket with a bunch of files in the following format.

Open Sales Orders DP (F4211) 10 June 2022 07_16_14

 

I want the file iterator to run through all the files (which works) but I need to retrieve the date from the end of the filename. I added a variable ${filename} and set the default as the above, but when i add that to the data selection box for the excel source, it just says "Unrecognised options:" Open Sales Orders DP (F4211) 10 June 2022 07_16_14

 

I don't understand how this is meant to work, and the instructions aren't clear enough.

 

Thanks for any tips or guidance that might help me move forward.

2

Hi

 

I know space not an issue with your case, Can you try change the mode to advanced and try to write a select statement?

 

or

 

Put quotes in your filename parameter in storage url properties

 

If you fixed already please share the solution.

3

Answering my own question. The key is to use a file iterator component with an orchestration job, then within that component do the Excel Query step, and whatever else you need to do, with the filename which will be held in the variable.