Data Shuttle: Target sheet is empty but all rows are "unaffected" after running workflow

Hello,

I am new to Data Shuttle and creating my first Upload Data workflow. My target sheet does not have any data in it: everything is blank (it is a new sheet). When I run the workflow however, the run details say that there are 77 rows unaffected (as in there are 77 rows where data already exists and is consistent with the source). There are supposed to be 69 rows, plus Smartsheet had been counting 8 blank rows at the bottom for some reason. No rows were added or updated.

Why is this happening? Does there need to be data already in the target sheet for the workflow to add anything? Why does it say there are 77 rows unaffected when the target sheet doesn't have any data?

Find more posts tagged with

Data Shuttle

Accepted answers

All comments

Kerry St. Thomas

This is largely conjecture on my part, and I haven't found any documentation from Smartsheet about this. But… it's based on me seeing a similar thing happen with my own stuff. If you're trying to set up a Data Shuttle using the "Merge data onto the target based on a key column value"… <blank> value in the key column could be causing the issue. Blank exists both in Smartsheet and in your source sheet… so apparently you confused Data Shuttle and broke the internet. 😀

The solution that I've used is to set up the Data Shuttle to "Replace all Target sheet rows with data from the input file" and then run it. Then reconfigure the Data Shuttle to "Merge…" now that your key column contains actual values. To test it, delete some (BUT NOT ALL) of the rows from your target sheet, delete all the blank rows in your source sheet (even though there's probably NOT anything in those rows this is a "just in case"), and then rerun your workflow.

Good luck!

Janae G.

Thanks for your help! I'll keep that in mind when I have blank rows or any issues regarding them. Changing it to Replace worked, however after deleting a few rows and the blank rows and changing it back to add and update, none of the removed rows were added. I am wondering if it has to do with my key column? I don't have a true primary column in my source data, so I tried creating one by creating an expression in the workflow to create a row ID column (that way it saves the step of creating a row ID in the source file, which is in Excel). It is basically a concatenate/join function that creates a compound primary key from three columns.

Will the data shuttle recognize the created expression as the unique identifier? Or does Smartsheet not let you use an expression as the unique identifier for adding/updating rows?

Humashankar

Great Insight

Janae G.

Just wanted to put an update on here: I believe Data Shuttles will not work if you try to use an expression for the unique identifier. I attempted to do that in a different data shuttle I was working on and it didn't upload anything into the sheet until I changed the unique identifier to a real field in the source.

So I believe that is why my original data shuttle here had a problem. The underlying issue here then is that as far as I know, Smartsheet does not have a way to use composite keys in the source as unique identifiers in Data Shuttle. So unless there is a method I am not aware of, that is a feature update I would like to see from Smartsheet!

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.