This article explains how to generate a Data Extension with random records using server-side Javascript in Salesforce Marketing Cloud.
Not a fan of reading? Jump to the code snippet.
Yes, but why?
Creating a Data Extension with a dozen of fake records is not a big deal. But what if we needed a Data Extension with thousands or millions of records?
The default solution would be cloning an existing Data Extension with real records, but it takes time and involves exporting and importing the records.
What if there was a better way?
What if we could generate a Data Extension with all types of fields and an unlimited number of random records just in seconds?
Let’s find out!
Fields & properties
At first, we need to define the fields of the Data Extension, one of each type and with different properties.
| Name | FieldType | IsPrimaryKey | IsRequired | MaxLength | DefaultValue | Scale |
| Primary | Text | true | true | 50 | ||
| Text_with_Default | Text | false | false | 50 | Hello world | |
| Number | Number | false | false | |||
| Date | Date | false | false | |||
| Boolean | Boolean | false | false | false | ||
| EmailAddress | false | false | 254 | |||
| Phone | Phone | false | false | |||
| Decimal | Decimal | false | false | 18 | 2 | |
| Locale | Locale | false | false | |||
| Text_required | Text | false | true | 50 | true |
In JavaScript code this translates to the following:
var fields = [
{
"Name": "Primary",
"FieldType": "Text",
"IsPrimaryKey": true,
"IsRequired": true,
"MaxLength": 50
},
{
"Name": "Text_with_Default",
"FieldType": "Text",
"MaxLength": 50,
"DefaultValue": "Hellow world"
},
{
"Name": "Number",
"FieldType": "Number"
},
{
"Name": "Date",
"FieldType": "Date"
},
{
"Name": "Boolean",
"FieldType": "Boolean",
"DefaultValue": false
},
{
"Name": "Email",
"FieldType": "EmailAddress"
},
{
"Name": "Phone",
"FieldType": "Phone"
},
{
"Name": "Decimal",
"FieldType": "Decimal",
"MaxLength": 18,
"Scale": 2
},
{
"Name": "Locale",
"FieldType": "Locale"
},
{
"Name": "Text_required",
"FieldType": "Text",
"MaxLength": 50,
"IsRequired": true
}
];Random data
Now that we know each field type we are going to be working with, we need to figure out a way to create a random data for each type.
Let’s create a function that returns a random record based on the FieldType attribute of a Data Extension field.
function getRandom(type) {
if(type == "Decimal") return Math.floor(Math.random() * (1000 - 100) + 100) / 100;
if(type == "EmailAddress") return Math.floor(Math.random() * 10000000000) + "@mail.com";
if(type == "Boolean") return (Math.random() >= 0.5);
if(type == "Number") return Math.floor(Math.random() * 100);
if(type == "Date") return new Date(+(new Date()) - Math.floor(Math.random() * 10000000000));
if(type == "Phone") {
var n = "0";
for (var i = 0; i < 9; i++) {
n += Math.floor(Math.random() * 9)
}
return n;
}
if(type == "Locale") {
switch (Math.floor(Math.random() * 4)) {
case 0: var loc = "FR"; break;
case 1: var loc = "NL"; break;
case 2: var loc = "RU"; break;
case 3: var loc = "EN"; break;
}
return loc;
}
if(type == "Text") {
var str = "lorem ipsum dolor sit amet consectetur adipiscing elit donec vel nunc eget augue dignissim bibendum";
arr = str.split(" ");
var ctr = arr.length, temp, index;
while (ctr > 0) {
index = Math.floor(Math.random() * ctr);
ctr--;
temp = arr[ctr];
arr[ctr] = arr[index];
arr[index] = temp;
}
str = arr.join(" ");
return str;
}
}Generate a Data Extension
In order to generate a Data Extension, we will use SOAP API with WSProxy object.
var api = new Script.Util.WSProxy();
var customerKey = Platform.Function.GUID();
var conf = {
"CustomerKey": customerKey,
"Name": "MyDataExtension",
"Fields": fields
};
var result = api.createItem("DataExtension", conf);Insert random data
The last thing to do is to insert the random data with the function we created previously, as many times as we want (3000 for example).
var de = DataExtension.Init(customerKey);
var payload = [];
for (var i = 0; i < 3000; i++) {
var obj = {};
for (k in fields) {
obj[fields[k].Name] = getRandom(fields[k].FieldType);
}
payload.push(obj);
}
var addedRowCount = de.Rows.Add(payload);Note that in case of a Primary key field, we need to be sure it stays unique. Therefore a GUID() function is more suitable in this case.
if(fields[k].FieldType == "Text" && fields[k].IsPrimaryKey == true) {
obj[fields[k].Name] = Platform.Function.GUID();
} else {
obj[fields[k].Name] = getRandom(fields[k].FieldType);
}Full code
Let’s wrap it up by integrating everything in a nice clean function, that we need to provide with a Data Extension name, folder name and the amount of random records to create.
<script runat="server">
Platform.Load("core", "1.1");
var insrt = createDemoDataExtension("MyDataExtension","MyFolder",3000);
function createDemoDataExtension(name, folder, num) {
var api = new Script.Util.WSProxy();
var fields = [
{
"Name": "Primary",
"FieldType": "Text",
"IsPrimaryKey": true,
"IsRequired": true,
"MaxLength": 50
},
{
"Name": "Text_with_Default",
"FieldType": "Text",
"MaxLength": 50,
"DefaultValue": "Hellow world"
},
{
"Name": "Number",
"FieldType": "Number"
},
{
"Name": "Date",
"FieldType": "Date"
},
{
"Name": "Boolean",
"FieldType": "Boolean",
"DefaultValue": false
},
{
"Name": "Email",
"FieldType": "EmailAddress"
},
{
"Name": "Phone",
"FieldType": "Phone"
},
{
"Name": "Decimal",
"FieldType": "Decimal",
"MaxLength": 18,
"Scale": 2
},
{
"Name": "Locale",
"FieldType": "Locale"
},
{
"Name": "Text_required",
"FieldType": "Text",
"MaxLength": 50,
"IsRequired": true
}
];
var conf = {
"CustomerKey": Platform.Function.GUID(),
"Name": name,
"Fields": fields
};
var req = Folder.Retrieve({ Property: 'Name', SimpleOperator: 'equals', Value: folder });
var catId = req[0].ID;
if (catId != null) conf["CategoryID"] = catId;
var res = api.createItem("DataExtension", conf);
if (res["Status"] == "OK") {
var message = '(+) Data Extension "' + name + '" has been created';
if (catId != null) message += ' in the folder "' + folder + '".'; else message += ' in the root folder.';
Write(message + "<br>");
var customerKey = res.Results[0].Object.CustomerKey;
var de = DataExtension.Init(customerKey);
var payload = [];
for (var i = 0; i < num; i++) {
var obj = {};
for (k in fields) {
obj[fields[k].Name] = (fields[k].FieldType == "Text" && fields[k].IsPrimaryKey == true) ? Platform.Function.GUID() : getRandom(fields[k].FieldType);
}
payload.push(obj);
}
var addedRowCount = de.Rows.Add(payload);
Write("(+) Rows added: " + Stringify(addedRowCount) + "<br>");
return addedRowCount;
} else {
Write("(-) Something went wrong: " + Stringify(res.Results[0].StatusMessage));
}
function getRandom(type) {
if(type == "Decimal") return Math.floor(Math.random() * (1000 - 100) + 100) / 100;
if(type == "EmailAddress") return Math.floor(Math.random() * 10000000000) + "@mail.com";
if(type == "Boolean") return (Math.random() >= 0.5);
if(type == "Number") return Math.floor(Math.random() * 100);
if(type == "Date") return new Date(+(new Date()) - Math.floor(Math.random() * 10000000000));
if(type == "Phone") {
var n = "0";
for (var i = 0; i < 9; i++) {
n += Math.floor(Math.random() * 9)
}
return n;
}
if(type == "Locale") {
switch (Math.floor(Math.random() * 4)) {
case 0: var loc = "FR"; break;
case 1: var loc = "NL"; break;
case 2: var loc = "RU"; break;
case 3: var loc = "EN"; break;
}
return loc;
}
if(type == "Text") {
var str = "lorem ipsum dolor sit amet consectetur adipiscing elit donec vel nunc eget augue dignissim bibendum";
arr = str.split(" ");
var ctr = arr.length, temp, index;
while (ctr > 0) {
index = Math.floor(Math.random() * ctr);
ctr--;
temp = arr[ctr];
arr[ctr] = arr[index];
arr[index] = temp;
}
str = arr.join(" ");
return str;
}
}
}
</script>That’s it folks! Please keep in mind that there are plenty of other and better ways to create random data per field type. Feel free to come up with something that fits your needs perfectly.
Have I missed anything?
Please poke me with a sharp comment below or use the contact form.
Thank you Master Razine