This is the documentation for older versions of Odoo (formerly OpenERP).

See the new Odoo user documentation.

See the new Odoo technical documentation.

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:

  • Name,

  • Contacts 菜单下边的:guilabel:`Contact Name`子菜单

  • Contacts 菜单下边的:guilabel:`City``子菜单 。

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!

小技巧

List limits

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).

例如:要导入几个你指定了名字和城市联系人的合作伙伴 ,能将从导出文件创建下面的CSV文件:

Example of importing partner address fields

名称

联系人/联系人名称

联系人/城市

全局技术

全局Graham 算法

Athens

 

万达国际

Rome

 

Emerson Earth

New York

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),

  • 导入几个CSV文件,每个对应到一个特定的数据库表,表之间有明确的连接。

注解

Server-side importing

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.

/doc_static/5.0/_images/csv_column_select.png

选择需要从CSV文件导入的字段

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).

注解

Foreground table

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.

看看下面的截屏,找出差异

/doc_static/5.0/_images/csv_many2one.png

many2one字段: 业务伙伴的销售员

/doc_static/5.0/_images/csv_many2many.png

many2many字段: 业务伙伴分类

/doc_static/5.0/_images/csv_one2many.png

one2many字段: 业务伙伴联系人

CSV文件中所有其他字段 被编码为每一列中只有一个文本字符串。

Many-to-one fields

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.

小技巧

Field identifiers

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 fields

Many-to-many字段负责 就像many-to-one 字段试图在表之间重建关系:通过姓名搜索或者使用标识符

单个many-to-many 字段有多个可能的值。因此,一个合作伙伴能够被授予几个关联的类别。你必须用逗号分隔不同的值。

One-to-many fields

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

开始在一个CSV文件中创建合作伙伴类别:

  1. 在你的电子表格程序建立下表:

    Partner categories file
     

    Column A

    Column B

    Line 1

    Category Name

    Parent Category

    Line 2

    Quality

     

    Line 3

    Gold

    Quality

    Line 4

    Silver

    Quality

    Line 5

    Bronze

    Quality

    在第一行,“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.

  2. Save spreadsheet file in CSV format – separated by commas – and name the file categories.csv.

  3. In Open ERP, select Partners ‣ Configuration ‣ Categories ‣ Edit Categories.

  4. 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.

  5. 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.

  6. 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.

  7. Click Partners ‣ Partners by category to view the tree of categories, including the new Quality branch that you loaded.

New partners

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:

  1. 输入下表进入你的电子表格程序

    Partner data file - partners.csv
     

    Column A

    Column B

    Column C

    Column D

    Line 1

    名称

    分类

    联系人/联系人名称

    Dedicated Salesman

    Line 2

    Black Advertising

    Silver,Gold

    George Black

    Administrator

    Line 3

       

    Jean Green

     

    Line 4

    Tiny sprl

     

    Fabien Pinckaers

    Administrator

  2. 第二行相当于建立一个新的合作伙伴,有两个已有的类别,两个联系人,并关联了一个销售员

  3. 保存这个文件,用文件名partners.csv。

  4. 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.

  5. 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,

  • 直接访问PostgreSQL数据库。

小技巧

Module Recorder

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.