Início / unix / Perguntas / 408189
Accepted
Thufir
Asked: 2017-12-02 05:19:32 +0800 CST

como importar clientes para dolibarr

  • 772

Tenho uma tabela tosca e pronta como:

mysql> 
mysql> show tables;
+----------------------+
| Tables_in_businesses |
+----------------------+
| 2017july             |
+----------------------+
1 row in set (0.00 sec)

mysql> 
mysql> describe 2017july;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| business     | varchar(45) | YES  |     | NULL    |       |
| product      | varchar(45) | YES  |     | NULL    |       |
| unit         | varchar(45) | YES  |     | NULL    |       |
| house        | varchar(45) | YES  |     | NULL    |       |
| road         | varchar(45) | YES  |     | NULL    |       |
| postal       | varchar(45) | YES  |     | NULL    |       |
| phnum        | varchar(45) | YES  |     | NULL    |       |
| emps         | varchar(45) | YES  |     | NULL    |       |
| town         | varchar(45) | YES  |     | NULL    |       |
| typebusiness | varchar(45) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
10 rows in set (0.01 sec)

mysql>

Exportar para o LibreOffice e outros não é problemático, mas as instruções de importação são apenas em francês. Como alternativa, o script PHP sugerido será importado de um arquivo de dados -- mas tem uma estrutura muito específica.

como devo modificar o esquema para caber no dolibarr?

Pode haver alguma ferramenta alternativa para importar clientes? Eu habilitei os módulos Projects/Opportunities/Leadse .Third parties

Dolibarr é um pouco mais sofisticado:

mysql> 
mysql> use dolibarr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> show tables;
+-----------------------------------------+
| Tables_in_dolibarr                      |
+-----------------------------------------+
| llx_accounting_account                  |
| llx_accounting_bookkeeping              |
| llx_accounting_fiscalyear               |
| llx_accounting_journal                  |
| llx_accounting_system                   |
| llx_actioncomm                          |
| llx_actioncomm_extrafields              |
| llx_actioncomm_resources                |
| llx_adherent                            |
| llx_adherent_extrafields                |
| llx_adherent_type                       |
| llx_adherent_type_extrafields           |
| llx_advtargetemailing                   |
| llx_bank                                |
| llx_bank_account                        |
| llx_bank_account_extrafields            |
| llx_bank_categ                          |
| llx_bank_class                          |
| llx_bank_url                            |
| llx_bookmark                            |
| llx_bordereau_cheque                    |
| llx_boxes                               |
| llx_boxes_def                           |
| llx_budget                              |
| llx_budget_lines                        |
| llx_c_accounting_category               |
| llx_c_action_trigger                    |
| llx_c_actioncomm                        |
| llx_c_availability                      |
| llx_c_barcode_type                      |
| llx_c_chargesociales                    |
| llx_c_civility                          |
| llx_c_country                           |
| llx_c_currencies                        |
| llx_c_departements                      |
| llx_c_ecotaxe                           |
| llx_c_effectif                          |
| llx_c_email_templates                   |
| llx_c_field_list                        |
| llx_c_format_cards                      |
| llx_c_forme_juridique                   |
| llx_c_holiday_types                     |
| llx_c_hrm_department                    |
| llx_c_hrm_function                      |
| llx_c_incoterms                         |
| llx_c_input_method                      |
| llx_c_input_reason                      |
| llx_c_lead_status                       |
| llx_c_paiement                          |
| llx_c_paper_format                      |
| llx_c_payment_term                      |
| llx_c_price_expression                  |
| llx_c_price_global_variable             |
| llx_c_price_global_variable_updater     |
| llx_c_propalst                          |
| llx_c_prospectlevel                     |
| llx_c_regions                           |
| llx_c_revenuestamp                      |
| llx_c_shipment_mode                     |
| llx_c_stcomm                            |
| llx_c_tva                               |
| llx_c_type_contact                      |
| llx_c_type_fees                         |
| llx_c_type_resource                     |
| llx_c_typent                            |
| llx_c_units                             |
| llx_c_ziptown                           |
| llx_categorie                           |
| llx_categorie_account                   |
| llx_categorie_contact                   |
| llx_categorie_fournisseur               |
| llx_categorie_lang                      |
| llx_categorie_member                    |
| llx_categorie_product                   |
| llx_categorie_project                   |
| llx_categorie_societe                   |
| llx_categorie_user                      |
| llx_categories_extrafields              |
| llx_chargesociales                      |
| llx_commande                            |
| llx_commande_extrafields                |
| llx_commande_fournisseur                |
| llx_commande_fournisseur_dispatch       |
| llx_commande_fournisseur_extrafields    |
| llx_commande_fournisseur_log            |
| llx_commande_fournisseurdet             |
| llx_commande_fournisseurdet_extrafields |
| llx_commandedet                         |
| llx_commandedet_extrafields             |
| llx_const                               |
| llx_contrat                             |
| llx_contrat_extrafields                 |
| llx_contratdet                          |
| llx_contratdet_extrafields              |
| llx_contratdet_log                      |
| llx_cronjob                             |
| llx_deplacement                         |
| llx_document_model                      |
| llx_don                                 |
| llx_don_extrafields                     |
| llx_ecm_directories                     |
| llx_ecm_files                           |
| llx_element_contact                     |
| llx_element_element                     |
| llx_element_resources                   |
| llx_element_tag                         |
| llx_entrepot                            |
| llx_establishment                       |
| llx_event_element                       |
| llx_events                              |
| llx_expedition                          |
| llx_expedition_extrafields              |
| llx_expeditiondet                       |
| llx_expeditiondet_batch                 |
| llx_expeditiondet_extrafields           |
| llx_expensereport                       |
| llx_expensereport_det                   |
| llx_expensereport_extrafields           |
| llx_export_compta                       |
| llx_export_model                        |
| llx_extrafields                         |
| llx_facture                             |
| llx_facture_extrafields                 |
| llx_facture_fourn                       |
| llx_facture_fourn_det                   |
| llx_facture_fourn_det_extrafields       |
| llx_facture_fourn_extrafields           |
| llx_facture_rec                         |
| llx_facturedet                          |
| llx_facturedet_extrafields              |
| llx_facturedet_rec                      |
| llx_fichinter                           |
| llx_fichinter_extrafields               |
| llx_fichinterdet                        |
| llx_fichinterdet_extrafields            |
| llx_holiday                             |
| llx_holiday_config                      |
| llx_holiday_logs                        |
| llx_holiday_users                       |
| llx_import_model                        |
| llx_links                               |
| llx_livraison                           |
| llx_livraison_extrafields               |
| llx_livraisondet                        |
| llx_livraisondet_extrafields            |
| llx_loan                                |
| llx_localtax                            |
| llx_mailing                             |
| llx_mailing_cibles                      |
| llx_menu                                |
| llx_multicurrency                       |
| llx_multicurrency_rate                  |
| llx_notify                              |
| llx_notify_def                          |
| llx_oauth_state                         |
| llx_oauth_token                         |
| llx_opensurvey_comments                 |
| llx_opensurvey_formquestions            |
| llx_opensurvey_sondage                  |
| llx_opensurvey_user_formanswers         |
| llx_opensurvey_user_studs               |
| llx_overwrite_trans                     |
| llx_paiement                            |
| llx_paiement_facture                    |
| llx_paiementcharge                      |
| llx_paiementfourn                       |
| llx_paiementfourn_facturefourn          |
| llx_payment_donation                    |
| llx_payment_expensereport               |
| llx_payment_loan                        |
| llx_payment_salary                      |
| llx_prelevement_bons                    |
| llx_prelevement_facture                 |
| llx_prelevement_facture_demande         |
| llx_prelevement_lignes                  |
| llx_prelevement_rejet                   |
| llx_printing                            |
| llx_product                             |
| llx_product_association                 |
| llx_product_batch                       |
| llx_product_customer_price              |
| llx_product_customer_price_log          |
| llx_product_extrafields                 |
| llx_product_fournisseur_price           |
| llx_product_fournisseur_price_log       |
| llx_product_lang                        |
| llx_product_lot                         |
| llx_product_lot_extrafields             |
| llx_product_price                       |
| llx_product_price_by_qty                |
| llx_product_pricerules                  |
| llx_product_stock                       |
| llx_product_warehouse_properties        |
| llx_projet                              |
| llx_projet_extrafields                  |
| llx_projet_task                         |
| llx_projet_task_extrafields             |
| llx_projet_task_time                    |
| llx_propal                              |
| llx_propal_extrafields                  |
| llx_propal_merge_pdf_product            |
| llx_propaldet                           |
| llx_propaldet_extrafields               |
| llx_resource                            |
| llx_resource_extrafields                |
| llx_rights_def                          |
| llx_societe                             |
| llx_societe_address                     |
| llx_societe_commerciaux                 |
| llx_societe_extrafields                 |
| llx_societe_log                         |
| llx_societe_prices                      |
| llx_societe_remise                      |
| llx_societe_remise_except               |
| llx_societe_rib                         |
| llx_socpeople                           |
| llx_socpeople_extrafields               |
| llx_stock_mouvement                     |
| llx_subscription                        |
| llx_supplier_proposal                   |
| llx_supplier_proposal_extrafields       |
| llx_supplier_proposaldet                |
| llx_supplier_proposaldet_extrafields    |
| llx_tva                                 |
| llx_user                                |
| llx_user_alert                          |
| llx_user_clicktodial                    |
| llx_user_employment                     |
| llx_user_extrafields                    |
| llx_user_param                          |
| llx_user_rib                            |
| llx_user_rights                         |
| llx_usergroup                           |
| llx_usergroup_extrafields               |
| llx_usergroup_rights                    |
| llx_usergroup_user                      |
| llx_website                             |
| llx_website_page                        |
+-----------------------------------------+
238 rows in set (0.00 sec)

mysql>

Talvez copiar a estrutura llx_societee carregar os dados lá? Eu nem tenho certeza do que alguns dos nomes das tabelas significam - e isso parece bastante estranho. Deve haver uma ferramenta ou idioma que estou perdendo.

Ah.... não sabia que tinha que habilitar o Data Importsplugin para pegar um assistente:

importação de dados

php mysql

2 respostas

  1. Best Answer

    Aqui está a página traduzida magicamente para o inglês: https://translate.google.com/translate?hl=en&sl=auto&tl=en&u=https%3A%2F%2Fwiki.dolibarr.org%2Findex.php%2FImporter_des_donn%25C3%25A9es_avec_OpenOffice .

    A maneira mais rápida seria executar um comando SQL semelhante a este:

    INSERT INTO dolibarr.llx_societe SELECT /*choose fields here*/ from businesses.2017july;

    Ou use o assistente descrito em https://www.dolibarr.org/57-documentation/features/231-import-export .

    Existem algumas dicas em https://www.dolibarr.org/forum/12-howto-help/27371-why-is-importing-so-impossibly-difficult#31833 .

    • 1

  2. Para mim, pelo menos, demorou um pouco para acertar:

    mysql> 
mysql> 
mysql> describe businesses.2017july;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| nom          | varchar(45) | YES  |     | NULL    |       |
| product      | varchar(45) | YES  |     | NULL    |       |
| unit         | varchar(45) | YES  |     | NULL    |       |
| house        | varchar(45) | YES  |     | NULL    |       |
| road         | varchar(45) | YES  |     | NULL    |       |
| postal       | varchar(45) | YES  |     | NULL    |       |
| phone        | varchar(45) | YES  |     | NULL    |       |
| emps         | varchar(45) | YES  |     | NULL    |       |
| town         | varchar(45) | YES  |     | NULL    |       |
| typebusiness | varchar(45) | YES  |     | NULL    |       |
| client       | int(11)     | YES  |     | 2       |       |
+--------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> 
mysql> select nom, phone, code_client, client from dolibarr.llx_societe limit 2;
+-------+-------+-------------+--------+
| nom   | phone | code_client | client |
+-------+-------+-------------+--------+
| alpha | NULL  | CU1711-0001 |      2 |
| beta  | NULL  | CU1711-0002 |      2 |
+-------+-------+-------------+--------+
2 rows in set (0.00 sec)

