Import data with LibreOffice: Difference between revisions

From Dolibarr ERP CRM Wiki
Jump to navigation Jump to search
(Initial page creation - still needs formatting and screenshots in English)
(Added TODO for this page)
Line 6: Line 6:
<!-- END interlang links -->
<!-- END interlang links -->


{{ToTranslate}}<br />
{{ToTranslate}}<br /><!-- TODO
Screenshots in English
Page formatting
Links -->


= Pre-requisites =
=Pre-requisites=
First make sure you have everything you need, OpenOffice, Java Runtime Environment, and the JDBC connector for MySql. Generally, you can follow this tutorial on the OpenOffice site to set up the connector.
First make sure you have everything you need, OpenOffice, Java Runtime Environment, and the JDBC connector for MySql. Generally, you can follow this tutorial on the OpenOffice site to set up the connector.


Line 15: Line 18:
'''We will take the example of importing Customers / Suppliers.'''
'''We will take the example of importing Customers / Suppliers.'''


= Create the customer table =
=Create the customer table=
First you need to prepare a summary table of the data to import.
First you need to prepare a summary table of the data to import.


Line 24: Line 27:
The principle is simple, whatever the table to import (see the complete list), just put the fields of this table in the first line. Then fill in the other lines with your data. Save your file (it's more careful), but leave it open.
The principle is simple, whatever the table to import (see the complete list), just put the fields of this table in the first line. Then fill in the other lines with your data. Save your file (it's more careful), but leave it open.


= Create an OpenOffice link to the Dolibarr database =
=Create an OpenOffice link to the Dolibarr database=
Then create a new OOo database, File> New> Database
Then create a new OOo database, File> New> Database


Line 47: Line 50:
'''Please note: everything you do in OpenOffice Base will also be done on your MySQL database, OpenOffice Base serving here as an interface only.'''
'''Please note: everything you do in OpenOffice Base will also be done on your MySQL database, OpenOffice Base serving here as an interface only.'''


= Import of the customer table in the Dolibarr database =
=Import of the customer table in the Dolibarr database=
Go back to the data table (under Calc), select all your columns and rows, and make a Copy (CRTL + C)
Go back to the data table (under Calc), select all your columns and rows, and make a Copy (CRTL + C)


Line 60: Line 63:
Depending on the size of your Calc table you will have to be a little patient, for 1500 entries you have to wait 2-3 minutes. So be patient.
Depending on the size of your Calc table you will have to be a little patient, for 1500 entries you have to wait 2-3 minutes. So be patient.


= Conclusion =
=Conclusion=
This tutorial works for other tables (products for example), you must always be sure to fill in the 'vital' fields. You can also modify the data by double-clicking on the table of your choice, it's faster than with the Dolibarr interface, but be careful, we modify the data in the database directly, don't forget.
This tutorial works for other tables (products for example), you must always be sure to fill in the 'vital' fields. You can also modify the data by double-clicking on the table of your choice, it's faster than with the Dolibarr interface, but be careful, we modify the data in the database directly, don't forget.


Line 67: Line 70:
[[Special:Categories|Category]]:
[[Special:Categories|Category]]:


* [[:Category:Imports|Imports]]
*[[:Category:Imports|Imports]]


Available in french : [[Importer_des_données_avec_OpenOffice]]
Available in french : [[Importer_des_données_avec_OpenOffice]]
or in spanish : [[Importacion con OpenOffice]]
or in spanish : [[Importacion con OpenOffice]]

Revision as of 13:19, 12 June 2020

En verysmall.png Page waiting for translation. To translate, create an account, go back and clic on "Modify".
Fr verysmall.png Page en attente de traduction. Pour traduire, créez un compte, revenez et cliquez sur "Modifier".
Es verysmall.png Página a traducir. Para traducirla, cree una cuenta, vuelva a la página y haga clic en "editar".
It verysmall.png Pagina da tradurre. Per tradurla, crea un utente, torna indietro e clicca su "modifica".
Pt verysmall.png Página a aguardar por tradução. Para traduzir, crie uma conta, volte atrás e clique em "Modificar".
De verysmall.png Seite wartet auf Übersetzung. Um Übersetzung zu erstellen, richte einen Account ein, geh zurück und klicke auf "bearbeiten".
Zh verysmall.png 页面等待翻译。若要翻译本页,先创建一个帐户、登录并返回本页后单击“编辑”。


Pre-requisites

First make sure you have everything you need, OpenOffice, Java Runtime Environment, and the JDBC connector for MySql. Generally, you can follow this tutorial on the OpenOffice site to set up the connector.

If your base is not local, check that port 3306 of the Mysql server is accessible from the internet and that the user of the base has access rights from the IP address where you make the requests. (different from localhost)

We will take the example of importing Customers / Suppliers.

Create the customer table

First you need to prepare a summary table of the data to import.

Here is a blank database to fill in to list your third parties: Llx_societe.ods

Here's another blank base for your physical third party contacts: Llx_socpeople.ods

The principle is simple, whatever the table to import (see the complete list), just put the fields of this table in the first line. Then fill in the other lines with your data. Save your file (it's more careful), but leave it open.

Create an OpenOffice link to the Dolibarr database

Then create a new OOo database, File> New> Database

Check "Connect to an existing database" Select MySQL from the drop-down list.

Click on Next

Select Connection using JDBC and do Next

Fill in the fields as below (make the name of your Dolibarr database and the address of the MySQL server)

To enter your MySQL username, check Password required (if it is, but I hope so)

There, nothing to change, do Finish

Save your base according to the name that suits you.

You will be asked for the Root password when connecting to the base.

You are now directly connected to your Dolibarr database on MySQL.

Please note: everything you do in OpenOffice Base will also be done on your MySQL database, OpenOffice Base serving here as an interface only.

Import of the customer table in the Dolibarr database

Go back to the data table (under Calc), select all your columns and rows, and make a Copy (CRTL + C)

Return to Base and now select the llx.societe table (just click on it, no need to open it)

Just make a Paste (CRTL + V)

A window appears, do not change anything and click on Next

New window, make sure that the fields correspond well and click on Create

Depending on the size of your Calc table you will have to be a little patient, for 1500 entries you have to wait 2-3 minutes. So be patient.

Conclusion

This tutorial works for other tables (products for example), you must always be sure to fill in the 'vital' fields. You can also modify the data by double-clicking on the table of your choice, it's faster than with the Dolibarr interface, but be careful, we modify the data in the database directly, don't forget.

It also works whether it is under Linux (tested under Ubuntu, but I don't see why it won't work under other distributions) or under Windows.

Category:

Available in french : Importer_des_données_avec_OpenOffice or in spanish : Importacion con OpenOffice