Jump to content

Import Clients To WHMCS Via CVS


Recommended Posts

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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();

 

Link to comment
Share on other sites

  • 1 year later...
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!

Link to comment
Share on other sites

  • 2 weeks later...
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 ...

 

Link to comment
Share on other sites

  • 3 weeks later...
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?

Link to comment
Share on other sites

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... 😉

Link to comment
Share on other sites

  • 10 months later...
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 by blakeh
Link to comment
Share on other sites

  • 1 year later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated