Updating Excel data regularly to existing data files

[serhoshu1118]

I am trying to build a product mix model on a weekly basis - adding in each week's new data from Excel to the already existing ones from the previous weeks. I want to see the historical trends and comparisons, instead of just that specific week's data (which means I don't want to overwrite the existing data). Is there a way to do it on Alteryx? 

Currently, I have already built a workflow of simply importing one week's worth of data and generate a chart out of it. I want to be able to make that dynamic and build onto the previous weeks' data and also able to add future ones to it. 

[nick_ceneviva · Accepted solution]

You are able to configure the "Output Data" tool under the Output Options to be "Append to Existing Sheet".  This will just add the new data to the existing file every time the workflow is ran.  

For reporting purposes, you will want to make sure that you have some sort of date field that will allow you to distinguish which week the data was loaded.

Hopefully that helps!

[serhoshu1118]

Hi Nick, thanks for the solution. I tried doing it and was wondering what are the specific settings for "append to existing" to work. Can I just export it to any existing .xls Excel file that I have or do I have to match certain fields? I got the error "Microsoft Jet Database Engine: External table is not in the expected format."

[nick_ceneviva · Accepted solution]

When using the append to existing option, you are able to control the field mappings using the "Append Field Map" setting.  the default is to just append based on field position, so you will want to make sure your file matches the Alteryx output.  The other alternative would be to do a custom mapping and then you can map specific fields from the Alteryx output to columns of the Excel sheet.

I think the issue with Jet Engine database is due to versions of Excel.  The Microsoft ACE engine should be used when working with Excel 2007 or later files (.xlsx as opposed to .xls), while the Jet Engine is used for earlier Excel formats. 

[serhoshu1118]

Thanks so much for the solution! I have a follow-up question. When I append the data to the existing Excel sheet, is there a way I could append one column only? For example, in the attached screenshot, I want the values of all the different platforms to just be appended horizontally. The numbers should be under the "May_90" column instead of being appended separately below, without repeating the names of the platforms. 

[nick_ceneviva]

hmmmm great question.  Nothing comes to me right away where you could just add the new column. 

One option that would produce the same end result, would be to have the Excel file as an input source, Join the new data with the existing data as a new column and then instead of append to existing, you would update the source to be the Overwrite Sheet or Overwrite File option.  You will probably also need to use the block until done tool to make sure all of the data is read from the sheet before it starts entering the data back into that file.

Make sense?

[serhoshu1118]

Hm I tried it out and it worked - but the Excel table is actually linked to a chart, so the goal is to update the chart dynamically as more data is added periodically. If I overwrite the sheet, the chart would be gone. 

Thanks a lot, though! Please do let me know if Alteryx offers any solutions to this. 

[AnkitO]

Hi Nick,

I've been able to append data into an existing tab of an excel file using the methodology you provided.  However, when the data gets populated into the tab, the columns of data all lineup but there are a random amount of blank rows that appear between the existing data and the new data populated.  Any idea why that may happen?

Is there a way to get the new data populated in the very next row so that there are no blank rows?

Thanks!

Ankit

[ashissanpui]

Use filter for blank case. Pass only nonblank cases before the output file. I hope this would suffice your requirement.