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.

[Kanderson]

One simple way would be to read from your target file and perform a join to your existing workflow. The records that do not meet a join criteria (would be presented in the left our right outputs of the join tool) would be your new records. You could then use the append option and achieve the desired result.

[mattlukoff]

Thanks, but then it's a matter of manually updating the target file with
the new records each time right?

[SeanAdams]

Per @Kanderson's suggestion- bring in all the records from Excel, change the ones that need changing, and then write them all out to excel again with an overwrite. There is no way that I know of to do an in-place add or update with Excel because excel doesn't have the concept of a primary key.

[SeanAdams]

@Joe_Mako is exactly right - if you just want to append new rows at the bottom then this will work perfectly. If you also need to deal with updates (or deletions) then you have to rewrite the full set.


If you still need more guidance - feel free to mock this up like a weekly challenge (see https://community.alteryx.com/t5/Weekly-Challenge/Weekly-Challenge-Index-amp-Welcome/td-p/48275) and include some sample excel sheets and your desired outcome, and we can work together as a community to get you to a solution.

Cheers
Sean

[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

[Joe_Mako]

If you only want to Append new records, and not update existing records, there is an option called "Append To Existing Sheet" with using the Microsoft Excel (*.xlsx) file format.

Is this not an option for you, or is it not behaving as expected?

[mattlukoff]

@Joe_Mako,

Thanks but it's not working as expected because the Append is adding ALL records, not just the new ones.

[mattlukoff]

Hi @ Joe_Mako, 

It's not working as expected because Append is adding all records to my mapping file, not just the new ones.

[Joe_Mako]

One option is you can perform a Join just prior to output with your existing file, that way it outputs just the new records.