Table of Contents
TODO

UPDATE FOR PIMICS 13.0: Excel export documentation needs to be updated to reflect new Source Record options:

New Source Record Types:

  • As of Pimics 13.0, Publication and Translation are now available as Source Record Types (in addition to existing options)
  • This enables exporting main language + translations in the same file

Action required:

  • Update step 3 in the mapping configuration section to mention Publication and Translation as available source types
  • Add a new section showing how to configure exports with Publication/Translation source types
  • Provide example use case: "Exporting multilingual product data with main language and translations in one file"
  • Include screenshots showing the new source type options
  • Add best practices for using Publication vs. Translation source types
  • Consider adding a comparison table showing when to use each source type option

How to export to an Excel file with custom layout

When to use the Pimics Excel export

Business Central contains an Excel integration by default, and this integration can be used for internal ERP purposes. However, if you want to export a price list or another type of feed with a custom layout, the default integration cannot be used. That is the reason why we made Pimics functionality that will allow you a lot of flexibility in defining the data for your Excel exports, and deciding how you want them to look.

Before exporting to Excel

There are several things to be set before being able to export data to an Excel file.

Note

We recommend downloading our RapidStart package PackagePIMICS.ENU.EXCELEXP.rapidstart from our configuration packages page. It contains the base configuration which you can then change for your own purposes. For more details see our packages documentation.

Mapping

Publication and Translation as Source Records

You can use Publication and Translation as Source Record Types in Excel/CSV exports. This enables exporting main language and translations in a single file, supporting better integration with external systems.

How to use:

  • When configuring your export mapping, select Publication or Translation as the Source Record Type.
  • This allows you to include both main product data and translations in your export.

Example use case: Exporting multilingual product data with both the main language and translations in one file for e-commerce or partners.

Best practices:

  • Use Publication for main product data exports.
  • Use Translation to include all available translations for each product.
  • Consider your integration needs when choosing the source type.

A mapping is typically created once per export type. You can use this mapping to define the format and layout of your Excel data. You can look at it as defining the header for your Excel exported file. The actual data will be added to this header at the time the data will be exported. Which items, categories or prices will be included into the final exported file is defined in the particular publication that you will export from.

Note

If you use our predefined configuration package, some values can be already created. Be sure to change them if you need to.

  1. Using Alt+Q, search for export mapping and then click on the relevant link
  2. Select New to create a new mapping
  3. Specify the Description
  4. Set Excel in field Type; after that a new group Excel - Details will become available
  5. If you already have a file which should be used as a template select it in the field Template File
    • In case no template is uploaded, data will be exported into an empty Excel worksheet
  6. Go to Excel - Details and create a new line, specifying these fields:
    1. Target List Name to set a name for the Excel list
    2. First Data Line to set a line number beginning from where data will be inserted
    3. Filter to set condition to filter data for Target List
  7. Select List Settings from the group actions
  8. In page Excel Export Mapping Line you can define a mapping between data in Pimics and the export
    1. Create a new line
    2. Set Source Type to Item
    3. Set Source Record Type to Filter
    4. Set Source Field No. to 1. Lookup and select field Code
    5. Set Destination Type to Column
    6. Set Destination Value to A
    7. Repeat steps 1 to 5 to create other fields, updating the column label. Or you can use action Copy to duplicate selected line
  9. Close all pages

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.

  1. Select the row mode – by item, variant, or catalog item.
  2. For each sheet in Excel, one mapping row is created.
  3. 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.

Source Type

Source type determines, what source table will be used for mapping row.

Source Record Type

  1. Filter will select Source acording to Source Filter
  2. Next Record will select next record from previously filtered Source
  3. Same Record will select same record from previous row

Destination Type

  1. Column to select specific column in Export File
  2. Next Column to select column following last entery
  3. Append To Last Column to merge with previous entery

Publication Setup

  1. Using Alt+Q, search for publication channel and then click on the relevant link
  2. Select New to create a new channel
  3. Specify Code and Description
  4. Set Type to Excel
  5. Set Publication Type to No Details
  6. In Excel group select the Mapping Code that you created before in Mapping
  7. Using Alt+Q, search for publication group and then click on the relevant link
  8. Create a new publication group for all types that you want to include in your publication, with predefined filters and rules. How to create publication group.
  9. Close all pages

Exporting product data

We recommend creating a publication for each different purpose. For example if you need to export different items (or prices) for different customers, each should have their own publication set up, using the same mapping.

Note

In this scenario we assume that you downloaded and applied the Allium-provided configuration packages. All values are included in this package, and they can be changed at any time to suit your own purposes.

  1. Go to Publication Management > Publications
  2. Create a new publication and select Excel (Publication) from the template list
  3. Select Edit to open the publication card
  4. Define settings for pricing in group Pricing & Period
  5. In field Export Channel specify the channel you created previously in Publication Setup
  6. Select Publication Structure
  7. Define data for the export
    • Select the data you want to include in your export, by using the function Insert New Peer
    • The data is inserted into the publication and you can browse it in the publication tree
    • Here you can find more details about how to define a publication structure
  8. Close the page Edit Publication Tree
  9. Select Process and then Publish
  10. The Excel file is generated and downloaded
  11. When running the export at a later date, it is best to first run either Update Publication or Reorganize Publication, to update the publication data. More details here
  12. Close all pages