EXCEL SFTP Connector Keep All Columns (Append)

Justinp
Justinp Member
in Connectors

Hi All,

I have been having a problem recently with different vendors' data pushes that the data connects via Excel SFTP. What occurs is that either the vendor changes the column name, so the past data drops the column with the name change, and all that data gets lost.

What also occurs is that the workflow the vendor has doesn't always generate the columns, so in this instance, when the new upload happens, the existing data drops the columns of the data that existed.

I know when it comes to email connectors, this wouldn't happen since new columns end up being created when it is the first time it appears in the dataset, and was wondering if there is a way to setup this up with the SFTP connectors.

The vendors are working on fixes, but I want to have a plan in case this ever occurs again, since adding an IFNULL formula is quicker than having to retrieve the data back. Thanks!

Tagged:

Answers

  • ArborRose
    ArborRose Coach

    I'd be having some un-friendly conversations with the vendor about consistency.

    You might be able to do some kind of staging dataset. Something you can push data to and then process with a Magic ETL or SQL to join new data with previous data on specific fields. And apply logic to identify changes, etc.

    I handle SFTP using Domo's Jupyter Workspace and Python. But I'm normally just receiving a file and pushing it to Domo or out to a network location. But my experience using Python has been highly productive, fast, and we can maintain control.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories