Output to Excel file range that is also a table

Question - I exported data from SMSS through Alteryx (using the output tool) to a range in a sheet in an Excel file. I created a table for only the first 10 rows thinking, that once the data was updated, the table range would expand like it always does when I paste data in. It did not.

Any ideas what I am missing.

Dynamic Processing

Accepted answers

JoeChia

I keep getting messages about the optimal solution to my question. Here is what I learned:

If Alteryx sends data via an output tool to a "table', as defined by Excel, the table will not update in Excel.

As a result, if you have formulas (within the table, or other from other sheets) or a pivot table referring to that table name in that sheet, it will not work properly, as the table range is not extended with the addition of the data, as would normally happen if you manually copied and pasted that data to that range. You need to set up the formula so it includes a range that will include all records and enough for expansion. Not sure if this is an Excel issue or Alteryx issue, but that was my workaround.

I hope this helps.

All comments

gabrielvilella

Hi @JoeChia, are you changing the values for the range dynamicaly like on a formula tool or did you just leave a big range like A1:ZZ9999? Either way should overwrite the existing data though if you have Overwrite on the output data tool.

JoeChia

Thanks for the quick response. Yes it does overwrite.

My output range is A5:P100000.

My excel table range is A5:P10

What my issue is ...why doesnt the table range expand when Alteryx overwrites the data? When I perform a manual copy and paste into that range of the Excel file, the table range automatically expands as designed by Excel. But when the data is output from Alteryx to that range, it does not. Any ideas? I ask, because I have formulas in columns Q through Z, that will automatically go down when the table expands, but will not, if the table range does not expand. This requires me to remember to manually copy down the formulas with each Alteryx update, and on a busy day, could possibly miss that and not copy all the formulas down. Its an extra step I worry will be missed.

gabrielvilella

The output data tool was not designed to update the range automatically based on the data. On this case, you need to update it using the workflow itself. Just keep in mind that if you have a range that is less than the present data on the sheet that will be overwritten, it will only overwrite the range you have set in Alteryx, any extra data outside that range will still remain on the sheet. To avoid this you could either read the original file and get the range to use it or have a large range that you are sure that will always be enough for any dataset.

JoeChia

I keep getting messages about the optimal solution to my question. Here is what I learned:

If Alteryx sends data via an output tool to a "table', as defined by Excel, the table will not update in Excel.

I hope this helps.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167