Import with Data Shuttle but keep some columns

Trevor Smit

I import an excel attachment into a sheet on a monthly basis using Data Shuttle. The report contains data for the last 13 months, example May 2021 to May 2022. The next month when I import the report again, it will contain June 2021 to June 2022 and again the month thereafter will be July 2021 to July 2022.

I would like to keep the historical months in the sheet without overwriting them, so when I import June 2021 to June 2022 I need to keep the May 2021 column data in the sheet. Also then when I import July 2021 to July 2022, I need to keep the May 2021 and June 2021 columns, etc.

Is there a way to achieve this?

Mark Safran

Hey @Trevor Smit ,

When you're setting up your workflow, you'll want to choose the "Merge" option as the workflow action, rather than the "Replace". This is found on the Target tab of the workflow editor:

This will preserve the data you originally uploaded, while adding in the new months' data as you attached new excels.

-MCS

Trevor Smit

Thank you for the quick response, I will give it a go!

Trevor Smit

Hi Mark

My months are currently in columns, which then requires me to map the columns. For this solution I might have to transpose the data so that the months are in rows, or is there another way?

Thank you

Trevor

Mark Safran

Hey @Trevor Smit

Transposing your source data to months as rows would definitely work (and likely make this easier long term). If your source data is tough to manipulate though, you can try something like this:

• Give the destination sheet columns generic names (Month 1, Month 2.... Month 13), and leverage a "locked" Row 1 that has their actual month/year name (i.e. "June-21", July-21"..."June-22")
• Only edit the column headers in your source excel sheet to be something similarly generic (so that they have the same labels every time).
• Build your DataShuttle workflow to map these re-named (but now constant) source headers to your generic "Month 1", "Month 2".... "Month 13" destination columns. This would be a "Replace" action, not the "Merge" as suggested earlier.
• Before running your workflow each month, copy over the "Month 1" values (what would now be 14 months old) over to a separate "Archive" sheet. The column names on here can be as specific as you want, as it will just be the archive/storage location for any data that's 14+ months old.

I have something very similar set up for a 13-week shipping projections tracker. It was too difficult to manipulate the source excel every month, so I traded that for a quick data copy of one of my columns to another sheet, and then a quick header re-label on my source excel.

Hopefully that made some sense. If not and you think it's easier to jump on a quick call just shoot me a DM and we can sync up.

-MCS

Trevor Smit

Hi Mark. I currently have my headers and 1st row setup in the way that you described. I will play with the introduction of the archive sheet, it sounds like the best approach. There are 21 excel reports that I pull into SmartSheet for this project, but the positive is that the updates are only done once a month.

Thank you for the help!