Import Clients To WHMCS Via CVS

mywebguy · January 11, 2019

I have a completely new install of WHMCS with 0 clients.

I have 98 clients in a CSV file with these columns

first_name | last_name | email

Email

FirstName

LastName

Company

Address1

Address2

City

State/Province

Zip/PostalCode

Country

Phone

Can anyone advise if there is a script or what I should do in phpmyAdmin to import these into my WHMCS please?

Is it possible someone can do a video "specifically for WHMCS" which shows how this would be done?

Thanks in advance.

brian! · January 15, 2019

perhaps the link to the video I posted in the thread below might help...

in WHMCS, the database table you will want to be importing to is tblclients - but backup the entire database (even if tblclients is empty) as a precaution before beginning. 🙂

also worth noting that in WHMCS, the country is stored as a two-letter ISO abbreviation - so if in the csv, that field currently isn't in that format, you might need to do a search & replace before importing.

one other minor thing when importing clients from csv is that the uuid value probably won't be automatically generated - editing the record(s) via the admin area will add any missing entry, or there is an admin script (untested by me) that should generate them.

Remitur · January 17, 2019

I created this script years ago, modifying an existing one in order to add the import of custom fields too.
In this version it managed 3 different custom fields, with ID = 3, 6 and 9; you can easily edit it to adapt to your own needs.

It uses WHMCS APi, so it's slightly more secure than working on phpmyadmin... but do a backup before running it!!!!

$scriptActive = 1;

    $url = "/includes/api.php"; # URL to WHMCS API file
    $username = "username"; # Admin username goes here
    $password = "pa$$word"; # Admin password goes here

    // Set path to CSV file
    $fileDirectory = dirname(__FILE__)."/csvfile/";
    $csvFile = "customer.csv";

    $csvFile = ($fileDirectory.$csvFile);
    $csvRowArray = readCSV($csvFile);

    /******************************************************************************/

    function readCSV($csvFile){

        if (file_exists($csvFile)) {

            $file_handle = fopen($csvFile, 'r');
            while (!feof($file_handle) ) {
                $line_of_text[] = fgetcsv($file_handle, 1024);

            }
            fclose($file_handle);

            //REMOVE FIRST LINE (WITH HEADERS)
            unset($line_of_text[0]);

            // RESET INDEXES & REMOVE EMPTY ARRAY ELEMENTS
            $line_of_text = array_values($line_of_text);
            $line_of_text = array_filter( $line_of_text );

            return $line_of_text;

        } else {
            echo "<h1>$csvFile does not exist</h1>";
            die;
        }

    }

    ob_start(); //Turning ON Output Buffering

    $postfields["username"] = $username;//DO NOT CHANGE
    $postfields["password"] = md5($password);//DO NOT CHANGE
    $postfields["action"] = "addclient"; # action performed by the [[API:Functions]]

    foreach($csvRowArray as $key => $csvRow) {
        $postfields["firstname"] = ucfirst($csvRow[0]);
        $postfields["lastname"] = ucfirst($csvRow[1]);
        $postfields["companyname"] = ucfirst($csvRow[2]);
        $postfields["email"] = strtolower($csvRow[3]);
        $postfields["address1"] = ucfirst($csvRow[4]);
        $postfields["address2"] = ($csvRow[5]!=''?ucfirst($csvRow[5]):'');
        $postfields["city"] = ucfirst($csvRow[6]);
        $postfields["state"] = strtoupper($csvRow[7]);
        $postfields["postcode"] = $csvRow[8];
        $postfields["country"] = strtoupper($csvRow[9]);
        $postfields["phonenumber"] = preg_replace("/[^0-9]/","",$csvRow[10]);
        $postfields["password2"] = $csvRow[11];
        $postfields["language"] = $csvRow[12];
        $postfields["notes"] = stripslashes($csvRow[13]);
        $postfields["customfields"] = base64_encode(serialize(array(
                   "1" => ucfirst($csvRow[14]),
                   "6" => ucfirst($csvRow[15]),
                   "9" => ucfirst($csvRow[16])
                   )));
        
        
        $postfields["noemail"] = 1;

        if ($scriptActive==1) {
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_POST, 1);
            curl_setopt($ch, CURLOPT_TIMEOUT, 100);
            curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $postfields);
            $data = curl_exec($ch);

            $data = explode(";",$data);
            foreach ($data AS $temp) {
                $temp = explode("=",$temp);
                $results[$temp[0]] = $temp[1];
            }

            ob_flush();//Flush the data here
            if ($results["result"]=="success") {
                # Result was OK!
                echo $postfields["firstname"]." ".$postfields["lastname"]." was successfully added!";
            } else {
                # An error occured
                echo "The following error occured: ".$results["message"];
            }
            curl_close($ch);
        }
    }

    if ($scriptActive!=1) {
        echo "Import File: " . $csvFile . "<br><br>";
        echo '<pre>';
        print_r($csvRowArray);
        echo '</pre>';
    }

    ob_end_flush();

