Help with importing csv data please

[Paul Leverett]

Hi, I'm struggling with importing csv data into Products.

From previous posts I understand that "CSV import is not the direct opposite of csv Export", but I still can't get my head around how to import data that includes mandatory pick-list fields. I can do a text-only import OK, but can't get anything to work with look-ups.

e.g. As a test I try to import a single Product record CSV with the minimal mandatory fields:

<<

Cost Price,List Price,Name,Type,Default UOM/Name,Cost Method

9.000,11.000,Minimal fields test product3,goods,Unit,fixed

>>

This doesn't work and gives me a server error.

Can you give me any specific information about why this doesn't work?

For look-ups do I need to use an ID instead of a name?

It would be great if someone could give me a working one-line csv that will work as an upload into Products.

Many thanks,

Paul

[Raimon Esteve]

2014-03-26 12:48 GMT+01:00 Paul Leverett <pa...@marsguitars.co.uk>:
> Hi, I'm struggling with importing csv data into Products.
>
> From previous posts I understand that "CSV import is not the direct opposite
> of csv Export", but I still can't get my head around how to import data that
> includes mandatory pick-list fields. I can do a text-only import OK, but
> can't get anything to work with look-ups.
>
> e.g. As a test I try to import a single Product record CSV with the minimal
> mandatory fields:
> <<
> Cost Price,List Price,Name,Type,Default UOM/Name,Cost Method
> 9.000,11.000,Minimal fields test product3,goods,Unit,fixed
>>>
>
> This doesn't work and gives me a server error.
>
> Can you give me any specific information about why this doesn't work?
> For look-ups do I need to use an ID instead of a name?

UOM need an ID, not name

You have examples in doc.tryton-erp.es (1) (2) (Use a translator to
translate Spanish to another language)

(1) http://doc.tryton-erp.es/trytond_doc/tryton_import_export.html
(2) http://www.tryton-erp.es/posts/importacion-de-productos-mediante-csv.html

Raimon

[Cédric Krier]

On 26 Mar 15:10, Raimon Esteve wrote:
> 2014-03-26 12:48 GMT+01:00 Paul Leverett <pa...@marsguitars.co.uk>:
> > Hi, I'm struggling with importing csv data into Products.
> >
> > From previous posts I understand that "CSV import is not the direct opposite
> > of csv Export", but I still can't get my head around how to import data that
> > includes mandatory pick-list fields. I can do a text-only import OK, but
> > can't get anything to work with look-ups.
> >
> > e.g. As a test I try to import a single Product record CSV with the minimal
> > mandatory fields:
> > <<
> > Cost Price,List Price,Name,Type,Default UOM/Name,Cost Method
> > 9.000,11.000,Minimal fields test product3,goods,Unit,fixed
> >>>
> >
> > This doesn't work and gives me a server error.
> >
> > Can you give me any specific information about why this doesn't work?

Can you give us information about the error first?

> > For look-ups do I need to use an ID instead of a name?
>
> UOM need an ID, not name
>

That's false. import_data for many2one accepts name:
http://hg.tryton.org/trytond/file/8e5623672cdc/trytond/model/modelstorage.py#l555

> You have examples in doc.tryton-erp.es (1) (2) (Use a translator to
> translate Spanish to another language)
>
> (1) http://doc.tryton-erp.es/trytond_doc/tryton_import_export.html
> (2) http://www.tryton-erp.es/posts/importacion-de-productos-mediante-csv.html

I could have proposed a correction if it was in English.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Raimon Esteve]

>> UOM need an ID, not name
> That's false. import_data for many2one accepts name:
> http://hg.tryton.org/trytond/file/8e5623672cdc/trytond/model/modelstorage.py#l555

Great!

[Paul Leverett]

Can you give us information about the error first?

Trying to import the 1-record example csv I get :

Application Error! 

<ProtocolError for localhost:8000/name: 500 Internal Server Error>

None

[Hendrik Brandes]

Hello Paul,

I have stucked with same errors and have done the following steps to solve this:

1) Create a debug-enhancement within modelstorage.py, so every exception within the validation gives me a hint, what went wrong

For example in modelstorage.py, method _validate. Before every cls.raise_user_error I have inserted a debug-statement like print or logging.

2) I also create some debug hints within the database-backend, method execute, so I can see, which records are read and what are the conditions

Under some circumstances, I found out, that some references are not stored correctly ( perhaps due to missing masterdata ) and those leads to my errors.

Finally, I could manage my imports by analyzing and add every needed field step by step. 

