Importing and exporting data¶
Every form in Open ERP has a standard mechanism for importing data from a CSV file through the client user interface. That's the same format as used in the language translations.
You have access to the Import and Export functions in the web client on a single form view in read- only mode – you can't reach Import or Export in any other view or when the form is editable. If you're using the GTK client you can find the functions from the top menu Form ‣ Import... and Form ‣ Export....
The CSV file format is a text format compatible with most spreadsheet programs (such as OpenOffice Calc and Microsoft Excel) and is easily editable as a worksheet. The first line contains the name of the field in the form. All the subsequent lines are data, aligned in their respective columns.
Exporting OpenERP data to CSV¶
通过在演示数据中导出一个适度复杂的合作伙伴和他们的地址数据，来开始探索 OpenERP CSV文件的使用。
Go to Partners ‣ Partners for the list of partners and then scroll to the bottom of the list to click the Export link. This pops up the Export Data dialog box. Select the following fields:
You can either select and add them one at a time, or Ctrl-click them and add the multiple selection - the order in which you select them is the order in which they'll be displayed.
Then click Export and save the resulting data.csv file somewhere accessible - perhaps your Desktop. You can open that file in a spreadsheet program or a text editor.
You'll see that you have a list of partners, with the name and city of each partner's contacts alongside. In the couple of cases where there is more than one address, the partner name is left out. So it is important to note that the order of entries is critical - do not sort that list!
There is a limit to the number of items you can export in the clients - it's the number you can actually see and that is limited to a maximum of 100 in the web client, but is arbitrary in the GTK client.
So if you want to export everything, use the GTK client. Set the export limit to an arbitrarily large figure (using the + button to expose the Parameters and Limit fields), then click Form ‣ Export data..., set up the fields to export, and then prepare to wait.
Importing CSV data to OpenERP¶
Use this export file as a template for an import file by deleting all of the data and using new data (here you'll just import new data alongside the demonstration data, but the principle is the same for a blank database).
Miles A Minute
From the list of partners, click the Import button and then in the Import Data window click Open to search for and import the new data.csv file. The web client automatically matches column names but the GTK client requires that you click the Auto detect button.
You'll get a dialog box showing that you have imported 2 objects, and you can see the new partners and partner addresses when you refresh the list on screen.
The CSV format for complex database structures¶
When you import data you have to overcome the problem of representing a database structure in .csv flat files. To do this, two solutions are possible in Open ERP:
importing a CSV file that's been structured in a particular way to enable you to load several different database tables from a single file (such as partners and partner contacts in one CSV file, as you have just done above),
You can also import CSV data in through the server interface. The file format is the same, but column headings differ slightly. When importing through the user interface it checks that the column heading names match the names seen in the forms on the user interface itself. In contrast, when importing through the server the column heading names must match the internal names of the fields.
Start by building the header of the CSV file. Open the import tool on the object that you're interested in and select the fields that you want to import into your Open ERP database. You must include every field that's colored in blue because those fields are required (unless you know that they get filled by default with an appropriate value), and also any other field that's important to you.
Use the field names as the column names in the first line of your CSV file, adding one field per column. If your CSV file has these names in the first line then when you import your CSV file, Open ERP will automatically match the column name to the field name of the table. When you've created your CSV file you'll do that by clicking the Nothing button to clear the Fields to Import, then select your CSV file by browsing for a File to import, and then clicking the Auto Detect button.
To import CSV data that matches your database structure, you should distinguish between the following types of field in the Open ERP interface: many-to-many fields (between multiple sources and destinations), many-to-one fields (from multiple sources to a single destination), and one-to- many fields (from a single origin to multiple destinations).
Each of these types is described in relation to a foreground table – the table whose entry form you're viewing and whose entries would be updated by a simple CSV file.
只是因为这些关系字段出现在前台表，并不意味着相关的表上有一个反向字段 - 但也有可能。
So there is no one-to-many field in the User form to reflect the many-to-one user_id Salesman field in the Partner form, but there is a many-to-one partner_id Partner field in the Partner contact form to reflect the one-to-many child_ids Partner contacts field in the Partner form.
Many-to-one fields represent a relationship between the foreground table and another table in the database where the foreground table has a single entry for the other table. Open ERP tries to link the new record in the foreground table with the field in the other table by matching the field values.
If you're working on the server side you can use identifiers rather than the names of resources to link tables. To do this you import the first file (for example, Products) with a column named id in your CSV file that contains a unique identifier for each product. This could be an incrementing number.
When you import other files which link to the first table, you can use the identifier in preference to the names (so, for example, when you're saving inventory you can use product:idinstead of the product name). You then don't need any complex conversion to create links between the two tables.
This considerably simplifies the importation of another database into Open ERP. You just create a linking id column for each table that you're importing that contains the identifier used in the first table.
Many-to-many字段负责 就像many-to-one 字段试图在表之间重建关系：通过姓名搜索或者使用标识符
One-to-many fields are a bit different. Take as an example the Partner Contacts field in the Partner form, which contains all of the linked contacts.
To import such a field you don't have to link to an existing entry in another table, but can instead create and link to several partner contacts using the same file. You can then specify several values for different fields linked to that object by the one-to-many field. Each field must be put in a column of the table, and the title of that column must be expressed in the form field_one-to- many/field_linked-object . The partner data you imported earlier took that form.
Symmetry in relation fields
Depending on the structure of your data it can be easier to use the one-to-many form or the many- to-one form in relating two tables, so long as the relevant fields exist on both ends of the relationship.
import one partner with different contact in a single file (one-to-many),
import the partners first, and then contacts with the field linking to the partner in a many-to- one form).
Another example of a CSV import file¶
To illustrate data importing, you can see another example below. First import partner categories, and then import some partners and their contacts along with links to the categories just created. Although you can create new contacts at the same time as creating partners (because you can do this for one-to-many relations), you can't create new categories this way (because they use many-to-many relations). You must create new categories in a separate step.
Partner categories file
在第一行，“My Name”和“Parent Category”是列标题 相当于“合作伙伴分类”表单的字段名。
Column A is for the different partner categories and Column B indicates if that category has a parent category. If Column B is blank then the category sits at the top level.
Save spreadsheet file in CSV format – separated by commas – and name the file categories.csv.
In Open ERP, select Partners ‣ Configuration ‣ Categories ‣ Edit Categories.
Click Import (to the bottom left of the list) to bring up the Import Data dialog box, in which you'll find the ist of fields that can be imported.
Click Browse... on the File to import field and select the CSV file you just created, categories.csv Then click Auto Detect to atch the column names in the CSV file with the field names available in:guilabel:Partner Categories.
Click Import at the bottom-right of the dialog box to load your data. You should get the message 4 objects imported in a new dialog box. Close both this and the Import Data dialog box to return to the original page.
Click Partners ‣ Partners by category to view the tree of categories, including the new Quality branch that you loaded.
Here's how to create new partners with more than one contact, as you did before, and how to link them to these new categories:
Partner data file - partners.csv
In OpenERP, select Partners ‣ Partners then import the file that you've just saved. You'll get a message confirming that you've imported and saved the data.
Verify that you've imported the data. A new partner should have appeared (NoirAdvertising), with a salesman (Administrator), two contacts (George Black and Jean Green) and two categories (Silver and Gold).
Exporting data in other forms¶
Open ERP's generic export mechanism lets you easily export any of your data to any location on your system. You're not restricted to what you can export, although you can restrict who can export that data using the rights management facilities discussed above.
You can use this to export your data into spreadsheets or into other systems such as specialist accounts packages. The export format is usually in the CSV format but you can also connect directly to Microsoft Excel using Microsoft's COM mechanism.
Access to the database
Developers can also use other techniques to automatically access the Open ERP database. The two most useful are:
使用XML-RPC web service，
If you want to enter data into Open ERP manually, you should use the Module Recorder, described in the first section of this chapter.
By doing that you'll generate a module that can easily be reused in different databases. Then if there are problems with a database you'll be able to reinstall the data module you generated with all of the entries and modifications you made for this system.