↑↓ selectEnter openEsc close

    1. Elbuz

    How to extract supplier categories from a price list

    20 min readUpdated

    Elbuz reads supplier categories from an Excel or CSV file and builds a tree: each product gets its full category path.

    When you need this:

    • The category is a row above the products — the app finds the heading row and gives its name to every product below.
    • Each product has a category column — its values become a tree; “/” splits a name into levels.
    • Categories differ only by fill or font — you mark which formatting means a category.
    • You picked the wrong type — the next import rebuilds the tree.

    Where to start: see where the file has the category and pick the matching tile.

    What category detection does

    Detection deals only with the supplier's categories — whatever came in the file: “Home appliances / Refrigerators”, “Accessories”, “Spare parts”. Whichever structure type you choose, the result is the same:

    • a price list category directory — this supplier's category tree with nesting levels;
    • the “Price category” field filled in for every imported product — a full path such as “Home appliances / Refrigerators / Built-in”.
    From the supplier file to a filled-in “Price category” field
    XML, JSON with a categorynode,API integrationExcel or CSVType 1Type 2Type 3Type 4Supplier file importedWhat kind of fileThe category comes from the file'stags.The chosen structure type has noeffectWhich structure typeis selected for the price listA row that matches the conditionbecomes a category headingCategory is the value of the“Price category” columnCategory is taken from cellformattingin the “Detected categories” listCategory is taken fromExcel row groupingPrice list category directoryThe product's “Price category”field is filled inSupplier categories can beturned into catalog categories
    • Action or result
    • Decision: the path depends on the answer

    What you have for this:

    Turning supplier categories into your catalog categories is the next step: category matching, distribution rules or AI category matching.

    Which price lists it works for

    The four structure types apply only to Excel and CSV files. For other formats the chosen type has no effect at all.

    In Excel and CSV, rows come as one continuous list, and the category has to be read from somewhere: a separate heading row, a column, cell formatting or row grouping. XML doesn't have this problem — the category is described by tags, and the app takes it from there directly, ignoring the chosen type.

    The chosen structure type is not applied if:

    • the price list file is XML;
    • the file is and the category node is filled in the source settings;
    • products come through some of the ready-made integrations;
    • the app itself found a separate category table in the file — this happens with some XML and CSV files, and then a service type is used that isn't among the tiles.

    From the menu of the “List of price lists” window, the category detection window opens for an XML price list too: nothing stops you there. You pick a tile, the app shows “Updated”, the setting is saved — and does nothing. The source card has no such trap: there the “Category detection” button isn't shown for XML at all.

    For XML, category nesting is turned on by a separate checkbox, “Search XML Category Hierarchy”, in the source card — that's a different setting, not this window.

    How to choose the category structure type

    The “Category structure type” tab has four tiles. Instead of a description, each shows what such an Excel sheet looks like, so you can simply compare it with the supplier's file.

    Clicking a tile saves the choice immediately — there's no separate button, and the app replies with “Updated”. If you pick the third type, the window switches to “Detailed settings” by itself: for this type you'll find a list of detected categories there instead of a condition.

    The Category structure type tab with four tiles showing Excel sheet layouts
    1Selected tile
    2Detailed settings tab
    1. The tile with the check mark is the structure type currently set for this price list.
    2. Detailed settings — switch here to define the rule.
    TypeTile labelHow to spot it in the file
    1Category row, followed by product rowsseparate rows with a category name and no price, with products below them; most price lists are set up this way
    2Category is given in every product roweach product has its own category column
    3Categories have cell formattingno column; categories differ only by fill, bold font or indent
    4Categories have row groupingplus and minus signs to the left of the Excel rows; rows collapse into groups

    A new price list gets type 1 by default.

    A person picks the type after looking at the file: the app doesn't guess it. Supplier price lists vary too much — what marks a category heading in one file turns out to be a row without a price and unrelated to categories in another. There are no price lists that fit none of the four types.

    Type 1. Category row, followed by product rows

    The most common price list layout. The file has a “Refrigerators” row with twenty refrigerators below it, then a “Washing machines” row with washing machines below it. The app finds heading rows by the condition (by default, the price is zero and the name isn't empty) and gives each product the name of the heading it sits under.

    • Identical headings merge into one. If the supplier wrote “Accessories” in three places in the file, the directory will have one “Accessories” category — and it takes its products from only one of those three places. Products in the other blocks go to the category whose heading is above them, so they end up in the wrong category. If a category has noticeably more products than it should, this is usually why.
    • Nesting is two levels only, and based on one sign only: if a heading sits directly under another heading, the upper one is treated as the parent, and the names are joined with “ / ”. Three levels can't be built this way.
    • Products above the first heading get no category — they come before the app has seen any category.

    Heading rows stay among the imported products of the price list — as items with a zero price. This type doesn't remove them; ignore rules do.

    Type 2. Category is given in every product row

    Here there are no separate heading rows: each product has its own category column, and at the column mapping step it's mapped as “Price category”. The app takes the unique values of this column — the directory is built from them. The condition is different here: by default, rows with a price above zero and a non-empty name, that is, ordinary product rows.

    The condition builder for a price list with structure type 2
    1Comparison “greater than”
    1. Comparison “greater than” with value 0 — with type 2 the condition catches ordinary product rows rather than heading rows.

    Nesting is built only in three cases:

    • several file columns are mapped to the “Price category” field — then each column is its own level;
    • the price list comes from a site parser;
    • at least one category name contains the / character — then the name is split into levels at it.

    In the third case, the app first normalizes the spelling: “Appliances/Refrigerators”, “Appliances /Refrigerators” and “Appliances / Refrigerators” are treated as the same thing. Parsing goes up to ten levels deep; anything deeper won't make it into the tree.

    The app deliberately doesn't merge identical names on adjacent levels. It looks odd — “Tools / Tools / Drills” — but some price lists use exactly this repetition to define the hierarchy.

    Categories by formatting or row grouping

    The app parses types 1 and 2 on its own. Types 3 and 4 need something the plain-text view of the file doesn't have: cell formatting and the grouping structure. The app picks them up from the Excel file itself (.xls or .xlsx) during a regular import — there's nothing extra to turn on.

    For types 3 and 4 the app reads category texts from the column mapped to the “Price category” field, or, if there's no such column, from the name column. If neither is mapped, formatting isn't picked up and the “Detected categories” list stays empty.

    Type 3. Categories have cell formatting

    A price list where you can only see the category with your eyes: the category name is bold on a gray fill, the subcategory uses the same font but is indented, and products are plain text. A Boolean condition can't help here: cell formatting isn't a price list field, and the condition builder has no such field.

    For these files the app compares cell formatting. There are eight attributes: fill, font color, font name, size, bold, italic, underline and indent level. All unique combinations from the file are collected in the “Detected categories” list: one list row is one formatting combination. You mark which of them really are categories and set the nesting level for each.

    The app then goes through the levels from lowest to highest and assigns a price list row to a level when its formatting matches all eight attributes at once. The full path is made of the levels joined with “ / ”, also up to ten levels.

    The order of steps here differs from the other types: the list appears only after the price list is imported with type 3 already selected.

    1. Pick the tile and re-import the price list — the first import fills the list.
    2. Mark the rows you need and set the levels.
    3. Re-import the price list once more — this time the categories are assigned.
    The key part is the diamond in the middle: the “Detected categories” list fills up only when it's empty. After that, each import assigns categories based on the rows marked “Active”
    YesNoNext importTile selected:“Categories have cell formatting”Price list importThe app reads the fileand picks up cell formattingIs the “Detected categories”list empty?The list fills up: each formattingcombination is a row with “Active”and level 1The list stays as it was,new formatting isn't addedCategories are assignedonly by rows marked “Active”You clear “Active” on extra rowsand set the “Category level”
    • Action or result
    • Decision: the path depends on the answer

    Type 4. Categories have row grouping

    Excel can collapse rows into groups — plus signs appear to the left of the row numbers, and the sheet can be collapsed down to just the category names. If the supplier used this, the app reads the ready-made grouping structure: Excel has already worked out the nesting levels.

    The category tree from a price list with real nesting
    1Nested category
    1. A nested category — the levels come from the row grouping in the Excel file, nothing had to be counted by hand.

    This type is the only one that removes heading rows from imported products: an item whose name matches the name of a detected category is deleted from the price list. With types 1 and 3, such rows stay among the products with a zero price.

    How many categories and levels detection can handle

    Only the tree depth is limited: the category level is a number from 1 to 10, where 1 is the top level. Detection has no separate cap on the number of categories in a price list. In the “Detected categories” list, only the page size is limited: 25 rows, the rest on the following pages.

    A row in this list isn't a category but one formatting combination. A hundred categories formatted the same way give one row in the list. When “Use sheet names” is on, combinations are counted for each sheet separately.

    The log shows how long your particular file took to import: under the “Data loading completed!” line, the app writes “Time elapsed:” and the duration.

    How the app recognizes category rows

    The second tab of the window is called “Detailed settings”. The hint above it reads: “Define the category-detection rules for the chosen type; save changes with the Save button.” For types 1, 2 and 4 it holds the condition builder titled “Boolean condition for detecting categories in the price list”. For type 3, instead of the builder there's the list of detected categories.

    The condition answers the question “which rows of the file to consider”. It's made of fields, comparisons and values: “Price equals 0” AND “Name is not empty”. Below the builder, the app spells out the assembled condition in plain words — this shows whether you built what you meant.

    The condition builder with a rule and its plain-language reading
    1Condition field
    2Condition in plain words
    3Save
    1. The field list is built from the columns that have an address assigned.
    2. The condition read in plain words — use it to check that it is built as intended.
    3. Save — without it, changes in the builder are not stored.

    The field list contains only the columns you gave an address at the column mapping step: “Name”, “Price”, “Price category”, “Manufacturer SKU”, “Manufacturer” and the other mapped ones. A column you didn't map won't be in the builder — this is the most common reason for “where's my field?”. Column mapping is described in How to upload a supplier price list to Elbuz.

    The set of comparisons depends on the field. For price fields, these are number comparisons: equals, does not equal, greater than, less than, in range. For text fields — checks for an empty and non-empty value, contains a substring, matches, starts with and ends with.

    Below the builder are two buttons: “Clear All” resets the assembled condition, and “Save” saves it. Unlike the tiles, changes here aren't saved automatically — if you don't click “Save”, the old condition stays.

    Conditions filled in automatically

    As soon as you pick a type tile, the app writes a default value to the condition:

    TypeCondition the app fills in
    1Price equals 0 AND Name is not empty
    2Price greater than 0 AND Name is not empty
    3same as type 1 — but this type doesn't read the condition
    4Price equals 0 AND Name is not empty

    For most price lists this is enough — you'll almost never need to touch the condition.

    There's one more automatic addition. If a file column is mapped both to the “Price category” field and to another field — manufacturer SKU, manufacturer, supplier SKU, model, short or full description, color — the condition gets an extra requirement that this other field is non-empty too. This way the app guards against rows where the shared column holds something other than a category.

    If the condition is empty, types 1, 2 and 4 do nothing: no category will appear. Picking a type always writes something, so the condition is empty only if you cleared it with “Clear All” and didn't build a new one.

    How to mark categories in the “Detected categories” list

    Only those who picked type 3 see this list. The “Detailed settings” tab is split into two panels: “Detected categories” on the left and “Price list preview” on the right.

    The Detected categories list for structure type 3
    1Active column
    2Category level column
    3Refresh list button
    1. Active — a check mark marks which formatted cells count as categories.
    2. Category level — sets the nesting level of the category.
    3. The refresh button rebuilds the list from the price list and also restarts the price list import.
    ColumnWhat it meansEditable
    “Active”whether to treat this formatting as a categoryyes, checkbox
    “Category name”text from the supplier fileno
    “Category level”nesting level: 1 is the topyes, a number from 1 to 10

    Double-click a cell to edit it. The category name is drawn with the same formatting as in the supplier file: the same fill, color, font size, bold, italic and indent. If a row has no indent level of its own, the app calculates the indent from the leading spaces in the name.

    The level is a number from 1 to 10: a full path deeper than that isn't built anyway. You can't enter a higher value — the app replies “Category level must be a number from 1 to 10.”

    The list header has three buttons: “Unselect all”, “Select all” and “Update category list from price list”.

    “Select all” and “Unselect all” work only on the visible page of the list, and a page holds 25 rows. If the file has more formatting combinations, you have to click on each page separately. It's easy to assume everything is marked and end up with categories from the first 25 rows only.

    How to rebuild the list

    The list fills up only once: while it has at least one row, new formatting from the file isn't added to it. When the supplier has changed the formatting or added categories, rebuild the list with the “Update category list from price list” button.

    The “Update category list from price list” button clears the whole list and re-reads the price list with a log window. The “Active” marks and the levels you set are lost — the only way to restore them is to set them again.

    This isn't a regular price list import. The file isn't fetched again from the link, email, or API — the app reads the one the source already has. Source operations aren't applied after reading, category detection included. The steps go in this order:

    1. The “Detected categories” list is cleared by a separate request right after you click — regardless of how the import ends. If the list can't be cleared, the app shows a red message with the error text and doesn't start the import.
    2. A check whether the source is already being imported. If it is, the app replies “This source is already being imported (started at ‹time›). Wait for it to finish or stop it with the “Stop” button.” and doesn't start a new import. The list is already empty at that point.
    3. The file is read sheet by sheet. Before each sheet, the app checks whether stop was clicked. If the file couldn't be read, the log shows “No data received, previous product list will be displayed”.
    4. The list is built. Before that, the app checks for a stop once more. If the import was stopped, the list stays empty. If not, it's filled again, and every row is marked “Active” with level 1.

    This button doesn't touch the price list's products, its category tree, attributes or photos. That's why the final “Data loading completed!” log line shows the same product count in parentheses as before you clicked. To have categories assigned by the new list, mark the rows, set the levels and import the price list the regular way.

    If reading is interrupted, the source's products and categories stay as they were; the next import fills the empty list.

    The right panel shows the file itself. If viewing through MS Office Online is turned on in the app, and the file is Excel and smaller than 5 MB, the panel shows it as Microsoft does. Otherwise you get a simple content view: for Excel with tabs per sheet, for XML and CSV as one canvas.

    When one file holds several price lists

    Sometimes a supplier puts several price lists into one Excel file: refrigerators on the first sheet with categories in separate rows, spare parts on the second with the category in a column. One structure type for the whole file won't work here.

    For this, the “Detailed settings” tab has a “Use sheet names” checkbox below the condition builder. When it's on, the app goes through the active sheets one by one and builds categories for each separately.

    The sheet name doesn't become a category — it only separates the records; you won't see it in the category tree. The checkbox exists only for Excel price lists: CSV and XML have no sheets. It's saved as soon as you select it. It's off by default.

    The structure type still stays one for the whole file — the one picked with the tile. If sheets need different types, you need an import with separate column addresses per sheet. Then the source gets an “Import from sheets” window, where you pick a sheet from the list and open category detection for that sheet with the “Sheet operations” button.

    How to fix a wrong type choice

    A wrong choice doesn't break anything for good. On every price list import, this source's category directory is deleted and rebuilt. If you picked the wrong type, pick the right one, adjust the condition if needed and re-import the price list: the old categories disappear by themselves, there's nothing to clean up.

    Switching the type overwrites the condition you set up: the app writes the default value for the new type over it. The app gives no warning about this. If you edited the condition by hand — wrote special text, added a manufacturer check — you'll have to build it again after switching the type.

    So the order is: type first, then the condition. Not the other way round.

    Scenarios

    Categories are assigned during price list import, so each scenario ends with an import.

    The supplier writes the section name as a separate row, with its products below

    What to do. Pick the “Category row, followed by product rows” tile. The app fills in the condition on “Detailed settings” itself — “Price equals 0 AND Name is not empty”. If the heading row in the file really has no price, leave it as is. Import the price list.

    What you get. Rows that match the condition become categories. Each product between two such rows gets the name of the upper one in the “Price category” field. When a heading sits directly above another heading, the upper one becomes the parent, and the name has two parts joined with “ / ”.

    What you won't get. This type doesn't remove heading rows from the price list products — they stay there as items with a zero price. It doesn't build a third nesting level.

    Each product has a category column, and levels are separated by “/”

    What to do. Give the category column the address of the “Price category” field and pick the “Category is given in every product row” tile. The app fills in the condition “Price greater than 0 AND Name is not empty”. Import the price list.

    What you get. The directory is made of the distinct values of this column. If at least one name contains “/”, the app normalizes the spaces around it and splits the name into levels — up to ten. When the levels are in different columns of the file, map all of them to “Price category”: each column becomes its own level.

    What you won't get. If there's one column and the names have no “/”, there won't be a tree: each column value becomes a separate top-level category.

    Categories in the file are visible only by formatting

    What to do. Pick the “Categories have cell formatting” tile — the window switches to “Detailed settings” by itself. Import the price list. In the “Detected categories” list, clear “Active” on rows formatted as products and set a “Category level” for each remaining row. Import the price list again.

    What you get. The first import fills the empty list: each formatting combination is a separate row, marked “Active” with level 1. Categories are assigned only by rows marked “Active”, at the levels you set.

    What you won't get. If the supplier changes the formatting later, new variants won't be added to the filled list automatically.

    The supplier grouped rows in Excel, and there are plus signs to the left of them

    What to do. Pick the “Categories have row grouping” tile. The app fills in the condition “Price equals 0 AND Name is not empty” itself — leave it. Import the price list.

    What you get. Nesting levels are taken from the row grouping, and each product gets the full path of its category. Items whose name matches the name of a detected category are deleted from the price list products.

    What you won't get. CSV has no row grouping, so this type works only with Excel files (.xls, .xlsx).

    What to do so the price list categories are detected

    What to do when no categories were detected

    The app has no separate “no categories found” message, so a failure is easy to miss. The import log won't help either: the “Loading product categories” line appears in it only for types 3 and 4 and doesn't say how many categories were found.

    An empty directory happens in two cases: the price list really is flat, with no categories in the file, or detection is set up incorrectly. Only someone who has opened the supplier file and looked can tell one from the other.

    You can check in three places.

    1. The “Price list category matching” window, right panel “Categories from the price list”. This is where the supplier's category tree is built. If there isn't a single category, it says “No data!” instead of a tree.
    2. The “Products” tab in the source card, “Price category” column. If it's empty in all rows, no categories were detected. If it's empty in some rows, the condition didn't catch everything.
    3. The “Detected categories” list in the detection window itself — for type 3. An empty list means the price list hasn't been imported with this type yet, or the source has neither the “Price category” column nor the name column mapped.
    The right-hand panel of the category matching window with the categories found in the price list
    1Panel title
    2Category from the file
    1. The panel title — check here whether categories were found.
    2. A row in the tree is a category the program took from the file.

    What to do when the tree is empty:

    • open the supplier file and see where the category is actually written — and whether that matches the chosen type;
    • check the file format: for XML the chosen type doesn't work at all, categories there come from tags;
    • check the condition on the “Detailed settings” tab — if it's empty, types 1, 2 and 4 do nothing;
    • for type 2, make sure the category column has the address of the “Price category” field: without mapping there's nothing to take;
    • re-import the price list — the directory is rebuilt only during an import.

    A product whose category wasn't detected

    Such a product keeps an empty “Price category” field. It isn't in the category directory, and it doesn't appear under any branch of the price list category tree: the app has no separate “no category” node.

    This product isn't included when supplier categories are turned into catalog categories: a match like supplier category “Refrigerators” → my category “Home appliances / Refrigerators” won't affect it. It can get a catalog category only from distribution rules by name, AI matching by products, or from you manually.

    FAQ

    The supplier added new categories, but the “Detected categories” list doesn't show them. Why?

    The list fills up only once: while it has at least one row, new formatting isn't added to it. Rebuild it with the “Update category list from price list” button (how to rebuild the list).

    I set the category level to 15, but the app won't accept it. How many levels are allowed?

    Up to ten: “Category level” accepts a number from 1 to 10, where 1 is the top level. The level is the category's place in the tree, not a row or column number. The exception is type 1: nesting there is two levels only.

    Why do the imported products include items with a zero price and a category name?

    These are heading rows from the supplier file. Types 1 and 3 don't remove them — they stay among the products. Only type 4 deletes such rows. Otherwise, ignore rules remove them.

    Why doesn't the app detect the structure type by itself?

    Supplier price lists vary too much: what marks a category heading in one file is a row without a price and unrelated to categories in another. That's why a person picks the type after looking at the file (how to choose the type).

    Can the system detect a product's category from its photo?

    No, the app has no “category from image” feature: detection reads the file structure. If the supplier didn't provide categories, the category is chosen by the product name — with AI category matching or distribution rules.

    I want to extract a volume like “0.5 L” from the product name into a separate field — can a regular expression do that?

    Yes, but category detection doesn't do it — the “Regular expressions” window does: “List of price lists” menu → “Categories” → “Regular expressions”. A rule with “Name” as the source field runs during import. Learn more: Regular expressions for a price list.

    Where to find it and who has access

    The window has no menu item of its own — you open it while working with price lists. The label is the same everywhere: “Category detection”.

    From “List of price lists”
    Directories → Processing price lists → List of price lists → header menu → “Categories”
    From the source card
    “Additional” section → “Category detection” (except for and per-sheet settings)
    For a single sheet
    “Import from sheets” → sheet → “Sheet operations”
    From category matching
    right panel → “Additional operations” menu → last item
    Access right
    the “Category detection” window; there are no separate rights inside the window
    What you need
    an Excel or price list with mapped columns
    The price list actions menu with the Categories group expanded
    1Category detection
    2Regular expressions
    1. Category detection — opens the window this article is about.
    2. Regular expressions — a neighbouring feature that handles something else.
    The Additional section of the price list source settings with the row of buttons
    1Category detection
    1. Category detection — the second way into the same window, from the card of a specific source.

    If the window is opened for a specific sheet, the title gets “Settings for sheet:” and the sheet name: changes apply to that sheet only, not to the whole file.

    The window has two tabs: “Category structure type”, where you pick one of the four types, and “Detailed settings”, where you set the rule.

    The neighboring menu item “Regular expressions” has nothing to do with category detection: it extracts a value from free text into a separate field. Learn more: Regular expressions for a price list.

    Related topics

    Was this article helpful?

    Missing a feature? Tell us.

    We will add it for free if other users need it too.

    If only you need it, we will agree on it separately.

    Article contents

    Suggest an improvement

    Try Elbuz free