Data Shuttle doesn't seem to be importing from an Excel file when it is closed

Matt Molland

I set up a connection with Data Shuttle to import from an Excel file stored in 'Box' cloud server. When I have the Excel file open it seems to be able to import the data correctly. But if I have the Excel file closed it doesn't seem to update any data.

Does anyone know what is causing this issue?

Ryan Kramer

@Matt Molland,

I have had a number of similar issue with data uploader.

Currently, I run a number of python scripts (in place of data uploader) and am able to haven't had any issues yet. You can build a pretty simple script that would pull the file from Box, process it and upload directly into Smartsheet.

Ryan

Danielle Wilson

Hi @Matt Molland if you're still having issues here it would be awesome if you could file a Support Ticket to have it investigated!

Matt Molland

@Danielle Wilson I did find a resolution that is a bit of a workaround. When I create a DataShuttle workflow I have to first have it set it up to 'Replace all target sheet rows with data from the input file', Then after the first run modify the workflow to 'Merge data into the target based on key column value' and only have 'update rows as they are changed in the source file' selected.

This seemed to have solved my issue, but it is just a bit clunky to get a workflow set up.

Danielle Wilson

@Matt Molland Huh - that's so strange! I'll raise it with the product team so they're aware here. Thanks for circling back with me!

PTT

I am having similar issue here. MS SQL server creates an Excel spreadsheet and DataShuttle couldn't read that file. The work around is open the file in Excel and save it on the top of the original file. Then DataShuttle can read it without any issue.

Ryan Kramer

@PTT , that sounds like a pretty terrible manual process where you have to add a human intervention before every data shuttle job.

It might be worth looking into the encoding of the file produced by MS SQL Server and adjusting it accordingly. I usually use CSVs personally and migrated most of my larger data jobs away from Data Shuttle but if I was still using it and had that issue that you are having, that would be my approach.

Change your export value to CSV, check encoding, and try to get Data Shuttle to run directly off that file.

Ryan

PTT

Thank you, Ryan for the reply.

Yes, it is a bad workaround.

I am working with my DBA to send the file in CSV format. I will update you all after I test with the CSV file.

PTT

Update:

The CSV file works fine. Thank you all.