我有一个粗略且准备好的表格：
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>
导出到 LibreOffice 等没有问题，但导入方向是仅有的法语。或者，建议的 PHP脚本将从数据文件导入——但具有非常特定的结构。
我应该如何修改架构以适应 dolibarr？
可能有一些替代工具来导入客户吗？我已经启用了Projects/Opportunities/Leads和Third parties模块。
多利巴尔是一个少量更复杂：
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>
也许复制结构llx_societe并加载数据？我什至不确定一些表名的含义 - 这看起来很尴尬。我一定缺少某种工具或习惯用法。
啊....不知道我必须启用Data Imports插件才能获得向导：
答案1
最快的方法是运行类似如下的 SQL 命令：
INSERT INTO dolibarr.llx_societe SELECT /*choose fields here*/ from businesses.2017july;
或者使用此处描述的向导https://www.dolibarr.org/57-documentation/features/231-import-export。
有一些提示https://www.dolibarr.org/forum/12-howto-help/27371-why-is-importing-so-impossible-difficult#31833。
答案2
至少对我来说，花了一些时间才把它做好：
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>
它在数据库中。多利巴尔是否会抱怨还有待观察。我认为这个code_client领域才是NULL它应该有价值的地方。