Data Shuttle - I just want to append to an existing sheet.

James Keuning

I have a CSV with one column and one record: Column is called Project. One record says Test Project.

I want to add one record with that one field to my existing sheet. I have mapped my CSV Project field to the destination Project field. Can Data Shuttle do this?

I am feeling a bit floored right now. Because I have no key column. Because this is a new record.

Mike TV

@James Keuning

I could be wrong but I believe your "key column value" is going to be the one single column you have on the source sheet. Just make sure when you are on the Mapping area that your Unique Identifier Column is the one single column you're mapping over. I'm pretty sure you'll be fine.

Paul H

@James Keuning In order to use the merge function you must have a unique identifier column in both the source and target.

Example Logic, Data shuttle looks for row "P-0100" in the Project number column, if it exists it updates the cells from row "P-0100" in the source, if no "P-0100" is found it adds a new row.

James Keuning

To add: I am thrown by the "key column" concept. Because my source has no key field.

Mike TV

@James Keuning

I could be wrong but I believe your "key column value" is going to be the one single column you have on the source sheet. Just make sure when you are on the Mapping area that your Unique Identifier Column is the one single column you're mapping over. I'm pretty sure you'll be fine.

James Keuning

@Matt C. It looks like the key column it is asking for is in the destination. Which makes no sense to me since it there is no requirement that my source have any sort of key.

I assume key means unique... but that assumption is not based on anything. I assume the key is used to update matches or append non-matches. I am so confused why I cannot find any documentation about the requirements of this key field.

Paul H

@James Keuning In order to use the merge function you must have a unique identifier column in both the source and target.

Example Logic, Data shuttle looks for row "P-0100" in the Project number column, if it exists it updates the cells from row "P-0100" in the source, if no "P-0100" is found it adds a new row.

James Keuning

@Paul H Thank you for that. I JUST figured out after hours of testing. Here is the problem, my unique identifier in my sheets is a formula field. And so far, I have not been able to successful using these two fields. Am I right that I can't use formula fields as the key?

Because if not, do people really manually create unique values? Is there a workflow I can use in my sheets to update a field with my formula field so that I can key off of this field?

James Keuning

OK, so this is me responding to my own comment, because I think I am figuring this out. With my data and this workflow, I am only ever going to be appending data. So I do not need a key to match or exclude duplicates. So I can tell Smartsheet that the key field is just a random empty field in the target, call it something like Shuttle Catcher. And my source data, which has a formula field which is unique (it's a combination of Project and Event, and I make sure that it's always unique for a number of unrelated reasons, basically use conditional formatting to alert us if we use the same task name more than once in a project, and we just rename the task). I was hung up on this field, concerned that the fact that it is a formula is messing things up, but I just realized that once this field is exported from the source, it's just a field in a CSV, Smartsheet has no idea that it originated from a formula. And I can dump this field into my Shuttle Catcher field, which is really just a workaround and is not meaningful to me. I am too exhausted to try to figure out anything else tonight.