Excel import

Import from Excel

In Pimics, you can easily import product data from Excel files. Usually, our customers use this functionality to get price lists into Microsoft Dynamics 365 Business Central. These price lists can have different structures and can also contain more data than just items and prices.

Import from Excel is part of Vendor Catalog extension. Please, ensure that you have this extension installed.

The import of Excel contains a couple of steps that have to be done. These steps include creating a mapping of incoming data, importing a file, managing data in Vendor Catalog and activating data to transfer it to Microsoft Dynamics 365 Business Central.

To Create Excel mapping

Mapping is usually created once per Excel format and Excel format is usually unique per each of your supplier. The mapping is later on used for every file from each supplier. The mapping contains complete settings for one file with more worksheets.

  1. Create a base mapping. See Create a mapping
  2. Open the mapping card.
  3. In the Type field, select Excel.
  4. 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.
    3. Repeat steps above for each worksheet and each column in the worksheet.

  5. Click on OK to close the page.

Source Column Type possibilities

  • Name is the exact name of the column defined in the header row (see field Header Row No. in Excel Details group on Mapping Card).
  • ID is the identification of the column (like A, B, C, etc.) as you can see it in Excel.
  • Index is the order number of the column.

Import data from a file

Once you get a new file from a vendor, you need to get it into the system. In Pimics, we have usually one import for each Excel file.

  1. Choose the search icon, enter Excel Import Wizard, and then choose the related link.
  2. On the first screen, select your local Excel file in the File field. The file will be uploaded to the server
  3. Click on Next to go to the second screen.
  4. In the Reference Type field, select on of the following:
    1. Partner if a supplier is already in the system. Select the partner in Reference No. field.
    2. Create New Partner if it is a file from a new supplier. The partner will be created automatically.
  5. Select your mapping for the file in the Mapping Code field.
  6. Click on Next to go to the third screen.
  7. In the Process File field select one of the following:
    1. No if you want to start the import later on.
    2. Yes if you want to start the import immediately.
    3. On background if you want to start import immediately and continue with your other work. The import will be scheduled and run on the background.
  8. Click on Finish to create the import, and also the partner, if selected, and run the import.

See also

Title: Excel import, Author: Martin Opršal