Hi,
I have a GCP Storage Bucket folder full of Excel files (all in the same format) that I need to iterate through, load and then archive on success.
What is my best way to handle this? I am struggling with the file iterator combined with the Excel Query component... (the instructions around how to use the variables for filenames and folders aren't clear at all). I can set the Excel Query component up manually to load one file, but thats not very useful.
Thanks for any advice.
Rob
Assuming you can get the file iterator working, the folder and filenames should be passed into a job or environment variable. Then you pass these into the excel component as the URL.
I haven't used GCP but we have something similar on AWS.
In that example ${SOURCE_TABLE} is a job variable for the sheet name within the Excel spreadsheet. You might use something similar for the actual file names instead of the sheet names.
Just right-click the design surface and select the option to add Job Variables:
I'm guessing the file iterator is setup something like this:
Each filename in the bucket will get stored in the fullpath_filename job variable that I’ve created for iteration of the file iterator. Then pass that fullpath_filename into the Excel component as the URL…
Thanks for taking the time to reply, I'm still stuck though! Its frustarting because it seems really simple.
I hope the attached screenshot shows my issue. I've tested the file iterator with a python script so I can see how the variables are populated. Basefolder is just the actual folder the files are in. Filename is just the name of the file (no path). So I assume that what I should be putting into the Storage URL in the Excel component is the full path, and then {env_filename} at the end - however when I do this the variable just gets removed, and then it says its invalid....
Hey mate,
I think you should reference variables like this ${variable_name}... Looks like the $ sign is missing from your screenshots. :)
You look like you're on the right track though.
Steve