After I have known, what are my necessary fields, I created a pentaho transformation ( kettle ) and automate the creation of import data.

Kind regards,

Hendrik

[Paul Leverett]

On Thursday, March 27, 2014 9:55:43 AM UTC, Hendrik Brandes wrote:

Finally, I could manage my imports by analyzing and add every needed field step by step. 

After I have known, what are my necessary fields, I created a pentaho transformation ( kettle ) and automate the creation of import data.

Thanks for your comments, I have now solved the problem and it seemed very simple... I wan't enclosing text in "quotes".

If I do this and use a direct-matching name for the lookup field (not ID) it seems to work OK.

The notes in Spanish posted by Raimon were very helpful - muchas gracias.

(I used Google Chrome translator for instant page translation to English - fantastic.)

 

Now that I've go a basic example working I'm a lot happier in progressing various different imports - thanks for the help

[Dale Scott]

Hi all, 

On Thursday, 27 March 2014 06:26:21 UTC-6, Paul Leverett wrote:

Now that I've go a basic example working I'm a lot happier in progressing various different imports - thanks for the help

I'm still trying to get my basic example working before I can be a lot happier... ;-)

I am trying to import products (parts) and a BOMs from a commercial database application called Parts&Vendors (P&V) (see [8] for an ascii-art BOM tree). After some trial-and-error, I was able to import all the product from P&V using a simplified version of the csv exported from P&V ([1] is the csv as exported from P&V, [2] is the simplified version that I actually imported).

Problem 1. If I view the detail of a specific product in Tryton (menu Products > Products, select a specific product, "Switch view" to see product detail), and then try to "Switch view" back to the grid view showing all product, Tryton will warn that the record has been modified and ask if I want to save it - even though I did not knowingly edit it. What can cause this? Is it because my import csv is missing some required field, and if so, how can I know which fields are required? I thought perhaps the "blue" highlighted fields in the Import/Export "All fields" window are required, but I was able to import [2] without error - and without most of the "blue" fields (there are so many blue fields!).

Problem 2. The P&V BOM table includes two foreign key constraints to the P&V part table. Each row in [3] represents a child part on a BOM, PLListID is the PNID of the parent product in [1], and PLPartID is the PNID of a child product on the BOM. Other fields in [3] give the quantity on BOM, the "use-as" UOM, the item number on the BOM listing, etc. Can Tryton import multiple BOMs from a single csv file having this structure? Will someone give me a hint as to the field names?

Problem 3. P&V product data ([1]) includes a number of attributes not defined in the Tryton product module. I have installed the product-attribute module, created  attributes corresponding to those in P&V (and entered values for several products manually), but I don't understand how to import these attributes from csv. Is it possible to import these additional attributes at the same time as products are imported?

I read the Tryton wiki page on Export/Import Limitations ([4]), and the pages on importing csv on the spanish-language Tryton site ([5], [6] and [7]), but right now importing seems very close to magic. Why are two csv files required, one for "Template: name, price..." and another for "product code, description..." ([6]). Perhaps if I understand this, all will be clear (but right now, importing seems more like magic. ;-))

Thanks for your help,

Dale

[1] https://github.com/dalers/maestro/blob/master/scc/csv-1/pv_pn.csv

[2] https://github.com/dalers/maestro/blob/master/scc/ods/product-tryton-product.csv

[3] https://github.com/dalers/maestro/blob/master/scc/csv-1/pv_pn.csv

[4] https://code.google.com/p/tryton/wiki/ImportData

[5] http://doc.tryton-erp.es/trytond_doc/tryton_import_export.html

[6] http://www.tryton-erp.es/categories/importacion.html

[7] http://www.tryton-erp.es/posts/importacion-de-terceros-mediante-csv.html

[8] https://github.com/dalers/maestro/blob/master/tryton-proposed/001-initial-setup-and-import.md#create-bills-of-materials-boms

[Cédric Krier]

On 02 Jul 06:05, Dale Scott wrote:
> Hi all,
>
> On Thursday, 27 March 2014 06:26:21 UTC-6, Paul Leverett wrote:
> >
> >
> > Now that I've go a basic example working I'm a lot happier in progressing
> > various different imports - thanks for the help
> >
>
> I'm still trying to get my basic example working before I can be a lot
> happier... ;-)
>
> I am trying to import products (parts) and a BOMs from a commercial
> database application called Parts&Vendors (P&V) (see [8] for an ascii-art
> BOM tree). After some trial-and-error, I was able to import all the product
> from P&V using a simplified version of the csv exported from P&V ([1] is
> the csv as exported from P&V, [2] is the simplified version that I actually
> imported).
>
> Problem 1. If I view the detail of a specific product in Tryton (menu
> Products > Products, select a specific product, "Switch view" to see
> product detail), and then try to "Switch view" back to the grid view
> showing all product, Tryton will warn that the record has been modified and
> ask if I want to save it - even though I did not knowingly edit it. What
> can cause this?

