Define Mapping for Excel import

Create Excel Mapping

In the Type field, select Excel.
The FastTab Excel Details contains a definition for each worksheet.
1. Fill up the first line.
  
  Note
  
  It is necessary to have a line for each combination of worksheet and destination. That means you can import data from the same worksheet into two different tables.
  1. In the List Name, enter the worksheet name. It has to be same as the worksheet name in the Excel file.
  2. Select the destination table in Table No. For importing data into the Vendor Item table for example, you must specify 4006590.
  3. In the Header Row No., define where column headers are located in the Excel.
  4. Define the first line with data in Excel file in the First Data Row No.. The data will be imported from this line to the end of the worksheet.
2. For each line, you need to define field mapping.
  1. Select List Settings by clicking on Excel Workbook Mapping.
  2. In the Source Type field, define the source by selecting:
    - Column if you want to use a value from Excel column
      1. In the Column Type field, select ID.
      2. Enter A for the first column in the Excel in the Source Description field.
    - Constant if you want to insert the same value for all entries.
      1. Specify the constant in the Source Description field.
  3. Define the target field in Destination Type by selecting:
    - Field if the source value should be placed into a field in the Vendor Item table.
      1. Specify the target field in Destination Field No.. At a minimum, it is necessary to set up the field No., because this ensures identification of the vendor's item.
    - Another Related table if the value should be imported into related tables like features, prices, and so on.
      1. Specify the table in Destination Relation field.
      2. Specify the target field in Destination Field No. field.
  4. You can also define transformation more details you can see below
3. Repeat steps above for each worksheet and each column in the worksheet.
Click on OK to close the page.

Importing export mapping from a template

It is the process where, when creating a new export map in Pimics, an Excel template is used as the basis for automatically generating mapping rows. The goal is to simplify and automate the export data setup.

Selecting a template

If you already have a file you want to use as a template, select it in the Template File field. Exporting sheets from a template After adding the file to the Template File field, the option Sheets From Template becomes available. Running this option exports the sheets from the template into Excel – Details. For each sheet, one row is created in Excel – Details, and the Target List Name is filled in.

Checking and completing details

You need to check and complete additional fields in individual rows in Excel – Details, such as export type, filter, target object, etc.

Select the row mode – by item, variant, or catalog item.
For each sheet in Excel, one mapping row is created.
For each column in the sheet, a mapping row is created with the target type “Column” and a value based on the column header.

Generating the mapping

Completing the import of Sheets From Template makes the Mapping From Template option available. This function automatically generates mapping rows according to the template settings.

Smart field recognition

The algorithm recognizes what field it is based on existing data and sets it in the export definition.

For example:

Field matching:
The system tries to find a corresponding field based on the column name.
Matching takes place in the following order:

Item Variant
Item
Feature (roduct Feature)
Text (Description Text)

Special objects:
If no corresponding field is found in the basic objects, the system tries others such as:

Item Group
Feature
Price
Document, Picture, Media, CAD Drawing
Matching is based on table/column labels and is case-insensitive.

Limitations:
Some types such as Product Group, Chapter, Catalog Group, and Keywords are not yet allowed as source types for export mapping from Excel.

Input data transformations

To transformormation data you can specify in the last group Transtormation Settings

Select a type in Import Transf. Type
- We're providing the base functions and others can be added by your partner
Use lookup on field Transformation Expresssion to define parameters
- For each types the parameters are different

Regular expresions

The typical scenario for this type of transformation is to find part in the incoming data. For example vendor send you "100 PCS" in one field and you need to split it into two parts 100 and PCS.

Follow instructions in Create Excel Mapping
Duplicate the line with the source definition. Select a line in mapping and run an action Copy.
Set Regex in Import Transf. Type
Use lookup in Transformation Expression
1. Put [0-9.,]+ into a line with expression to extract numbers
2. Keep 1 in match no., because we'd like to get the 1st match
Select the second line with the same source, repeat the steps 3-4 for the second source
1. Put [a-zA-Z]+ into a line with expression to extract units
2. Keep 1 in match no., because we'd like to get the 1st match
For both lines you can define the destination as it is written in Create Excel Mapping

Activation
BMECat import
DataNorm import
Manage imports and items
Partner
XML import

Table of Contents