Can't append data to existing excel as table

[Bushra_Akhtar]

Hi Team,

I have successfully used the Output Data tool to append to append data to a excel spreadsheet using a small data set.  However when I tried to do this for a larger data set and to a excel file with many more tabs which have validation rules built in it does not work.  If I try appending to an existing sheet nothing happens and if I try on a new sheet the data will save to one cell but not as a table.

I am not sure if I need to change anything on the excel file itself in case that is the issue.

Has anyone come across this before or can anyone help?

Many Thanks

B

[TrevorS]

Hello @Bushra_Akhtar 
Thank you for posting to the Community!
Are you receiving any specific error messages from the workflow when the append fails? If so, please share the text for the error message.
If possible, please also share a copy of your workflow and some sample data for the Community to use to troubleshoot.

A few things to check right off the bat:
Is the Excel document shared? If yes, make sure no one is using it when you run the workflow otherwise it will fail.
Does your dataset match the layout of the table you are trying to append to?

I have attached some additional posts for you that have some useful information regarding appending data.

Tool Mastery- Output Data

Interactive Lesson- Writing Data

Can't append data to existing excel sheet

Thanks!
TrevorS

[Bushra_Akhtar]

Hi Trevor,

Thanks for responding.  I have attached the workflow.  It seems pretty easy to do if I don't use the 'Table Tool' but once I use this I can't append to an existing excel table range.  I get the below error message:

Error: Output Data (29): Incoming field named "Table" cannot be appended. All incoming fields must match (or be mapped to) a destination field name.  

I have noticed I lose the ability to 'map' my fields when I use the table tool. 

Many Thanks

B

[TrevorS]

Thanks for the info @Bushra_Akhtar 
With the Table tool, you are building a report table, so your output from this tool is a single datapoint (the table itself.)
I would try to remove the table tool and see if the data is flowing properly into your existing table.

Thanks!
TrevorS

[Bushra_Akhtar]

Hi Trevor,

The reason for using the table tool was to build conditional formatting rules to highlight certain cells different colors.  Unless there is another way to do this for now not using a table is not an option.  If I take the table tool out it does work but it defeats the object of what I was intending to do which is to automate several conditional formatting rules in multiple spreadsheets and tabs.

Would be great to know if there is a workaround.

Many Thanks

B  

[Bushra_Akhtar]

It does look like the only work around I have is a VBA hack in excel to copy and paste back to the original spreadsheet which has the formats.  It doesn't appear Alteryx has the functionality for this use case which is a shame as it does appear to be a common scenario that occurs. 

[TrevorS]

Hello @Bushra_Akhtar 

I apologize for my delayed response. I was checking to see if there was any other way to do this but you are right.

The other option for you would be to make the append within Alteryx instead of the output.
Right before your table tool in your workflow, you can join your existing table data into your new dataset. Perform the conditional formatting with all of the data in the Table tool, and then output using the override existing data.

This should give you all of your existing data, correctly formatted in the output excel table.

If you'd like to see this incorporated into the product, please submit an idea! 

Thanks,
TrevorS

[Bushra_Akhtar]

Hi Trevor,

Thanks for coming back to me with that suggestion.  What would that look like a work flow or which tool do I use before the table tool? If you can provide more detail that would be useful.

Many Thanks

B

[TrevorS]

I hope this helps give you an idea!

[Bushra_Akhtar]

Thanks Trevor,

The underlying spreadsheet has data validation rules built into the cells.  will I lose this information if I do it this way?