Evergreen Excel Dataset

KLEITH21

Hello,

I receive monthly data and am looking for the best solution to keeping a continuous and evergreen EXCEL workbook. The table would look something like this, with a new column of data to the right of the UID each new month the workflow is run:

UID	Jan Data
1	x
2	x

UID	Jan Data	Feb Data
1	x	x
2	x	x

UID	Jan Data	Feb Data	Mar Data
1	x	x	x
2	x	x	x

...and so on...

I can only seem to replace and NOT preserve the Jan Data column when I attempt to Join Feb data. Any suggestions?

danilang

Hi @KLEITH21 

Here's a dynamic method that will allow you run the workflow monthly without any changes to the workflow(after the initial run).

The workflow reads the data from the latest excel file((bottom input) and uses a dynamic rename to change the name of the new data column to the file name which I've set to the month name.  This data is joined to the data in the existing evergreen file to add a new column in the last position.

The first time you run this, disable the container called "Enable on subsequent runs" and enable the the container called "Enable on first run only".   Run this with your first file to create the initial Evergreen file.  Once you've done this, disable the "Enable on first run" container and enable the other container.   From now on, run this as an analytic app and use the file browse tool to select the next monthly file.  The data column from new file will be added to end of the columns with the new month name. 

Here's a sample of running this for the first 3 months.  

Month 1(initial run)

Month 2 (as analytic app)

Month 3

I use the file name as the column header for simplicity.  You can either rename the monthly files before you run the workflow or modify the column logic to work with the data that you have. 

Attached is the workflow and the 3 sample files that I'm using

Dan

Jan.xlsx

Feb.xlsx

Mar.xlsx

Evergreen.yxwz