Some data are not in the right format. So the client after displaying
it, re-format it and detect a change in the value.

> Problem 2. The P&V BOM table includes two foreign key constraints to the
> P&V part table. Each row in [3] represents a child part on a BOM, PLListID
> is the PNID of the parent product in [1], and PLPartID is the PNID of a
> child product on the BOM. Other fields in [3] give the quantity on BOM, the
> "use-as" UOM, the item number on the BOM listing, etc. Can Tryton import
> multiple BOMs from a single csv file having this structure? Will someone
> give me a hint as to the field names?

I did not look to close to your data structure but any way, csv import
only works for very basic/simple cases. Any even a little bit complex
import should be done using a proteus script. Python is an the ETL
(https://en.wikipedia.org/wiki/Extract,_transform,_load)

> Problem 3. P&V product data ([1]) includes a number of attributes not
> defined in the Tryton product module. I have installed the
> product-attribute module, created attributes corresponding to those in P&V
> (and entered values for several products manually), but I don't understand
> how to import these attributes from csv. Is it possible to import these
> additional attributes at the same time as products are imported?

Attributes is a Dict field and there is no format in csv for this kind
of field. But it is supported by proteus.

> I read the Tryton wiki page on Export/Import Limitations ([4]), and the
> pages on importing csv on the spanish-language Tryton site ([5], [6] and
> [7]), but right now importing seems very close to magic. Why are two csv
> files required, one for "Template: name, price..." and another for "product
> code, description..." ([6]). Perhaps if I understand this, all will be
> clear (but right now, importing seems more like magic. ;-))

This is because you have to deal with the internal data structure of
Tryton. If you want to understand, you have to read the code of the
Models.

[Dale Scott]

Hi Cédric, thanks for your feedback.

On Wednesday, 2 July 2014 07:39:14 UTC-6, Cédric Krier wrote:

On 02 Jul 06:05, Dale Scott wrote:

<snip> 

>
> Problem 1. If I view the detail of a specific product in Tryton (menu
> Products > Products, select a specific product, "Switch view" to see
> product detail), and then try to "Switch view" back to the grid view
> showing all product, Tryton will warn that the record has been modified and
> ask if I want to save it - even though I did not knowingly edit it. What
> can cause this?

Some data are not in the right format. So the client after displaying
it, re-format it and detect a change in the value.

Could you describe in a general way how you would investigate this?

 

> Problem 2. The P&V BOM table includes two foreign key constraints to the
> P&V part table. Each row in [3] represents a child part on a BOM, PLListID
> is the PNID of the parent product in [1], and PLPartID is the PNID of a
> child product on the BOM. Other fields in [3] give the quantity on BOM, the
> "use-as" UOM, the item number on the BOM listing, etc. Can Tryton import
> multiple BOMs from a single csv file having this structure? Will someone
> give me a hint as to the field names?

I did not look to close to your data structure but any way, csv import
only works for very basic/simple cases. Any even a little bit complex
import should be done using a proteus script. Python is an the ETL
(https://en.wikipedia.org/wiki/Extract,_transform,_load)

Is the csv import capable of importing one single-level BOM, but no more complex?

 

> Problem 3. P&V product data ([1]) includes a number of attributes not
> defined in the Tryton product module. I have installed the
> product-attribute module, created  attributes corresponding to those in P&V
> (and entered values for several products manually), but I don't understand
> how to import these attributes from csv. Is it possible to import these
> additional attributes at the same time as products are imported?

Attributes is a Dict field and there is no format in csv for this kind
of field. But it is supported by proteus.

OK, I know I must eventually learn how to use proteus.

Why are two csv 

> files required, one for "Template: name, price..." and another for "product
> code, description..." ([6]). Perhaps if I understand this, all will be
> clear (but right now, importing seems more like magic. ;-))

This is because you have to deal with the internal data structure of
Tryton. If you want to understand, you have to read the code of the
Models.

OK. Thanks.

[Cédric Krier]