JamesN · October 9, 2020

On 1/17/2019 at 12:20 PM, Remitur said:

I created this script years ago, modifying an existing one in order to add the import of custom fields too.
In this version it managed 3 different custom fields, with ID = 3, 6 and 9; you can easily edit it to adapt to your own needs.

It uses WHMCS APi, so it's slightly more secure than working on phpmyadmin... but do a backup before running it!!!!


$scriptActive = 1;

    $url = "/includes/api.php"; # URL to WHMCS API file
    $username = "username"; # Admin username goes here
    $password = "pa$$word"; # Admin password goes here

    // Set path to CSV file
    $fileDirectory = dirname(__FILE__)."/csvfile/";
    $csvFile = "customer.csv";

    $csvFile = ($fileDirectory.$csvFile);
    $csvRowArray = readCSV($csvFile);

    /******************************************************************************/

    function readCSV($csvFile){

        if (file_exists($csvFile)) {

            $file_handle = fopen($csvFile, 'r');
            while (!feof($file_handle) ) {
                $line_of_text[] = fgetcsv($file_handle, 1024);

            }
            fclose($file_handle);

            //REMOVE FIRST LINE (WITH HEADERS)
            unset($line_of_text[0]);

            // RESET INDEXES & REMOVE EMPTY ARRAY ELEMENTS
            $line_of_text = array_values($line_of_text);
            $line_of_text = array_filter( $line_of_text );

            return $line_of_text;

        } else {
            echo "<h1>$csvFile does not exist</h1>";
            die;
        }

    }

    ob_start(); //Turning ON Output Buffering

    $postfields["username"] = $username;//DO NOT CHANGE
    $postfields["password"] = md5($password);//DO NOT CHANGE
    $postfields["action"] = "addclient"; # action performed by the [[API:Functions]]

    foreach($csvRowArray as $key => $csvRow) {
        $postfields["firstname"] = ucfirst($csvRow[0]);
        $postfields["lastname"] = ucfirst($csvRow[1]);
        $postfields["companyname"] = ucfirst($csvRow[2]);
        $postfields["email"] = strtolower($csvRow[3]);
        $postfields["address1"] = ucfirst($csvRow[4]);
        $postfields["address2"] = ($csvRow[5]!=''?ucfirst($csvRow[5]):'');
        $postfields["city"] = ucfirst($csvRow[6]);
        $postfields["state"] = strtoupper($csvRow[7]);
        $postfields["postcode"] = $csvRow[8];
        $postfields["country"] = strtoupper($csvRow[9]);
        $postfields["phonenumber"] = preg_replace("/[^0-9]/","",$csvRow[10]);
        $postfields["password2"] = $csvRow[11];
        $postfields["language"] = $csvRow[12];
        $postfields["notes"] = stripslashes($csvRow[13]);
        $postfields["customfields"] = base64_encode(serialize(array(
                   "1" => ucfirst($csvRow[14]),
                   "6" => ucfirst($csvRow[15]),
                   "9" => ucfirst($csvRow[16])
                   )));
        
        
        $postfields["noemail"] = 1;

        if ($scriptActive==1) {
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_POST, 1);
            curl_setopt($ch, CURLOPT_TIMEOUT, 100);
            curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $postfields);
            $data = curl_exec($ch);

            $data = explode(";",$data);
            foreach ($data AS $temp) {
                $temp = explode("=",$temp);
                $results[$temp[0]] = $temp[1];
            }

            ob_flush();//Flush the data here
            if ($results["result"]=="success") {
                # Result was OK!
                echo $postfields["firstname"]." ".$postfields["lastname"]." was successfully added!";
            } else {
                # An error occured
                echo "The following error occured: ".$results["message"];
            }
            curl_close($ch);
        }
    }

    if ($scriptActive!=1) {
        echo "Import File: " . $csvFile . "<br><br>";
        echo '<pre>';
        print_r($csvRowArray);
        echo '</pre>';
    }

    ob_end_flush();

