Posted in Marketing Cloud, Salesforce

Marketing Cloud : Programmatically Export Data Extension

Exporting a Data Extension through Marketing Cloud interface is possible through Email Studio or through Contact Builder. Bulk exporting of Data Extensions is not yet available as of this writing and is painful when we have to export them.

This simple SSJS script would help us to download them with simple clicks:

Screenshot of a simple Data Extension Manager

Created this with two Code Snippet Content Blocks (each for searching the Data Extension and to download the CSV file) and a Cloud Page with 2 items inside the collection to add two content blocks.

Here is the boilerplate for you to customize the things upon it:

Data Extension Manager block: To search the Data Extension using Name or External Key with operators – equals, not equals, in and like.

Data Extension Export CSV block: To download the CSV file with the Data Extension contents.

<script runat="server" language="JavaScript">
Platform.Load("core","1.1.2");
try {
var DE_CustomerKey = Request.GetQueryStringParameter("de_key");
var DE = DataExtension.Init(DE_CustomerKey);
var DEName = DataExtension.Retrieve({Property:"CustomerKey",SimpleOperator:"equals",Value:DE_CustomerKey})[0].Name;
var DEFields = DE.Fields.Retrieve();
var DEFieldsList = [];
for (var i = 0; i < DEFields.length; i++) {
DEFieldsList.push(DEFields[i].Name);
}
Write(DEFieldsList.join());
Write("\r\n");
var DERows = DE.Rows.Retrieve();
for (var i = 0; i < DERows.length; i++) {
var DERowsList = [];
var record = DERows[i];
for(var j = 0; j < DEFieldsList.length; j++) {
DERowsList.push(record[DEFieldsList[j]]);
}
Write(DERowsList.join());
Write("\r\n");
}
HTTPHeader.SetValue("content-disposition","attachment; filename=" + DEName + ".csv");
} catch(e) {
Write(Stringify(e));
}
</script>
<script runat="server" language="JavaScript">
Platform.Load("core","1.1.2");
try {
var DESearchOperand = Request.GetQueryStringParameter("DESearchOperand") || "CustomerKey";
var DENameOperator = Request.GetQueryStringParameter("DENameOperator") || "equals";
var DENameOperand = Request.GetQueryStringParameter("DENameOperand") || null;
Variable.SetValue("DESearchOperand", DESearchOperand);
Variable.SetValue("DENameOperator", DENameOperator);
Variable.SetValue("DENameOperand", DENameOperand);
if(DENameOperand != null) {
var DEList = DataExtension.Retrieve({
Property: DESearchOperand,
SimpleOperator: DENameOperator,
Value: (DENameOperator == "in" ? DENameOperand.split(",") : DENameOperand)
});
var DETableRows = [];
for (var i = 0; i < DEList.length; i++) {
// Name
var tempVar = "<td>" + DEList[i].Name + "</td>";
tempVar += "<td>" + DEList[i].CustomerKey + "</td>";
// Folder Path
var folderPath = [];
var path = function(id) {
if (id> 0) {
var results = Folder.Retrieve({Property:"ID",SimpleOperator:"equals",Value:id});
folderPath.unshift(results [0].Name);
return path(results[0].ParentFolder.ID);
} else {
return id;
}
};
path(DEList[i].CategoryID);
tempVar += "<td>" + folderPath.join(" ⇒ ") + "</td>";
// Operation
var operationString = '<a href="https://abc.xyz/downloadCSV?de_key=&#39; + DEList[i].CustomerKey + '" title="Export CSV">⇩</a>';
operationString += '<a href="https://abc.xyz/viewCSV?de_key=&#39; + DEList[i].CustomerKey + '" title="Export CSV">👁</a>';
tempVar += "<td>" + operationString + "</td>";
DETableRows.push(tempVar);
}
Variable.SetValue("DETableRows", DETableRows);
}
} catch(e) {
Write(Stringify(e));
}
</script>
<style>
a {
text-decoration: none;
padding-right: 0.5rem;
}
</style>
<h1 style="text-align: center">DATA EXTENSION MANAGER</h1>
<form method="post">
Get Data Extension(s) whose
<select name="DESearchOperand">
<option %%=IIF(@DESearchOperand=="CustomerKey", "Selected", "")=%% value="CustomerKey" %%=IIF(@DESearchOperand=="CustomerKey", "Selected", "")=%%>External Key</option>
<option value="Name" %%=IIF(@DESearchOperand=="Name", "Selected", "")=%%>Data Extension Name</option>
</select>
<select name="DENameOperator">
<option %%=IIF(@DENameOperator=="equals", "Selected", "")=%%>equals</option>
<option %%=IIF(@DENameOperator=="notEquals", "Selected", "")=%%>notEquals</option>
<option %%=IIF(@DENameOperator=="in", "Selected", "")=%%>in</option>
<option %%=IIF(@DENameOperator=="like", "Selected", "")=%%>like</option>
</select>
<input type="text" name="DENameOperand" value="%%=v(@DENameOperand)=%%">
<input type="submit">
</form>
%%[ IF ROWCOUNT(@DETableRows) > 0 THEN ]%%
<table border="1" cellpadding="7">
<thead>
<th>Data Extension Name</th>
<th>Data Extension External Key</th>
<th>Path</th>
<th>Operation</th>
</thead>
<tbody>
%%[
FOR @I = 1 TO ROWCOUNT(@DETableRows) DO
]%%
<tr>
%%=v(Row(@DETableRows, @I))=%%
</tr>
%%[ NEXT @I ]%%
</tbody>
</table>
%%[ ENDIF ]%%

Add these content blocks to the Cloud Page website.

Note:

  • Remove all the contents and just add the snippet to call the content block in the cloud page. Otherwise, the generated CSV file during export would result in adding the other HTML tags to your file.
  • Remember to replace the hyper link in Line 40 in Data Extension Manager.block file with the ones that got generated for you upon creating the cloud pages.

Enter the Data Manager Cloud Page URL, search for the keywords for the Data Extension to export, you would see ⇩ arrow. Clicking on it downloads the CSV file.

Do share your code in comments when you add some features to it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s