Importing data taking information from the header and adding to each row of data

[willbutler1]

I have a bid template (attached), and I want to import multiple files and append the supplier name and quotation date to each row of the data when I import into the master file.

Schema will be the same across all files, but filenames will not be.

First time post on here so be nice! Help very much appreciated.

Thanks.

Will

[TomWelgemoed]

Hi @willbutler1 ,

Welcome! 

Could you physically attach the file? You've shown an image, but would be easier/best to work with the file you've got.

You can attach it at the bottom of your post window.

Regards,

Tom

[willbutler1]

Hi Tom, 

Thought I had attached the file - but here it is

[willbutler1]

I thought I had attached the file in the original post. Here it is (hopefully) now

[TomWelgemoed]

Hi @willbutler1 ,

The attached workflow should do it. I've assumed you will have multiple sheets with the exact same structure, and then filtered out the supplier name & quote date from the header (and reattached it). Let me know if that works for you? This should work if you add more sheets.

Regards,

Tom

[willbutler1]

Hi Tom,

Thanks for the quick response - much appreciated!

I am unable to open the sample workflow you sent as it says I'm using an older version of Alteryx. Is there any way around that (other than a new Alteryx install)?

Thanks!

Will

[TomWelgemoed]

There is a bit of a hack, yes. 

If you right click an Alteryx workflow and open it with a Text editor (e.g. Notepad ++) you should see the XML. At the top of the workflow there is the version number. Now, go have a look at one of your workflows and see what number is shown there (e.g. 2019.1). Copy that version number over the version number in my workflow (the one I attach) and save the file. You should then be able to open it in your version.

That should work. Let me know if it doesn't.

[willbutler1]

Ah ok thank you - good to know.

In the end I downloaded the latest version of Alteryx (why not?)

I can see the logic to the workflow, very simple in the end as these things often are once you know how!

Just one tweak though - I am going to have lots of separate excel files, rather than one file with lots of sheets/tabs, so would I need to look to the directory tool here? All files will have different filenames, but the Name of the sheet and the schema will be the same. 

The end result is just as you describe - one big table with all the information and a supplier and quotation date field appended.

Thanks again.

[TomWelgemoed]

Haha, why not indeed.

And oh, my mistake, maybe didn't read your post carefully enough (on the multiple sheets point). But indeed, use the directory and just import  *.xlsx (or whatever format) and read them through the dynamic input tool. 

And you're right: almost everything in life is easy once you know how! 🙂 Alteryx does make it very easy though to do more complex operations.

If you're happy with the solution, do you mind to mark it as solved?

Best,

Tom

[willbutler1]

Hi Tom,

Now struggling with the directory tool!

So i can get it to pull in the filenames of the bid sheets in the directory folder, but then dynamic input doesn't like it because there isn't a sheet in the Data Source Template with the same name as the file name.

How can I get around this as I'll have lots of files with different names (but the quotation data will be on a tab with the same name)

Should I open a new thread for this discussion?