Sorry for an old bump, but would this be a python script? Trying to run it now to import 400ish customers :-)

Thank you!

Remitur · October 22, 2020

On 10/9/2020 at 3:35 PM, JamesN said:

Sorry for an old bump, but would this be a python script? Trying to run it now to import 400ish customers 🙂

It's PHP... (even if it's missing the usual PHP open tag)

I guess it's stll working fine; when I wrote it, years ago, used it to import few thousands users, 500 users at each run ...

Bryan K · November 6, 2020

On 1/18/2019 at 1:20 AM, Remitur said:

I created this script years ago, modifying an existing one in order to add the import of custom fields too.
In this version it managed 3 different custom fields, with ID = 3, 6 and 9; you can easily edit it to adapt to your own needs.

It uses WHMCS APi, so it's slightly more secure than working on phpmyadmin... but do a backup before running it!!!!


$scriptActive = 1;

    $url = "/includes/api.php"; # URL to WHMCS API file
    $username = "username"; # Admin username goes here
    $password = "pa$$word"; # Admin password goes here

    // Set path to CSV file
    $fileDirectory = dirname(__FILE__)."/csvfile/";
    $csvFile = "customer.csv";

    $csvFile = ($fileDirectory.$csvFile);
    $csvRowArray = readCSV($csvFile);

    /******************************************************************************/

    function readCSV($csvFile){

        if (file_exists($csvFile)) {

            $file_handle = fopen($csvFile, 'r');
            while (!feof($file_handle) ) {
                $line_of_text[] = fgetcsv($file_handle, 1024);

            }
            fclose($file_handle);

            //REMOVE FIRST LINE (WITH HEADERS)
            unset($line_of_text[0]);

            // RESET INDEXES & REMOVE EMPTY ARRAY ELEMENTS
            $line_of_text = array_values($line_of_text);
            $line_of_text = array_filter( $line_of_text );

            return $line_of_text;

        } else {
            echo "<h1>$csvFile does not exist</h1>";
            die;
        }

    }

    ob_start(); //Turning ON Output Buffering

    $postfields["username"] = $username;//DO NOT CHANGE
    $postfields["password"] = md5($password);//DO NOT CHANGE
    $postfields["action"] = "addclient"; # action performed by the [[API:Functions]]

    foreach($csvRowArray as $key => $csvRow) {
        $postfields["firstname"] = ucfirst($csvRow[0]);
        $postfields["lastname"] = ucfirst($csvRow[1]);
        $postfields["companyname"] = ucfirst($csvRow[2]);
        $postfields["email"] = strtolower($csvRow[3]);
        $postfields["address1"] = ucfirst($csvRow[4]);
        $postfields["address2"] = ($csvRow[5]!=''?ucfirst($csvRow[5]):'');
        $postfields["city"] = ucfirst($csvRow[6]);
        $postfields["state"] = strtoupper($csvRow[7]);
        $postfields["postcode"] = $csvRow[8];
        $postfields["country"] = strtoupper($csvRow[9]);
        $postfields["phonenumber"] = preg_replace("/[^0-9]/","",$csvRow[10]);
        $postfields["password2"] = $csvRow[11];
        $postfields["language"] = $csvRow[12];
        $postfields["notes"] = stripslashes($csvRow[13]);
        $postfields["customfields"] = base64_encode(serialize(array(
                   "1" => ucfirst($csvRow[14]),
                   "6" => ucfirst($csvRow[15]),
                   "9" => ucfirst($csvRow[16])
                   )));
        
        
        $postfields["noemail"] = 1;

        if ($scriptActive==1) {
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_POST, 1);
            curl_setopt($ch, CURLOPT_TIMEOUT, 100);
            curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $postfields);
            $data = curl_exec($ch);

            $data = explode(";",$data);
            foreach ($data AS $temp) {
                $temp = explode("=",$temp);
                $results[$temp[0]] = $temp[1];
            }

            ob_flush();//Flush the data here
            if ($results["result"]=="success") {
                # Result was OK!
                echo $postfields["firstname"]." ".$postfields["lastname"]." was successfully added!";
            } else {
                # An error occured
                echo "The following error occured: ".$results["message"];
            }
            curl_close($ch);
        }
    }

    if ($scriptActive!=1) {
        echo "Import File: " . $csvFile . "<br><br>";
        echo '<pre>';
        print_r($csvRowArray);
        echo '</pre>';
    }

    ob_end_flush();