mysql> 
mysql> describe dolibarr.llx_societe;
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
| Field                   | Type         | Null | Key | Default           | Extra                       |
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
| rowid                   | int(11)      | NO   | PRI | NULL              | auto_increment              |
| nom                     | varchar(128) | YES  |     | NULL              |                             |
| name_alias              | varchar(128) | YES  |     | NULL              |                             |
| entity                  | int(11)      | NO   |     | 1                 |                             |
| ref_ext                 | varchar(128) | YES  |     | NULL              |                             |
| ref_int                 | varchar(60)  | YES  |     | NULL              |                             |
| statut                  | tinyint(4)   | YES  |     | 0                 |                             |
| parent                  | int(11)      | YES  |     | NULL              |                             |
| tms                     | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| datec                   | datetime     | YES  |     | NULL              |                             |
| status                  | tinyint(4)   | YES  |     | 1                 |                             |
| code_client             | varchar(24)  | YES  | MUL | NULL              |                             |
| code_fournisseur        | varchar(24)  | YES  | MUL | NULL              |                             |
| code_compta             | varchar(24)  | YES  |     | NULL              |                             |
| code_compta_fournisseur | varchar(24)  | YES  |     | NULL              |                             |
| address                 | varchar(255) | YES  |     | NULL              |                             |
| zip                     | varchar(25)  | YES  |     | NULL              |                             |
| town                    | varchar(50)  | YES  |     | NULL              |                             |
| fk_departement          | int(11)      | YES  |     | 0                 |                             |
| fk_pays                 | int(11)      | YES  |     | 0                 |                             |
| fk_account              | int(11)      | YES  |     | 0                 |                             |
| phone                   | varchar(20)  | YES  |     | NULL              |                             |
| fax                     | varchar(20)  | YES  |     | NULL              |                             |
| url                     | varchar(255) | YES  |     | NULL              |                             |
| email                   | varchar(128) | YES  |     | NULL              |                             |
| skype                   | varchar(255) | YES  |     | NULL              |                             |
| fk_effectif             | int(11)      | YES  |     | 0                 |                             |
| fk_typent               | int(11)      | YES  |     | 0                 |                             |
| fk_forme_juridique      | int(11)      | YES  |     | 0                 |                             |
| fk_currency             | varchar(3)   | YES  |     | NULL              |                             |
| siren                   | varchar(128) | YES  |     | NULL              |                             |
| siret                   | varchar(128) | YES  |     | NULL              |                             |
| ape                     | varchar(128) | YES  |     | NULL              |                             |
| idprof4                 | varchar(128) | YES  |     | NULL              |                             |
| idprof5                 | varchar(128) | YES  |     | NULL              |                             |
| idprof6                 | varchar(128) | YES  |     | NULL              |                             |
| tva_intra               | varchar(20)  | YES  |     | NULL              |                             |
| capital                 | double       | YES  |     | NULL              |                             |
| fk_stcomm               | int(11)      | NO   |     | 0                 |                             |
| note_private            | text         | YES  |     | NULL              |                             |
| note_public             | text         | YES  |     | NULL              |                             |
| model_pdf               | varchar(255) | YES  |     | NULL              |                             |
| prefix_comm             | varchar(5)   | YES  | MUL | NULL              |                             |
| client                  | tinyint(4)   | YES  |     | 0                 |                             |
| fournisseur             | tinyint(4)   | YES  |     | 0                 |                             |
| supplier_account        | varchar(32)  | YES  |     | NULL              |                             |
| fk_prospectlevel        | varchar(12)  | YES  |     | NULL              |                             |
| fk_incoterms            | int(11)      | YES  |     | NULL              |                             |
| location_incoterms      | varchar(255) | YES  |     | NULL              |                             |
| customer_bad            | tinyint(4)   | YES  |     | 0                 |                             |
| customer_rate           | double       | YES  |     | 0                 |                             |
| supplier_rate           | double       | YES  |     | 0                 |                             |
| fk_user_creat           | int(11)      | YES  | MUL | NULL              |                             |
| fk_user_modif           | int(11)      | YES  | MUL | NULL              |                             |
| remise_client           | double       | YES  |     | 0                 |                             |
| mode_reglement          | tinyint(4)   | YES  |     | NULL              |                             |
| cond_reglement          | tinyint(4)   | YES  |     | NULL              |                             |
| mode_reglement_supplier | tinyint(4)   | YES  |     | NULL              |                             |
| cond_reglement_supplier | tinyint(4)   | YES  |     | NULL              |                             |
| fk_shipping_method      | int(11)      | YES  |     | NULL              |                             |
| tva_assuj               | tinyint(4)   | YES  |     | 1                 |                             |
| localtax1_assuj         | tinyint(4)   | YES  |     | 0                 |                             |
| localtax1_value         | double(6,3)  | YES  |     | NULL              |                             |
| localtax2_assuj         | tinyint(4)   | YES  |     | 0                 |                             |
| localtax2_value         | double(6,3)  | YES  |     | NULL              |                             |
| barcode                 | varchar(255) | YES  | MUL | NULL              |                             |
| fk_barcode_type         | int(11)      | YES  |     | 0                 |                             |
| price_level             | int(11)      | YES  |     | NULL              |                             |
| outstanding_limit       | double(24,8) | YES  |     | NULL              |                             |
| default_lang            | varchar(6)   | YES  |     | NULL              |                             |
| logo                    | varchar(255) | YES  |     | NULL              |                             |
| canvas                  | varchar(32)  | YES  |     | NULL              |                             |
| import_key              | varchar(14)  | YES  |     | NULL              |                             |
| webservices_url         | varchar(255) | YES  |     | NULL              |                             |
| webservices_key         | varchar(128) | YES  |     | NULL              |                             |
| fk_multicurrency        | int(11)      | YES  |     | NULL              |                             |
| multicurrency_code      | varchar(255) | YES  |     | NULL              |                             |
+-------------------------+--------------+------+-----+-------------------+-----------------------------+
77 rows in set (0.00 sec)

mysql> 
mysql> insert into dolibarr.llx_societe nom, phone, client select nom, phone, client from businesses.2017july;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nom, phone, client select nom, phone, client from businesses.2017july' at line 1
mysql> 
mysql> insert into dolibarr.llx_societe select nom, phone, client from businesses.2017july;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> insert into dolibarr.llx_societe (nom, phone, client) select nom, phone, client from businesses.2017july;
Query OK, 13479 rows affected (3.28 sec)
Records: 13479  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> 
mysql> select count(*) from llx_societe;
ERROR 1046 (3D000): No database selected
mysql> 
mysql> select count(*) from dolibarr.llx_societe;
+----------+
| count(*) |
+----------+
|    13485 |
+----------+
1 row in set (0.00 sec)

mysql> 
mysql>

    Está no banco de dados. Resta saber se dolibarr vai reclamar. O code_clientcampo é NULLonde deveria ter valores, eu acho.

    • 0

relate perguntas