How can I use SFMC automation studio to download a csv from a site into the import ftp folder and import that csv into a data extension?
Do I need to use SJSS? Or is there an activity that does that that I'm missing?
-
The question is : What do you want to achieve with this. It helps when we know the use case. – Johannes Schapdick Feb 1 '19 at 6:05
-
Thanks Johannes, so basically we want to get e.g. nightly customer updates. So a URL is provided to download a CSV from a website with that information. I want to download that csv, and then import those to into my data extensions... – mercifulhop Feb 1 '19 at 6:11
-
Is it possible that website system can access the SFTP server of salesforce marketing cloud and directly store that file into the SFTP server of salesforce with a pattern you chose? -> Than you can make a trigger based automation that checks if a file of a specific pattern has been uploaded to a specifc sftp location / folder. – Johannes Schapdick Feb 1 '19 at 7:21
-
Sadly that doesn't look like it's an option at the moment. – mercifulhop Feb 1 '19 at 8:28
-
There might be a better way to cover this but i will describe my way: When I have control over a webserver i would do this: Write a program that retrieves the file and then sends it to a particular location on the sftp server of salesforce. If i don't have a webserver or a coding environment i would do that with powershell / batch whatever. Run the script on particular times which then saves the file to a particular folder - after that i would write the script that transfers it to the sftp server or create a synchronisation between the folder and the sftp server in a different script. – Johannes Schapdick Feb 1 '19 at 8:43
So finally worked this out was working with Salesforce support for quite a while on this.
Create a location for the external sftp https://help.salesforce.com/articleView?id=mc_overview_create_file_transfer_location.htm&type=5
A file transfer activity can download the file. However, it's not clearly documented that it downloads this into the safehouse by default.
In theory you can use another file transfer activity to move this out of the safehouse to e.g. the import folder, however due to a bug, at the moment this is not possible.
The best bet is to use a file import activity to import the file from the safehouse. Because there's no visibility into the safehouse, you must know the file exists there beforehand and ensure you get the filename pattern correct.
Be careful with paths, I was downloading a zip from an sftp like host/folder/archive.zip. Apparently after unzipped into the safehouse, the file is unzipped to folder/file.csv
So I had to change the host from e.g. 127.0.0.1 to 127.0.0.1/folder, the import file activity doesn't accept slashes in the filename pattern.
If you can get the site to push the file to SFMC's eFTP, you will then be able to use import file activity followed by file extract activity into a DE
-
Yeah, it would be good if we could do it that way; but doesn't look like it's an option... – mercifulhop Feb 4 '19 at 0:55
