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

MWilkesen

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.

THANKS!!!

Michael

Andrée Starå

Hi @MWilkesen

I hope you're well and safe!

Do you need to be able to edit the data after it's been imported, or is it only referenced?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

MWilkesen

Andree, thanks for responding. That was a great question that I didn't think of. We might need to update this data after it is ported into Smartsheet in the future as our processes evolve, but I don't think we would need to edit the data immediately.

Thanks,

Michael

Andrée Starå

@MWilkesen

Happy to help!

You could have Data Shuttle populate a so-called helper sheet and then use cross-sheet formulas to collect the data for the primary sheet, where you would already have added the 3-column combination of unique IDs.

Make sense?

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

MWilkesen

Thanks, Andree, that is an option that I was considering. Maybe I'm more of a genius than I gave myself credit for! LOL

Andrée Starå

@MWilkesen

Excellent! Haha! Yes, it seems like it!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!