How update excel file with updates.

Question

Hi All,

I am still relatively new to alteryx but I wonder if anyone can help me out with the task I want to develop in designer.

I have got 'master file' that is excel file and I get updates to that file. The 'master' has 10,000 rows and 80 columns. There is a ID column that I use as Primary Key. The update file might have some rows changed but not all, it might contain unknown number of rows.

My idea was to use ID column in update file and based on that update that ID row in Master only if any update was done?

I hope it does make sense 🙂

atcodedog05 · Answer

Hi @tomekgagola

Here is an example.

Workflow:

1. Read in both main data and update data.

2. Use union tool to merge them together. Make sure to set the output order in union tool as #1 (old data) and #2 (new data).

3. Using sample tool groupby on Primary key (here its CustomerID) keep last row. This way old data is replaced with new data wherever applicable.

Hope this helps : )

update data.yxmd

tomekgagola · Answer

Would you be able to expand on Point 2?

atcodedog05 · Answer

Hi @tomekgagola

The concept you are speaking about is only available for SQL database output. Where you can update only specific rows based on primary key.

For excel if you want to update specific rows only way you can do it is

1. Read all the data.

2. Update the required data in data manipulation stage.

3. Overwrite back all the data into the file.

Hope this helps : )