Eu tenho uma tabela aproximada 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>
A exportação para o LibreOffice et al não é problemática, mas as direções de importação são somente em francês. Como alternativa, o script sugerido do PHP script será importado de um arquivo de dados, mas tem uma estrutura muito específica.
como devo modificar o esquema para se adequar ao dolibarr?
Pode haver alguma ferramenta alternativa para importar clientes? Eu habilitei os módulos Projects/Opportunities/Leads e Third parties .
Dolibarr é um bit 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 para llx_societe e 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 está faltando.
Ah .... não sabia que tinha que ativar o Data Imports plugin para obter um assistente:
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. Se dolibarr vai reclamar continua a ser visto. O campo code_client é NULL , onde deveria ter valores, eu acho.
Tags mysql libreoffice php database sql