Best Practice: Importing and Replacing Large Amounts of Data on a Weekly Cadence

MWilkesen ✭✭✭
edited 04/04/24 in Smartsheet Basics

Hello, fellow Smarthseet Gurus! I need some best practice advice.

On a weekly cadence, I have a vendor that supplies me with a "data dump" status report containing more than 2,500 line items. I need to get this report uploaded, on the same cadence, into Smartsheet with the least amount of work possible, preferably as automation, to reduce human error & dependency on a person(s) to complete the task. I can set up a Data Shuttle to import and update rows based on a RowID cross reference; however, the RowID is not unique to the data report provided by my vendor; rather, I had to write a formula that joined three (3) cells of data to create the unique RowID. It is not ideal to build a formula each time a report is received (people get lazy, complete incorrectly, etc.).

Given the size of the data report, I need to find a way to import and update the data correctly into Smartsheet without having to "copy & paste" data from the original Excel sheet to another Excel sheet which includes the RowID formula and is further connected to Smartsheet via Data Shuttle. I am very experienced in Smartsheet but inexperienced in using DataShuttle to its fullest potential. The goal is to make this as hands-off as possible. I should note that my organization will not subscribe to the Data Table app.

Below is a screenshot of the receiving sheet in Smartsheet for visual reference. All unhighlighted data can change/update each time the report is provided, but the highlighted columns will never change.

Any advice or clarifying questions are welcomed. I am also amenable to initiating a Zoom call if anyone would like to "live" assist.