How to generate a Data Extension with random records using server-side JavaScript

How to generate a Data Extension with random records using server-side JavaScript

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.

NameFieldTypeIsPrimaryKeyIsRequiredMaxLengthDefaultValueScale
PrimaryTexttruetrue50
Text_with_DefaultTextfalsefalse50Hello world
NumberNumberfalse false
DateDatefalse false
BooleanBooleanfalse false false
EmailEmailAddressfalse false 254
PhonePhonefalse false
DecimalDecimalfalse false 182
LocaleLocalefalse false
Text_requiredTextfalsetrue50true

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.

Pay me a coffee

Want to say thanks? Pay me a coffee! Remember, I turn coffee into code.

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.

Leave a Reply

Your email address will not be published. Required fields are marked *

Salesforce Marketing Cloud
Up Next:

The 5 ways of adding and updating records in SFMC using AMPscript and server-side JavaScript

The 5 ways of adding and updating records in SFMC using AMPscript and server-side JavaScript