Dolibarr ERP CRM Wiki - User contributions [en]

Importing customers automatically

2010-02-01T23:42:57Z

Kario:

[[Category:ImportsEn]]
{{ToTranslate}}

This page describes an example of development used to make imports in Dolibarr by coding. If you are looking for other methods, see [[Mass_imports]].

[edit] Data file

First you must create your data file and make sure it meets a specific structure:

This file should contain the following fields and must be separated by tabs. The easiest way is to work with excel and then save your document as a tab-delimited text file.

Column1: code_client

Column2: company name

Column3: supplement company name

Column 4: Address Line 1

Column 5: Address Line 2

Colonne6: Address Line 3

Colonne7: Zip Code

Colonne8: City

As you can see the address must be standardized (2 lines for name and 3 lines for the address + ZIP + City). During the import process the 3 address lines will be entered in the single "address" field of Dolibarr with a carriage return after each line. The same will applied for the company name and the company supplement name that will populate the single field "name" of Dolibarr with automatic carriage return.

Colonne9: telephone

Colonne10: fax

Colonne11: email

Colonne12: customer (1 per customer, prospect 2, 0 otherwise)

Colonne13: Provider (1 for supplier or 0 if not vendor)

Colonne14: legal form (See table values llx_c_forme_juridique to feed that column. You can leave this column blank)

Colonne15: type of business (See table values llx_c_typent to feed that column. You can leave this column blank)

Colonne16: Country code (See table values llx_c_pays to feed that column. You can leave this column blank)

Colonne17: url (url of the websites of companies. You can leave this column blank)

Do not add column's header in your file.

Once your columns are filled in, save your file in your data directory under the name societe.txt

= Import script file =

Copy/paste the content below in a new file and save as "import_client.php" under the directory /scripts

<source lang="php">
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>
<head>

<title>Title here!</title>

</head>

<body>

<?php

'''''$sql_host= "localhost";'''''

'''''$sql_login= "root";'''''

'''''$sql_pass= "";'''''

'''''$sql_base= "nomdevotrebasededonnées";'''''

$dbid = mysql_connect("$sql_host", "$sql_login", "$sql_pass");
mysql_select_db("$sql_base",$dbid);

if(file_exists("'''''../../../mysql/data/demopasspro/societe.txt'''''")) {

create table if not exists societe_tmp
(
code_client varchar(15),
nom varchar(60),
complement varchar(60),
address1 varchar(255),
address2 varchar(255),
address3 varchar(255),
cp varchar(10),
ville varchar(50),
tel varchar(20),
fax varchar(20),
email varchar(128),
client tinyint DEFAULT 0,
fournisseur tinyint DEFAULT 0,
fk_forme_juridique integer DEFAULT 0,
fk_typent integer DEFAULT 0,
fk_pays integer DEFAULT 0,
url varchar(255)

);

$query_content="delete from societe_tmp";
$result_content=mysql_query($query_content,$dbid);

$query_content="LOAD DATA INFILE 'societe.txt' into table societe_tmp fields terminated by '\t' ENCLOSED BY '\"' lines terminated by '\r\n'" ;

$result_content=mysql_query($query_content,$dbid);

$requete=mysql_query("select * from societe_tmp ") or die("Erreur SQL : $query<br/>".mysql_error());

$i=0;

while ($data=mysql_fetch_array($requete))
{
$i=$i+1;

$sql = "INSERT INTO llx_societe (datec, client, nom, address,cp,ville,tel,fax,email,fournisseur,fk_forme_juridique,fk_typent,code_client,fk_pays,url,fk_user_creat) ";

$valeur_concat='"'.date("Y-m-d H:m:s").'","'.$data['client'].'","'.$data['nom']." ".$data['complement'].'","'.$data['address1']."\\r\n".$data['address2']."\\r\n".$data['address3'].'","'.$data['cp'].'","'.$data['ville'].'","'.$data['tel'].'","'.$data['fax'].'","'.$data['email'].'","'.$data['fournisseur'].'","'.$data['fk_forme_juridique'].'","'.$data['fk_typent'].'","'.$data['code_client'].'","'.$data['fk_pays'].'","'.$data['url'].'","1"';

$sql .= "VALUES ($valeur_concat);";

$result_content=mysql_query($sql);
}

echo "Nb de clients importés:".$i;

}

?>
</body>
</html>
</source>

