| Docs

Define Mapping for Excel import

Author: Pimics Team, Modified On:

Create Excel Mapping

  1. In the Type field, select Excel.
  2. The FastTab Excel Details contains a definition for each worksheet.

    1. Fill up the first line.

      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.

  3. Click on OK to close the page.

Input data transformations

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

  1. Select a type in Import Transf. Type
    • We're providing the base functions and others can be added by your partner
  2. 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.

  1. Follow instructions in Create Excel Mapping
  2. Duplicate the line with the source definition. Select a line in mapping and run an action Copy.
  3. Set Regex in Import Transf. Type
  4. 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
  5. 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
  6. For both lines you can define the destination as it is written in Create Excel Mapping
Title: Define Mapping for Excel import, Author: Pimics Team