On 03 Jul 16:30, Dale Scott wrote:
> Hi Cédric, thanks for your feedback.
>
> On Wednesday, 2 July 2014 07:39:14 UTC-6, Cédric Krier wrote:
> >
> > On 02 Jul 06:05, Dale Scott wrote:
> > <snip>
> >
> >
> > > Problem 1. If I view the detail of a specific product in Tryton (menu
> > > Products > Products, select a specific product, "Switch view" to see
> > > product detail), and then try to "Switch view" back to the grid view
> > > showing all product, Tryton will warn that the record has been modified
> > and
> > > ask if I want to save it - even though I did not knowingly edit it. What
> > > can cause this?
> >
> > Some data are not in the right format. So the client after displaying
> > it, re-format it and detect a change in the value.
> >
>
> Could you describe in a general way how you would investigate this?

I would put a print statement in the client:
http://hg.tryton.org/tryton/file/e5604ddea4c4/tryton/gui/window/view_form/model/record.py#l140

I know we should have logging for that.

> > > Problem 2. The P&V BOM table includes two foreign key constraints to the
> > > P&V part table. Each row in [3] represents a child part on a BOM,
> > PLListID
> > > is the PNID of the parent product in [1], and PLPartID is the PNID of a
> > > child product on the BOM. Other fields in [3] give the quantity on BOM,
> > the
> > > "use-as" UOM, the item number on the BOM listing, etc. Can Tryton import
> > > multiple BOMs from a single csv file having this structure? Will someone
> > > give me a hint as to the field names?
> >
> > I did not look to close to your data structure but any way, csv import
> > only works for very basic/simple cases. Any even a little bit complex
> > import should be done using a proteus script. Python is an the ETL
> > (https://en.wikipedia.org/wiki/Extract,_transform,_load)
> >
>
> Is the csv import capable of importing one single-level BOM, but no more
> complex?

Normally it is theoretically possible to import anything but it is much
more complex and difficult when it is not simple data than using
proteus.

[ferr...@gmail.com]

Hello, I did not want to create a new thread. I don't know how to import or export. I am using Tryton 4.0 from the jessie repository, and I want to import data (anything at this point). The only thing that I see related to importing and exporting is Administration > Models > Exports which gives me a rather crude tab as compared to what I see on http://www.tryton.org/news/index.html. I would like to know how to bring that nice interface to import, please.

[Sergi Almacellas Abellana]

Hi,

El 20/07/16 a les 16:43, ferr...@gmail.com ha escrit:

On each toolbar of every tab you open in the client, there is the option
to import Data and Export Data. [1]

You can use those wizards to import data from every model. In this
wizard you will be asked for a CSV fields containing the data to import.
You also have to select the fields you want to import.

Hope it helps.

[1]
http://doc.tryton.org/4.0/tryton/doc/usage.html?highlight=import#tool-bar
--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

[ferr...@gmail.com]

On each toolbar of every tab you open in the client, there is the option
to import Data and Export Data. [1]

[1]
http://doc.tryton.org/4.0/tryton/doc/usage.html?highlight=import#tool-bar

Hi Sergi. Thank you very much for helping me. This is what I get on the toolbar of the parties view: New, Save, Switch View, Reload/Undo, Previous, Next, Attachment, Note, Actions, Relate, Report, E-mail, Print and Search. I cannot see: Export, Import, Duplicate, Delete. Did I do something wrong? I just checked, and my client says "4.0.2". I installed from the jessie repositories.
 

Hope it helps.

It does. Thanks.
 

[Sergi Almacellas Abellana]

El 20/07/16 a les 21:54, ferr...@gmail.com ha escrit:

> Hi Sergi. Thank you very much for helping me. This is what I get on the
> toolbar of the parties view: New, Save, Switch View, Reload/Undo,
> Previous, Next, Attachment, Note, Actions, Relate, Report, E-mail, Print
> and Search. I cannot see: Export, Import, Duplicate, Delete. Did I do
> something wrong? I just checked, and my client says "4.0.2". I installed
> from the jessie repositories.

You have to click on the tools icon, to open a menu where you will see
the missing options.

[Prayash Mohapatra]

On 21-Jul-2016 01:24, <ferr...@gmail.com> wrote:
> I cannot see: Export, Import, Duplicate, Delete.

Use the menu by clicking the Spanner icon above the new button. It has all those options.

Regards,
Prayash Mohapatra

[ferr...@gmail.com]

Use the menu by clicking the Spanner icon above the new button. It has all those options.

Regards,
Prayash Mohapatra

Oh, my gosh! I missed it completely. Thank you very much.

[ferr...@gmail.com]

You have to click on the tools icon, to open a menu where you will see
the missing options.

Thanks Sergi. The answer was too simple :) .