Edit the content of this file (the data to modify are in bold italics:

access to the database

path to data according to your tree

All you have to do is open this file with your browser and your data file will be imported.

Hoping it will help some people.

Importing customers automatically

2010-02-01T23:40:57Z

Kario:

[[Category:ImportsEn]]
{{ToTranslate}}

This page describes an example of development used to make imports in Dolibarr by coding. If you are looking for other methods, see [Mass imports].

[edit] Data file

First you must create your data file and make sure it meets a specific structure:

This file should contain the following fields and must be separated by tabs. The easiest way is to work with excel and then save your document as a tab-delimited text file.

Column1: code_client

Column2: company name

Column3: supplement company name

Column 4: Address Line 1

Column 5: Address Line 2

Colonne6: Address Line 3

Colonne7: Zip Code

Colonne8: City

As you can see the address must be standardized (2 lines for name and 3 lines for the address + ZIP + City). During the import process the 3 address lines will be entered in the single "address" field of Dolibarr with a carriage return after each line. The same will applied for the company name and the company supplement name that will populate the single field "name" of Dolibarr with automatic carriage return.

Colonne9: telephone

Colonne10: fax

Colonne11: email

Colonne12: customer (1 per customer, prospect 2, 0 otherwise)

Colonne13: Provider (1 for supplier or 0 if not vendor)

Colonne14: legal form (See table values llx_c_forme_juridique to feed that column. You can leave this column blank)

Colonne15: type of business (See table values llx_c_typent to feed that column. You can leave this column blank)

Colonne16: Country code (See table values llx_c_pays to feed that column. You can leave this column blank)

Colonne17: url (url of the websites of companies. You can leave this column blank)

Do not add column's header in your file.

Once your columns are filled in, save your file in your data directory under the name societe.txt

= Import script file =

Copy/paste the content below in a new file and save as "import_client.php" under the directory /scripts

<source lang="php">
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>
<head>

<title>Title here!</title>

</head>

<body>

<?php

'''''$sql_host= "localhost";'''''

'''''$sql_login= "root";'''''

'''''$sql_pass= "";'''''

'''''$sql_base= "nomdevotrebasededonnées";'''''

$dbid = mysql_connect("$sql_host", "$sql_login", "$sql_pass");
mysql_select_db("$sql_base",$dbid);

if(file_exists("'''''../../../mysql/data/demopasspro/societe.txt'''''")) {

create table if not exists societe_tmp
(
code_client varchar(15),
nom varchar(60),
complement varchar(60),
address1 varchar(255),
address2 varchar(255),
address3 varchar(255),
cp varchar(10),
ville varchar(50),
tel varchar(20),
fax varchar(20),
email varchar(128),
client tinyint DEFAULT 0,
fournisseur tinyint DEFAULT 0,
fk_forme_juridique integer DEFAULT 0,
fk_typent integer DEFAULT 0,
fk_pays integer DEFAULT 0,
url varchar(255)

);

$query_content="delete from societe_tmp";
$result_content=mysql_query($query_content,$dbid);

$query_content="LOAD DATA INFILE 'societe.txt' into table societe_tmp fields terminated by '\t' ENCLOSED BY '\"' lines terminated by '\r\n'" ;

$result_content=mysql_query($query_content,$dbid);

$requete=mysql_query("select * from societe_tmp ") or die("Erreur SQL : $query<br/>".mysql_error());

$i=0;

while ($data=mysql_fetch_array($requete))
{
$i=$i+1;

$sql = "INSERT INTO llx_societe (datec, client, nom, address,cp,ville,tel,fax,email,fournisseur,fk_forme_juridique,fk_typent,code_client,fk_pays,url,fk_user_creat) ";

$valeur_concat='"'.date("Y-m-d H:m:s").'","'.$data['client'].'","'.$data['nom']." ".$data['complement'].'","'.$data['address1']."\\r\n".$data['address2']."\\r\n".$data['address3'].'","'.$data['cp'].'","'.$data['ville'].'","'.$data['tel'].'","'.$data['fax'].'","'.$data['email'].'","'.$data['fournisseur'].'","'.$data['fk_forme_juridique'].'","'.$data['fk_typent'].'","'.$data['code_client'].'","'.$data['fk_pays'].'","'.$data['url'].'","1"';

$sql .= "VALUES ($valeur_concat);";

$result_content=mysql_query($sql);
}

echo "Nb de clients importés:".$i;

}

?>
</body>
</html>
</source>

Edit the content of this file (the data to modify are in bold italics:

access to the database

path to data according to your tree

All you have to do is open this file with your browser and your data file will be imported.

Hoping it will help some people.

Importing customers automatically

2010-02-01T23:33:24Z

Kario:

[[Category:ImportsEn]]
{{ToTranslate}}

This page describes an example of development used to make imports in Dolibarr by coding. If you are looking for other methods, see Mass imports.

[edit] Data file

First you must create your data file and make sure it meets a specific structure:

This file should contain the following fields and must be separated by tabs. The easiest way is to work with excel and then save your document as a tab-delimited text file.

Column1: code_client

Column2: company name

Column3: supplement company name

Column 4: Address Line 1

Column 5: Address Line 2

Colonne6: Address Line 3

Colonne7: Zip Code

Colonne8: City

As you can see the address must be standardized (2 lines for name and 3 lines for the address + ZIP + City). During the import process the 3 address lines will be entered in the single "address" field of Dolibarr with a carriage return after each line. The same will applied for the company name and the company supplement name that will populate the single field "name" of Dolibarr with automatic carriage return.

Colonne9: telephone

Colonne10: fax

Colonne11: email

Colonne12: customer (1 per customer, prospect 2, 0 otherwise)

Colonne13: Provider (1 for supplier or 0 if not vendor)

Colonne14: legal form (See table values llx_c_forme_juridique to feed that column. You can leave this column blank)

Colonne15: type of business (See table values llx_c_typent to feed that column. You can leave this column blank)

Colonne16: Country code (See table values llx_c_pays to feed that column. You can leave this column blank)

Colonne17: url (url of the websites of companies. You can leave this column blank)

Do not add column's header in your file.

Once your columns are filled in, save your file in your data directory under the name societe.txt

= Import script file =

Copy/paste the content below in a new file and save as "import_client.php" under the directory /scripts

<source lang="php">
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>
<head>

<title>Title here!</title>

</head>

<body>

<?php

'''''$sql_host= "localhost";'''''

'''''$sql_login= "root";'''''

'''''$sql_pass= "";'''''

'''''$sql_base= "nomdevotrebasededonnées";'''''

$dbid = mysql_connect("$sql_host", "$sql_login", "$sql_pass");
mysql_select_db("$sql_base",$dbid);

if(file_exists("'''''../../../mysql/data/demopasspro/societe.txt'''''")) {

create table if not exists societe_tmp
(
code_client varchar(15),
nom varchar(60),
complement varchar(60),
address1 varchar(255),
address2 varchar(255),
address3 varchar(255),
cp varchar(10),
ville varchar(50),
tel varchar(20),
fax varchar(20),
email varchar(128),
client tinyint DEFAULT 0,
fournisseur tinyint DEFAULT 0,
fk_forme_juridique integer DEFAULT 0,
fk_typent integer DEFAULT 0,
fk_pays integer DEFAULT 0,
url varchar(255)

);

$query_content="delete from societe_tmp";
$result_content=mysql_query($query_content,$dbid);

$query_content="LOAD DATA INFILE 'societe.txt' into table societe_tmp fields terminated by '\t' ENCLOSED BY '\"' lines terminated by '\r\n'" ;

$result_content=mysql_query($query_content,$dbid);

$requete=mysql_query("select * from societe_tmp ") or die("Erreur SQL : $query<br/>".mysql_error());

$i=0;

while ($data=mysql_fetch_array($requete))
{
$i=$i+1;

$sql = "INSERT INTO llx_societe (datec, client, nom, address,cp,ville,tel,fax,email,fournisseur,fk_forme_juridique,fk_typent,code_client,fk_pays,url,fk_user_creat) ";

$valeur_concat='"'.date("Y-m-d H:m:s").'","'.$data['client'].'","'.$data['nom']." ".$data['complement'].'","'.$data['address1']."\\r\n".$data['address2']."\\r\n".$data['address3'].'","'.$data['cp'].'","'.$data['ville'].'","'.$data['tel'].'","'.$data['fax'].'","'.$data['email'].'","'.$data['fournisseur'].'","'.$data['fk_forme_juridique'].'","'.$data['fk_typent'].'","'.$data['code_client'].'","'.$data['fk_pays'].'","'.$data['url'].'","1"';

$sql .= "VALUES ($valeur_concat);";

$result_content=mysql_query($sql);
}

echo "Nb de clients importés:".$i;

}

?>
</body>
</html>
</source>

Edit the content of this file (the data to modify are in bold italics:

access to the database

path to data according to your tree

All you have to do is open this file with your browser and your data file will be imported.

Hoping it will help some people.