Third Light Developer Exchange

Code and templating community forum for developers and software integrators

You are not logged in.

Announcement

If you wish to join the Developer Exchange, please contact your account manager - this is to avoid unnecessary spam in the forums. Many thanks for your understanding.

#1 2013-12-17 16:28:18

steve
Third Light Staff
Registered: 2013-06-06
Posts: 105

Importing user accounts from a spreadsheet

This example is designed to be run from the CLI and allows a list of users accounts to be created through the API based on input taken from a .csv file.

Example .csv content

Username    First Name   Surname   Email                 Group
simon       Simon        Jones     sjones@company.com    admin
bob         Robert       Miller    rmiller@company.com   Marketing
bill        William      Smith     wsmith@company.com    Accounts
alice       Alice        Walker    awalker@company.com

Example usage:

php UsersFromSpreadsheet.php -h "yoursite.com" -u "admin" -p "yourpassword" -f "sample_user_spreadsheet.csv"

This script makes use of the PHP client

<?php

// Display request and response data
// Choices are "DisplayRequestAndResponseJSONTxt", "DisplayRequestAndResponseArrayTxt" or ""
$arrExtraParams = array("DEBUG"=> "DisplayRequestAndResponseJSONTxt");

chdir(__DIR__);
require_once("displaysupport.php");
require_once("../imsapiclient.php");

try{
	
	$curDir = getcwd();

	$arrOpts = getopt("u:p:k:f:h:");

	$arrColumns = array("username" => null, "description" => null, "firstname" => null, "surname" => null, "email" => null, "emailaddress" => null, "group" => null, "preset" => null);

	// Validate Inputs
	if(empty($arrOpts["h"]))
	{
		echo "Specify IMS host using -h \n";
		exit(1);
	}

	if(empty($arrOpts["k"]) && (empty($arrOpts["u"]) || empty($arrOpts["p"])))
	{
		echo "Specify username/password with -u -p\n";
		echo "Alternatively provide API key with -k\n";
		exit(1);
	}

	if(empty($arrOpts["f"]))
	{
		echo "Specify filename of CSV with -f\n";
		exit(2);
	}

	if(!file_exists($arrOpts["f"]))
	{
		echo "Specified CSV not found\n";
		exit(3);
	}

	$rFile = @fopen($arrOpts["f"], "r");
	if(!$rFile)
	{
		echo "Failed to open file\n";
		exit(3);
	}

	$arrHeader = fgetcsv($rFile);

	if(!$arrHeader)
	{
		echo "File does not parse as CSV\n";
		exit(4);
	}

	foreach($arrHeader as $nIndex => $strValue)
	{
		$strSafeVal = mb_strtolower(preg_replace("/[^a-zA-Z0-9]/", "", $strValue));
		if(array_key_exists($strSafeVal, $arrColumns))
		{
			$arrColumns[$strSafeVal] = $nIndex;
		}
	}

	$nUserCol = $nEmailCol = $nGroupCol = $descCallback = null;

	if(!isset($arrColumns["username"]))
	{
		echo "No username column found\n";
		exit(5);
	}
	$nUserCol = $arrColumns["username"];

	if(!isset($arrColumns["email"]) && !isset($arrColumns["emailaddress"]))
	{
		echo "No e-mail address column found\n";
		exit(6);
	}
	elseif(isset($arrColumns["email"]))
	{
		$nEmailCol = $arrColumns["email"];
	}
	else
	{
		$nEmailCol = $arrColumns["emailaddress"];
	}

	if(isset($arrColumns["group"]))
	{
		$nGroupCol = $arrColumns["group"];
	}
	else
	{
		$nGroupCol = $arrColumns["preset"];
	}

	if(isseT($arrColumns["description"]))
	{
		$descCallback = function($arr) use ($arrColumns) {
			return $arr[$arrColumns["description"]];
		};
	}
	elseif(isset($arrColumns["firstname"]))
	{
		if(isset($arrColumns["surname"]))
		{
			$descCallback = function($arr) use ($arrColumns) {
				return $arr[$arrColumns["firstname"]]." ".$arr[$arrColumns["surname"]];
			};
		}
		else
		{
			$descCallback = function($arr) use ($arrColumns) {
				return $arr[$arrColumns["firstname"]];
			};
		}
	}
	elseif(isset($arrColumns["surname"]))
	{
		$descCallback = function($arr) use ($arrColumns) {
			return $arr[$arrColumns["surname"]];
		};
	}
	else
	{
		echo "No description column found\n";
		exit(7);
	}


	$arrUserInfo = array();

	while($arr = fgetcsv($rFile))
	{
		$thisUser = array(
			"username" => $arr[$nUserCol],
			"email" => $arr[$nEmailCol],
			"group" => "auto",
			"description" => "");
		if($nGroupCol && !empty($arr[$nGroupCol]))
		{
			$thisUser["group"] = mb_strtolower($arr[$nGroupCol]);
		}
		$thisUser["description"] = $descCallback($arr);

		$arrUserInfo[] = $thisUser;
	}

	fclose($rFile);


	$strApiKey = isset($arrOpts["k"]) ? $arrOpts["k"] : null;
	$objAPI = new IMSApiClient($arrOpts["h"], $strApiKey, $arrExtraParams);

	if(!$strApiKey)
	{
		$objAPI->Login(array("username"=>$arrOpts["u"], "password" => $arrOpts["p"]));
	}

	$arrPresetInfo = $objAPI->Users_GetAvailableUserPresets(array("includeGroups" => true));
	$arrPresetMappings = array();
	foreach($arrPresetInfo["presets"] as $thisPreset)
	{
		$arrPresetMappings[$thisPreset["id"]] = $thisPreset["id"];
	}
	foreach($arrPresetInfo["groups"] as $thisPreset)
	{
		$arrPresetMappings[mb_strtolower($thisPreset["description"])] = $thisPreset["id"];
	}

	foreach($arrUserInfo as $thisUser)
	{
		$thisUser["preset"] = "auto";
		if(array_key_exists($thisUser["group"], $arrPresetMappings))
		{
			$thisUser["preset"] = $arrPresetMappings[$thisUser["group"]];
		}
		unset($thisUser["group"]);
		$objAPI->Users_CreateUser($thisUser);
	}
}
catch(IMSApiClientException $e)
{
	echo "Error: ".get_class($e)."\n".$e->getMessage()."\n";
	if ($arrExtraParams["DEBUG"] =="") echo "Enable debug to see API request/response\n";
}

Offline

Board footer