Using INSERT_IN_TABLE for multi-record import

It is possible to import multiple lines in the content of the same table when importing using a connector (generic import), starting from a CSV data source. It is, therefore, possible to associate multiple lines in the origin, associated with the same product reference, and connect values to fill in multiple lines in the content of a table field, that is associated with a specific product reference (also catalog, variant, or Custom Entity).

Let's take the following CSV source file as an example:

Item Reference;Resource Id;Resource Type;Resource Description;Resource URL
RF-0;RF-0__0;jpeg/image;Product image large;https://www.example-company.com/rf_0_0.jpg
RF-0;RF-0__1;jpeg/image;Product image thumbnail;https://www.example-company.com/rf_0_1.jpg
RF-0;RF-0__2;pdf;Catalogue features;https://www.example-company.com/rf_0_2.pdf
RF-0;RF-0__3;pdf;Price sheets;https://www.example-company.com/rf_0_3.pdf
RF-0;RF-0__4;pdf;Brand logo;https://www.example-company.com/rf_0_4.pdf

Ejemplo de tabla de archivo de origen CSV

The table above shows it clearly.

If we want to import these five entries into a table-type field, keeping the same format of this representation from an import connector, we can do it by using the formula INSERT_IN_TABLE.

Although this example shows the representation for a single product with reference RF-0, the CSV source can contain multiple product references, as it usually is in Sales Layer import connectors. When we use the formula INSERT_IN_TABLE, repeated log lines will be taken into account.

** Although it is possible to connect other columns of the CSV source with other fields in Sales Layer PIM, which are not table-type fields (for instance texts, images, numbers ...), we recommend using this type of import with duplicate reference records when supplying information to table type fields. If we still want to connect CSV columns to NO-table fields, it is advisable to repeat their value in the successive columns. In these cases, the value, supplied to the PIM after the import, will be the last one read. No accumulation will occur.

To import this table completely into an import connector:

In a generic import connector, connect the key field with the product reference on the PIM side: Product reference, with the key field in the source CSV file: Item Reference.

Enter the data in a table-type field called Resources on the PIM side.

The following image shows how the configuration is displayed in the connector.

Now, feed the content of the Resources field using the INSERT_IN_TABLE formula to extract the data from the input CSV file with the following syntax:

INSERT_IN_TABLE(

"delete", 

"", "Resource Id", GET_COLUMN_VALUE("Resource Id"), 

"", "Resource Type", GET_COLUMN_VALUE("Resource Type"), 

"", "Resource Description", GET_COLUMN_VALUE("Resource Description"), 

"", "Resource URL", GET_COLUMN_VALUE("Resource URL")

)

The delete mode was used since it deletes the possible previous content of the field to introduce completely updated content from our CSV. Depending on the situation, you may want to use the insert or update modes.

In all the triads, the row name was left blank in quotation marks. This will guarantee the creation of new rows and in case the named columns do not exist (as in this case), they will be created since the delete mode acts similarly to the insert mode.

The result of applying this formula will appear in the following table:

This is the result you wanted to obtain for the product with ref RF-0.

The INSERT_IN_TABLE formula is a very powerful formula for entering data into table-type fields. It can be used in import connectors and bulk modification actions to alter the content of table-like fields.

Related Links