SFTP Connection- Edit CSV Documents

Sean Pflueger

I have an idea to create a connection from my SFTP to Power Automate back to SFTP

• Pull a nightly CSV from a folder on my SFTP
• Edit CSV document
  • Delete Lines not if column D is blank
  • Remove Leading zeros from column A
• Post back to different folder on my SFTP

I initially was trying to get the file emailed and then pulled on my my One Drive because that was easier to make a connection. Unfortunately, the email contains a link, not an actual attachment. Now, I'm trying to get Power Automate to pull from an STFP, but when I enter my credentials it gives me an error.

Does this seem reasonable or am I barking up the wrong tree? Totally open to suggestions.

Alex Wong

@Sean Pflueger
I'm not sure what you are trying to achieve as it doesn't quite make sense to me.

If you are using RE:Queue for a scheduled export of data from database view query, then “Delete Lines not if column D is blank” is just a criteria on the field in the query. While you can't remove leading zero from column A using query, that can be done in power auomate.

what also doesn't make sense is, why are you saving the file you modified in power automate back to the sftp location?

Sean Pflueger

@Alex Wong

I'm receiving a file from a vendor with information. They are posting it to an SFTP.

I want to make changes to the file before I load it. Another program (Omatic) is going to take the file from the SFTP and load it.

Alex Wong

@Sean Pflueger
I see, if you are using Omatic, then you shouldn't need to transform the file.

Import O matic is capable of skipping rows based on a column being blank, as well as there are regex that can remove leading zero. You don't need transformation of the data.

Meaning, all you need is to automate the sftp to get the file, and sftp to put the file in blackbaud file folder

Sean Pflueger

@Alex Wong
In the database view you can skip rows and remove leading zero. In the webview, you cannot.

Alex Wong

@Sean Pflueger
my misunderstanding then, when you say Omatic will use the SFTP file, are you talking about Omatic Cloud or Import O Matic?

Sean Pflueger

@Alex Wong
Omatic Cloud

Alex Wong

@Sean Pflueger
I see, we use IOM, and have not move to use Omatic Cloud, though they have had their sales pitch to us before so we determine that it doesn't do what we needed which IOM (with custom code) can do.

but regardless, the point here is not about the manipulation of the csv file as you have no issue doing. So going back to your original question, are you having trouble establishing SFTP connection to the vendor or SFTP to Blackbaud file folder?