Handling CSV content returned from a 3rd Party API

Question

We currently have a contract with a vendor to provide a set of multi-language travel guides. The latest list of branded guides available can be downloaded via HTTP API call that returns a CSV file.

I have used:

SET @content = HTTPGet("API URL")

Which is successfully get the contents of the file, however I am struggling to find obvious functions to handle the content of the file. With this file I want to achieve the following:

1. Loop through each row of data using the line break as the delimiter
2. Split the content of each row using the "," delimiter
3. Do some processing on some of the values (including splitting destination_url using "/" delimiter
4. Store all these values in a Data Extension

The CSV file has approximately 1,144 rows, and has the following header rows:

• iso
• destination_name
• lang_iso
• lang_name
• dl_url
• destination_url

Obviously once I am getting these records into a Data Extension I can use the usual Ampscript functions to lookup and find appropriate guides to include within out email campaigns.

Answer 1

After you've retrieved your CSV file, you could utilize nested BuildRowSetfromString() functions and a Script Activity in Automation Studio to accomplish what you've outlined. The outer loop would iterate through the rows and the inner one would iterate through the columns and add rows with the UpsertDE function.

I wouldn't be surprised if you would run into issues storing and parsing that much data.

Generally speaking, doing a FTP file drop and a File Import Activity is the more tried-and-true method of getting data into the platform.