Hi, possible to provide step by step on how to achieve this?

Remitur · November 14, 2020

On 11/6/2020 at 8:59 PM, Bryan K said:

Hi, possible to provide step by step on how to achieve this?

It's a quite simple PHP script, but what it does may be rather dangerous,and you need to be ready to a rollback... So, if you need a "step by step how to", I guess it's much better and safer for you find an expert developer to help you... 😉

blakeh · September 28, 2021

On 10/22/2020 at 1:56 AM, Remitur said:

It's PHP... (even if it's missing the usual PHP open tag)

I guess it's stll working fine; when I wrote it, years ago, used it to import few thousands users, 500 users at each run ...

trying this script, getting

PHP Warning: Undefined array key 1 in /path to script/clientimport.php on line 87

Warning: Undefined array key 1 in /path to script/clientimport.php on line 87
PHP Warning: Undefined array key "result" in /path to script/clientimport.php on line 91
PHP Warning: Undefined array key "message" in /path to script/clientimport.php on line 96
PHP Warning: Undefined array key 1 in/path to script/clientimport.php on line 87

for each entry in the csv.

When I set the script active bool to 0 it appears to be fine and echos the records back correctly to the screen.

Any ideas?

Thanks.

edit: lines in my file:

87: $results[$temp[0]] = $temp[1];

91: if ($results["result"]=="success") {

96: echo "The following error occured: ".$results["message"];

I'm wondering if this is a php version issue?

Edited September 28, 2021 by blakeh

Md Rasel Khan · March 5, 2023

To import around 7k clients, I build a simple node js application.

Just convert your CSV file to a JSON file. You may use this site: https://www.convertcsv.com/csv-to-json.htm

Here is the Git repository link: https://github.com/immdraselkhan/whmcs-clients-import

Sign In

Import Clients To WHMCS Via CVS

Recommended Posts

mywebguy

Link to comment

Share on other sites

brian!

Link to comment

Share on other sites

Remitur

Link to comment

Share on other sites

JamesN

Link to comment

Share on other sites

Remitur

Link to comment

Share on other sites

Bryan K

Link to comment

Share on other sites

Remitur

Link to comment

Share on other sites

blakeh

Link to comment

Share on other sites

Md Rasel Khan

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members

Browse

Latest Activity

Blog

Technical Support

Important Information