Automate email to FTP file transfer

June 27, 2016 9:26 AM

I have a vendor that would like me to place a daily report on an FTP server (either mine or theirs, preferably theirs). The only way I can do an automated export of this report is a CSV attachment via email. Is there a way to automate an email-to-FTP file transfer?

Unfortunately both sides are pretty rigid here: the vendor insists that the file must be left on an FTP server (either mine or theirs, preferably theirs); and the only way to automatically export the report from my system is via CSV attachment in an email. I'm thinking of something like a script or a macro (or an OS X Automation), but I'm not sure where to begin.

I would be able to use either Mac or PC to handle this transaction. I would also be able to use any type of email client and FTP client on a somewhat dedicated machine. I could also create a dedicated email address, either using our company's Microsoft 365 account, or a free service like Gmail.

I'm thinking of something like this: report is exported to dedicated email client; script or macro recognizes email, grabs CSV file, then transfers to FTP server via FileZilla (or Fetch or some similar FTP client).

Difficulty level: I have pretty much no chops as a developer, with no IT department and no budget.

Bonus points: if something like this is doable, would it be worthwhile to post it to Mefi Jobs? I could probably get my vendor to cough up a few bucks to help make it happen.

posted by slogger to Computers & Internet (15 answers total)

What about a service like Workflow?
posted by bleep at 9:33 AM on June 27, 2016

Best answer: I don't think you can do this directly with IFTTT, but it looks like you can do it in 2 steps via IFTTT & Wappwolf:

Set up an IFTTT to upload the file from gmail to dropbox or Google Drive
Set Wappwolf to get it from dropbox or Google Drive to the FTP

That should be pretty straightforward, without needing someone more technical to help.
posted by brainmouse at 9:33 AM on June 27, 2016

I don't have a specific solution in mind but it seems unlikely to me that you really want to use any 21st-century technology to do this, lest you create an overly-complicated process with unnecessary failure points. FTP, email handling, and task scheduling are all effectively ancient, rock-solid technologies at this point so there's no need to jury-rig anything. I would expect the optimal solution to look like a one-line command calling an email client that is run regularly by cron on the FTP server, no programming needed.
posted by XMLicious at 10:28 AM on June 27, 2016

(So in that case, the specific form of the solution will be determined by the operating system and software available on the computer running the FTP server, so ascertain that first and maybe even ask that server's administrator if they have a suggestion for the one-line command.)
posted by XMLicious at 10:35 AM on June 27, 2016

Response by poster: XMLicious: this sounds like a good direction to go. But I don't have an FTP server available to me, and my vendor isn't exactly...technically cooperative. If I were to start a project like this from scratch, what direction would you recommend with setting up my own FTP server, then implementing a solution like you recommend. Is this something I would want to do via AWS or a similar service? All this is just a bit outside of my element.
posted by slogger at 10:43 AM on June 27, 2016

I spent a few minutes looking for/trying to figure out a simple one line command, but didn't get anywhere.

I did find this script, which says it will download all the attachments from a gmail account. You would need to setup a scheduled task (windows) or a chron job (mac/etc) to run that script once a day. You would probably also want to setup a gmail account for this task.

Then maybe use LFTP to mirror the folder that the script downloads once a day to the client's FTP server.
posted by gregr at 11:24 AM on June 27, 2016

Is this something I would want to do via AWS or a similar service?

I think it's actually Amazon EC2 or another inexpensive virtual server maintained by a cloud services provider that you'd want to go with, to do it all with standard UNIX software. So then, you'd choose among the server operating system images the provider offers (usually BSD-based OSes are reputed to be the most dependable and stable), install the basic virtual server, learn how to log in/ssh in and work with the server OS, how to install software, and learn about each piece of software (FTP server, email client, and cron), and go through several cycles of setting everything up experimentally and wiping the server to start clean. You'd also want to be sure you understand how to schedule or trigger OS updates so that security patches are applied, how to set up security (a firewall and user and file system permissions to lock down the FTP server in case it's compromised will be most important) and how and where to look at logs in case the CSV reports stop showing up and you have to figure out what point things are breaking at.

Unfortunately, if you're starting from this all being new, the whole process would be kind of like getting an associates' degree in UNIX administration. Which is totally doable - all the learning materials are free on the internet - but obviously a big investment of time on your part. Unfortunately, if you have to set up and maintain your own FTP server (if Dropbox or something like that isn't acceptable) you'll still have to learn 95% of that anyways.

I and others can give you pointers about where to start learning all of that; or the take-away might just be, if this is the solution you want to go with, you're really looking to hire a UNIX administrator with EC2 experience or something like that, rather than a programmer.

Here's another thought though: there used to be an open source project called GmailFS that allowed you to mount a GMail account as a file system. I never used it myself, but I assumed that it would make email attachments show up like files that could be opened or dragged and dropped onto your desktop. If you can find something like that, and they don't specifically require an FTP site, maybe you can get the people who need to access the reports to install that and get to the reports that way.
posted by XMLicious at 11:29 AM on June 27, 2016

You could set up Amazon Simple Email Service and have it trigger an Amazon Web Services Lambda function on message receipt to FTP the document. Check the breadcrumbs on this help article. Once it's set up you'll pay based on usage, for daily activity of not more than a megabyte or so you'll probably pay at most a dollar per month, and it should be pretty reliable. Not too trivial to set up though.
posted by books for weapons at 11:38 AM on June 27, 2016

(And also you could look to see if something like that is available for your Microsoft 365 account, something which would serve attachments up as a file system.)
posted by XMLicious at 11:38 AM on June 27, 2016

Is there a reason that emailing the export to yourself, and then uploading the CSV to the server via normal FTPing won't work for you?
posted by humboldt32 at 1:30 PM on June 27, 2016

Sorry, saw your response above. So you don't have access to your own FTP server and the client isn't providing you login credentials for theirs? What's the endpoint for any automated or scripted solution then?
posted by humboldt32 at 1:33 PM on June 27, 2016

humboldt32, I think you're missing the "automated" part of the question - the goal is to set it up so it ends up on the FTP without having to actually do anything.
posted by brainmouse at 3:28 PM on June 27, 2016

If I were using Outlook I would keep it open all day, perhaps have a daily script that reboots the computer and opens Outlook each day, and set up a VBA macro to auto-scan the mailbox periodically, save the attachment (which assumes the email has a consistent sender or subject and ideally a standard naming convention for the file but not necessary) and then call the PuTTY secure FTP program from the command line. You can even redirect the output from the upload job into a confirmation email to show it was successful. Not elegant because it relies on an interactive program albeit automated, but it would work.

There are certainly better ways but if you're curious for more tidbits on how this would work, let me know.
posted by aydeejones at 7:17 PM on June 27, 2016

And yes I would enjoy tackling this as a mini paid gig but do not mean to solicit in my first comment, just putting out the fundamental pieces and parts. It's definitely one of those questions where I ask "can't the sender of the email just upload it for you or put it into a google drive or Dropbox folder" which simplifies things a lot.
posted by aydeejones at 7:20 PM on June 27, 2016

If you have a Linux server around, you could do this with a combination of procmail, uudeview, and fetchmail (if you're not running a mail endpoint on the linux server) for turning the email into a file. After that, there are ways to automate ftp (expect, shell script, etc) if you want to upload or just run your own ftp or sftp server on the machine. This is kind of similar.
posted by fings at 9:48 PM on June 27, 2016 [1 favorite]