Efficient File Reading and Data Insertion in Marketing Cloud Pages

August 24, 2020
Read
In this blog post, we will explore a solution for efficiently reading files from Marketing Cloud Pages. We'll address the limitations of the API and provide a step-by-step guide for reading a CSV file, converting it to JSON, and inserting the data into a Data Extension using two Cloud Pages. Additionally, we'll discuss the importance of headers in the CSV file and offer tips for restricting access to your Cloud Page. Let's get started!

Understanding API Payload Limitations:

Before diving into the solution, it's crucial to note that the maximum payload limit for any REST call in Marketing Cloud is four megabytes. This limit becomes particularly relevant in this solution as we'll be reading a CSV file and posting it as JSON to a second Cloud Page. Being mindful of this limitation ensures a smooth execution of the solution. For further API limits, refer here.

Restricting Access to Your Cloud Page:

If you want to restrict access to your Cloud Page, you can refer to my dedicated blog post on the same topic here. It provides valuable insights and techniques to enhance the security of your Cloud Page and control who can access it.

Initial Cloud Page: Reading and Posting JSON:

In this step, we'll focus on the first Cloud Page, responsible for reading the uploaded CSV file and converting it to JSON. The CSV file will be processed, and the data will be transformed into a JSON payload. This JSON payload will then be posted to the second Cloud Page for further processing. Your CSV should have headers for mapping

The sample file I used is on GitHub here.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<input type="file">
<!--Change the action attribute to the cloud page where your insert logic exists -->
<form name="myform" action="https://fe3d11717d64047c751473.pub.s7.sfmc-content.com/mqknnomyfba" method="post">
  <input type="hidden" id="userdata" value="" name="userdata">
</form>
<script>
  function ReadCSVFunction(file, delimiter, callback) {
    var reader = new FileReader();
    // after the file reader had loaded the file
    reader.onload = function() {
      callback(this.result);
    }
    // Read the file content as a single string
    reader.readAsText(file);
  }
  //var csv is the CSV file with headers
  function CSVtoJSONFunction(csv){
    //Split string at new line charecters
    //new line charecter may vary
    var lines=csv.split("\r\n");
    //convert string to JSON
    var result = [];
    var headers=lines[0].split(",");
    for(var i=1;i<lines.length;i++){
      var obj = {
      };
      var currentline=lines[i].split(",");
      for(var j=0;j<headers.length;j++){
        obj[headers[j]] = currentline[j];
      }
      result.push(obj);
    }
    //Write JSON to hideen input and submit form
    document.getElementById("userdata").value =JSON.stringify(result);
    document.myform.submit();
  }
  //add listner to input file
  document.querySelector('input[type="file"]')
    .addEventListener('change', function() {
    var files = this.files;
    for (var i = 0; i < files.length; i++) {
      ReadCSVFunction(files[i], ',', function(result) {
        CSVtoJSONFunction(result.toString());
      });
    }
  }
                     )
</script>


Second Cloud Page: Data Insertion into a Data Extension:

The second Cloud Page comes into play when the JSON payload is received. Using Server-Side JavaScript (SSJS), the code on this page will parse the JSON and insert the data into a designated Data Extension. It's crucial to customize the attribute mappings in the code to align with your specific file headers.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<h1>
  Your data has been imported successfully
</h1>
%%[
    /*Declare Variables*/
    var @userdata
    /*Get JSON  from url*/
    Set @userdata = RequestParameter("userdata")
]%%
<script runat="server">
  Platform.Load("Core","1.1.1");
  try{
    //Get JSON string retrived by AMPscript from URL parameter
    var userdata= Variable.GetValue("@userdata");
    //parse JSON
    var users = Platform.Function.ParseJSON(userdata)
    //Loop through JSON aray and insert every single user into DE
    var userarray=[];
    for (var i = 0; i < users.length; i++) {
      //get single user
      var user = users[i];
      //Create user array for insert function
      //Change the attributes as per headers in your file
      userarray.push(user['First Name']);
      userarray.push(user['Last Name']);
      userarray.push(user['Email']);
      //Insert data into DE
      var rowcount = Platform.Function.InsertData("UserDE",["FirstName","LastName","Email"],userarray);
      //reset userarray for new user
      userarray.length=0;
    }
  }
  //end try
  catch(e){
    Write(Stringify(e));
  }
  //End catch
</script>


Remember to consider API payload limitations, headers in your CSV file, and explore methods for restricting access to your Cloud Pages. 
Tags :

No comments:

Post a Comment

Powered by Blogger.