Data Shuttle - Remove row that is no longer is source sheet

I use Data Shuttle to upload current enrollment data for our courses. I have a unique identifier because the source sheet has duplicate entries, but I only need one in entry in Smartsheet.

Has anyone come up with a solution to remove the a row that is in Smartsheet, but no longer in your source sheet?

Sometimes courses are deleted and there isn't a way to do this in Data Shuttle. I need a combination of the replace data option and the merge data option. Any ideas?

Find more posts tagged with

Data Shuttle

Accepted answers

All comments

Danielle Wilson

@Katherine Britt If the records that are no longer in your source sheet have some kind of commonality for them, you could use the "Delete rows that no longer match the filter criteria" option in Data Shuttle. Otherwise there are probably some options that have to do with making sure you're not bringing in duplicate entries so you can use the course ID as the unique ID, which should solve it - though that might involve a staging sheet and DataMesh.

Galadriel975

@Danielle Wilson do you know if it's on the radar to have the option to check a box that says "remove rows that are no longer on the source sheet"? The report that my datashuttle is pulling from has no indicator that could be used to create an automation, since the report doesn't display orders where the delivery or invoice quantities are 0 (which would be the only way we would know). Also, the constant need to create staging sheets for old data, rather than just having an automation to delete rows after a condition is met makes it really hard to manage data. In some cases, I do need data stored for a longer period of time, so moving the data to staging sheets is fine in these instances, but in others, like open order reports, having the option to automatically delete the row as an automation and/or within datashuttle would make life so much easier than constantly having to come up with workarounds.

Danielle Wilson

Hi @Galadriel975 I just ran a quick test on my own with the "Delete rows that no longer match the filter criteria" that looked where the Unique ID field "is not blank" and it removed any rows that were not in the incoming file, so it looks like you should be able to achieve what you're looking for that way!

Galadriel975

https://community.smartsheet.com/discussion/comment/478944#Comment_478944

@Danielle Wilson I tried this last week and at first it seemed to work, but now I'm realizing that it seems to be removing duplicates, which I absolutely do not want. The report we pull from SAP pulls each line from a purchase order separately, so my primary column has several lines with the same value. Do you know any way to fix this?

Danielle Wilson

@Galadriel975 Ah, yes then probably no way to do this without a separate staging sheet and some other workarounds!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.