Data transformation concept

It is clear that in some non trivial cases of data transformation you need to use ETL tools. You can easily find in Internet a batch of such tools, but in my case, I decided to look into open source and free of charge tools to try the whole idea before consider purchasing any commercial application for my needs.

After several attempts I choose Expressor Studio 3.0.1.

You choice might differ, so try to use the idea with the tool which suits you best.

To do plan:

1. Select and install a ETL application;
2. Setup database connection;
3. Select several tables from the list of tables you are to migrate;
4. Start the migration;
5. Adjust sequences for your tables;

To do list in details

So the first step is to install Expressor Studio which is very simple task and I don't want to provide a lot of details with regard the activity.

The second step is to select what tables you are to migrate. IN general all tables from your old database are required your attention but you can be focused on a small subset as a try the concept. Once your algorithm works you can use it for the rest of data.

Let's start from migration of users. In such a case you are to migrate res_users table from your old database into the new one.

First of all, create a new clean database in your new OpenERp 6.x installation. When you are done, you can access the database by using PostgreSQL pgAdmin III - the native PostgreSQL administration tool.

You can setup a new connection by using a specific PostgreSQL user which has been created during your OpenERP installation.

If you don't know the password of such a user, you can change it in pgAdmin III or simply create a new one, you might use during the transformation activity.

Once you create a connection and test it, you can use the same data in Expressor Studio.

Final adjustments

The final database looks like as it is shown in image on the right.

But in order to be able to work with your data in OpenERP we are to set sequence numbers in PostgreSQL DB.

We need to use pgAdmin III tool. Open your database and select the table you just migrated as follows: your_table_name\Schemas\public\Tables\res_users, and consider the name of the field which is automatically increases each time you add new record (primary key).

 After that you are to open sequences for such a field as follows: your_database_name\Schemas\public\Sequences\res_users_id_seq, as you can notice, the name of the sequesnce is the table name + _field_name + _seq.

You got a dialog box in which you are to adjust current valus, which ought to be set as the last record id value in your table. In my table it is 10, so, just set the value in 10. If you don't correct such a value, in OpeneRP system you get a error message as follows:

Integrity error:
duplicate key value violates unique constraint "res_partner_address_pkey".

So, you are about to get your OpenERP system up and running with youк old data but new face and functionality.