Output to Excel without Over-Writing or Appending

[mattlukoff]

Hello, I'm using Alteryx to generate a mapping file in Excel which will be updated every time I re-run my workflow. I only see options to drop (delete) the Excel Table or Append it however I only want to append new records. What's the simplest way to do this with the least manual intervention in my workflow? Thanks.

[SeanAdams]

Hey @mattlukoff,
It may be easier if you were able to provide some sample files - even mocked up data. I'd be happy to pop this into an Alteryx flow for you tonight when I get home.

Key things that will make this easier:

- Sample of how you need the files segmented (do you break them up by field A into different files for example or all in 1 excel)

- Sample of the data you're working with (both the pre-alteryx state, and the desired post-alteryx state with the input you're working with)

o It's good to specify how you want to deal with:

§ Completely new records. What tells you that they are new - are you using a name field that is distinct?

§ Updated records. How would you want to deal with records that are updated? For example - Sean currently maps to user AAA but tomorrow we create a new account and Sean has to map to account BBB

§ Deleted records. Do you need to check your mapping table for deletions?

Based on this, I'm happy to mockup an alteryx flow for you tonight and post it back here - hopefully that can get you to a solution that solves your problem

Cheers
SEan

[mattlukoff]

@SeanAdams and @Joe_Mako thank you for staying with me on this. Seems simple but it's not clicking. See example workflow attached with comments (Scroll to the right for column 3) about what I'm trying to achieve. 

Thanks!

[SeanAdams]

Thanks for passing this on - I'll take a look tonight when I get home

[SeanAdams]

Hey @mattlukoff - I've attached the macro to this, and explained how it works below- I think that this does what you need, but please do check and verify.

• I took the output from your mapping set (in step 2)
• Then read the contents of Mapping.xlsx (which is your current mapping file)
  • Note: just to test this, I replaced Mapping.xlsx with Mapping_Before.xlsx which has the farm column, and where there's a fruit that I know is not in your mapping set from step 2 (Starfruit, at farm ZZ)
• Then joined it to the incoming stream from step 2.
  
  • The way that you do this join is key.
  • For the rows that match, take the fruit name from the mapping.xlsx; but the farm name from the incoming stream - only these two columns
  • That way, on your L leg (unjoined rows that were in your original file, like the Starfruit) you'll have 2 columns namely fruit and farm, but from the original data
  • On the J leg, you'll have the fruit name; but the farm name from the new mappings
  • When you union them together, you have 1 data set with the updated farms where there was an update available, and the original where there was no update or new
• then just pop this out to excel
  • Note; just to test this, I set the output to "Mapping_after.xlsx", but you can change this to "Mapping.xlsx" and it will overwrite your old mappings with this newly refreshed set.

Hopefully this solves your problem - if so, would you mind marking as solved?   If not, feel free to come back on this thread and we'll continue to iterate until we get you to your solution.

Cheers

Sean

[SeanAdams]

only thought of this afterwards.   I've attached the same macro but with the version set to 9.5 in-case you're not yet on 11.   No change in the solution, but just set the version tag to save you the pain.

[mattlukoff]

Thanks @SeanAdams. I get where you're going with this but the issue I'm having is that any mapping file is always the result of the data input. So if there are new entries in my data I'm stuck with creating multiple joins off of existing mapping files to new mapping files. My goal is simply to Append the new records to one Mapping File in Excel. The current Append function Appends ALL data again which is what I'm trying to solve around. 

[SeanAdams]

Hey @mattlukoff

I must apologise - I'm not quite following what you mean - are you able to extend your example to include the problem you're having that's causing you to have to do multiple joins?

keen to see if we can get you to a solution that meets your needs - the closer to the actual case you can make the example, the closer we can get to a solution.

Cheers @mattlukoff

Sean

[mattlukoff]

@SeanAdams,

Thanks again for staying on this! The problem was as posted in my workbook. Desired solution is to have just one Mapping file in Excel which gets updated with *only* the new records from my data. Doing an Append puts *all* records into the Excel. I also don't want to loose the info I add manually in the Excel which is why a Drop won't work.

[patrick_digan]

EDIT: Updated Files

@mattlukoff Try the attached workflow. Make sure to save the macro to the same folder before you run the workflow. Here are the macro inputs:

The file browse is where you point to your mapping file. It currently will only support xlsx files.

The list box is where you check which fields from your incoming data stream need to join to your mapping file. It should support more than 1 field, but I haven't tested that :)

As for the Macro, it's a little tricky trying to set it up dynamically:

It basically takes your incoming Alteryx stream (Macro Input) and Joins it to your mapping file in the join. It takes whatever isn't already in the mapping file (comes out the L join) and groups it by your key fields. Then it appends just those new fields to your mapping file.

Hope this points you in the right direction!

[mattlukoff]

@patrick_digan, thanks for the attempt. I do think this can be accomplished through a macro but I don't have enough skill building them yet. I used the one you provided and changed every file path for my local machine, the macro runs without error but the mapping file doesn't get updated. I'm assuming the one you tested worked fine?