Uploading an Excel with Data Shuttle Containing Duplicate Rows

cko

I have a Data Upload workflow via Data Shuttle that runs on attachment for the most recent file. The excel files that stakeholders are uploading usually only have ~10 rows and when it goes through the workflow, the rows self-populate into the correct positions within a Smartsheet master database. The problem I have encountered is that sometimes the data being uploaded to Data Shuttle could contain a duplicate row that already exists within the Smartsheet. I want duplicates to be added to the bottom of the sheet like the rest of the new data already is, regardless of whether it is a duplicate or not. When there is a duplicate, Smartsheet doesn’t import that row into the sheet. Please let me know if this makes sense and any suggestions if you have them would be greatly appreciated.

Kleerfyre

Each row will have to have a unique identifier for it to work correctly. Is it possible for you to convert this process into your users using a form so that it will always add an new entry into your sheet?

cko

https://community.smartsheet.com/discussion/comment/379141#Comment_379141

As of right now, our stakeholders use a form to upload the data into the sheet for Data Shuttle to do its thing. We are still in the preliminary phases of planning out this project but given the frequency of data being uploaded and the need for automation, we are really trying to stick to a model where an excel is uploaded (rows being auto populated into the Smartsheet database regardless if they are duplicates or not) and the rest taken care of. Someone from in my company suggested to look into if there is a way to not designate a unique record column but this doesn't seem to be the case. The only other thing I could think of would be to create a new Smartsheet sheet for every data upload but I don't think this is even feasible given the fact that many other sheets are linked to this main sheet via formulas.

Kleerfyre

Why not just cut out the Data Shuttle part? You could use the form for the stakeholders to fill out instead of them typing it into an excel then clicking on a form to upload the excel to the sheet to have data shuttle grab it and then put it on the sheet.

cko

https://community.smartsheet.com/discussion/comment/379149#Comment_379149

I wish I could cut Data Shuttle out of the picture. Unfortunately Data Shuttle has to be used because there is a separate database kept in another system used by a different company that generates an excel file for us to upload into our system.

Lesa Weaver

HELLO @cko,

I had an issue with duplicate unique identifiers that I solved by having a column in my Excel spreadsheet that concatenates two columns to form a unique identifier column that is better for my Smartsheet. You could use a formula combining your current unique identifier + today so that it would always be unique within that day when it is uploaded. If you have duplicates within one day, you could add time.

In Smartsheet, I don't need the unique identifier primary column anymore after upload because I have the data in separate columns already, so I just hide that column.

Hope this helps.