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

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

Input

Accepted answers

TomWelgemoed

Morning @willbutler1 ,

I've updated the workflow to take 2 files (I copied the original you gave me in a directory). It does need a few changes not only in the dynamic input tool but also in the flow, as you now will get the 2 files stacked on top of each other.

On the dynamic input tool, the key is to change the Input Data Source Template (at top of configuration window) and the Field should be the FullPath (Directory of the input file) and the Action should be "Change Entire Path". These 3 things basically tell Alteryx what the file will look like, which field tells it where it is located on the disk and finally what it should do with the field location (i.e. change every time it reads a new file).

Once that is done the sheet problem should go away. Now you deal with the data - I've attached a sample workflow. I don't know how your real data will look, so it's hard to filter out unnecessary columns. I've simply now specified which fields are needed in the filter (Vendor Name & Quotation date) and made those fields to be joined to the main dataset. But this should give you a good foundation from which to work.

Best,

Tom

Example 2.png

Example workflow v2.yxzp

All comments

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

Sample .xlsx

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

Example workflow.yxzp

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.

Example workflow.yxmd

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?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335