Skip to content

Maintaining Item Attributes with Excel

With Product Data you export the item attributes of many items to an Excel workbook, change the values in Excel and import only the changed values. Before anything is written, a preview shows every change and every cell that cannot be taken over.

You reach export and import

  • from the Product Data Dashboard: Export Attributes to Excel, Import Attributes from Excel,
  • the export also in the Item List, group Product Data,
  • both through search ("Tell me what you want to do").

Like every tool, they run only while Product Data is switched on and has a license or a running demo.

Exporting

You choose the items with the same filters as for the tools: item no., item category (with or without subcategories), vendor and manufacturer. Export to Excel creates the workbook and downloads it.

From the item list, the export takes over the selected items. If only one item is selected, it takes over the filters of the list on item no., item category, vendor and manufacturer. If a filter does not fit into the export – several item categories, say, or a great many individually selected items –, the export does not open. That way it never covers more items than the list shows.

If the export covers more than 50,000 items, it asks first: an import does not read that many rows at once.

Layout of the workbook

  • One worksheet per item category, named after the category code. Items without a category are on a worksheet of their own. Characters Excel does not allow in sheet names are replaced by _.
  • One row per item, sorted by item no.
  • Fixed columns: item no., description, vendor item no., GTIN and manufacturer code. They are for orientation; apart from the item no., the import does not read them.
  • One column per item attribute, sorted by name: every attribute an item of the sheet has, and every attribute the Attribute Rules provide for the category. That way missing values can be filled in directly. Blocked attributes are left out; they take no values.
  • A note on the column header names the type, unit, whether multiple values are allowed, and the hint for maintenance.
Row Content
1 Column names (attribute names)
2 Technical row, e.g. #ITEM or #ID:123. It maps every column to its attribute
from 3 Items

Do not change or delete row 2

Through the technical row, the import finds the attribute of a column even if the attribute was renamed in the meantime.

Values in the cells

  • Multiple values are in one cell, separated by a vertical bar with spaces: Blue | Red.
  • Numbers are number cells in the workbook. As text with a dot as decimal separator come multiple values of number attributes (1.5 | 2.25) and numbers with more than six decimal places or more than nine digits before the decimal point – so they come back unchanged.
  • Dates are text in the format YYYY-MM-DD (2026-10-16). If Excel turns one into a date cell while editing, the import reads that too.

Importing

  1. Open Import Attributes from Excel.
  2. Optionally switch on Empty Cells Clear Values (see below).
  3. Choose Import from File and select the workbook. The import checks all sheets and opens the Excel Import Preview. Nothing is written yet.
  4. Check the lines in the preview and choose Apply. The confirmation names the number of changes; afterwards a result message reports what was written.

How the import maps

  • Items by the item no. column (#ITEM in row 2).
  • Attributes by #ID:… in row 2, otherwise by the column name in row 1 – case-insensitively. So you can also add a column of your own named after an attribute.
  • A workbook without a technical row works too: row 1 then holds the column names (among them Item No.) and the items start in row 2. Columns named like the fixed columns are skipped.

Rules

  • Only changed values are written. A cell with the values the item already has stays untouched – including its origin. Texts and options are compared case-insensitively, numbers and dates by their value.
  • Other attributes stay untouched. The import writes only the attribute of a changed cell, never all attributes of an item anew.
  • Empty cells leave the item's values as they are. With Empty Cells Clear Values, an empty cell removes the values of the attribute.
  • Unknown columns and items are reported, never created. This also applies to values in a column with no header at all.
  • Duplicate rows and columns are not taken over: if an item appears twice in the workbook or an attribute has two columns, there is no telling which one is meant. The preview reports both.
  • A sheet without an item no. column, or one that cannot be read (a chart sheet, say), is skipped; the other sheets are imported.
  • Every change counts as entered by hand (origin Import): supplier connectors, Icecat and the AI no longer overwrite it.
  • An import reads at most 50,000 item rows. Split larger files.

The preview

At the top, the Excel Import Preview sums up: Item Rows Read, Values to Write, Values to Clear, Skipped or Errors, Unchanged Values and Empty Cells Left As Is. Below, there is one line per change or problem with sheet, cell, item, attribute, current and new value and the Import Action:

Import action Meaning
Write The item gets the values from the cell
Clear The values of the attribute are removed (only with Empty Cells Clear Values)
Skip Not taken over, e.g. because the attribute is blocked or a sheet has no item no. column
Error Not taken over; the Import Issue is shown in the line

Possible import issues: No item with this number, Item appears more than once, No item attribute with this name, Attribute has more than one column, Value does not fit the attribute type, Attribute takes one value only (multiple values without Multi-Value Allowed), Value is not in the option list, Value is too long, Attribute is blocked, Sheet has no item number column, Sheet cannot be read.

Show Problems Only hides the changes, Show All Lines shows everything again. Apply writes the lines with Write and Clear; lines with errors are left out. Correct them in the workbook and import again – values already taken over are then recognised as unchanged.

Limits

  • A vertical bar | separates multiple values. A single value that contains one survives the round trip only unchanged; whoever edits it in Excel gets several values.
  • Business Central writes cells with more than 250 characters (long multiple values) in the Windows-1252 character set and without line breaks. Values with other characters or line breaks in such long cells come back changed; the preview then shows them as a change.