Sales Channel Maintenance via Excel Cross-Reference

For many shop exports such as Shopware 6 or Shoppingfeed this modern maintenance system is used.

In this system, only the shop categories are maintained in the PIM channel tree, not the products, and the products will link these categories in the sales channel maintenance.

While maintaining the tree, it might be tempting to recursively copy entire product trees into the channel tree. And you are even encouraged to do so! Make sure to extend the product in the channel, so that the link to the original PIM product element persists.

After all categories are created in the channel tree, the products need to be cleaned up from it. You can do so by creating a search similar to “Product number is not empty AND IsFolder = False”.

It is now useful, that the channels have their original product ID (PdmarticleID), which they extend. We can use that!

We now want to link the product to its category in the sales channel like this. But doing so for every product can be time-consuming.

But now our logical connection comes in handy. If you think about it, the extended product ID (PdmarticleID) of the target channel is the ParentID of the product. So let’s build a sales channel Excel/CSV import for all products to link the correct channel.

We need an Excel export of all affected products, an Excel export of the entire channel tree and the sales channel import template.

Let’s export the products.

(In my case there are only 4 products, in your case it will be far more.)

Do the same for the channels.

Now download the sales channel import template. You can find it here.

If we fill in the information, we already know, the sales channel import should look like this. The PdmarticleIDs are the PdmarticleIDs of our targeted products.

Only the LinkedRecordIDs will be the hard part. So let’s remember: The linked record IDs will be the channel ID (PdmarticlestructureID) of the channel with the ParentID of the products as its PdmarticleID.

For this use the command VLOOKUP() – this works both in Excel and Libre Office Calc.

I recommend to add all tables into the same excel file in different worksheets called “SCChannel”, “Products” and “Channels”. For the command to work, it might be necessary to change the order of “PdmarticlestructureID” > “PdmarticleID” to “PdmarticleID” > “PdmarticlestructureID” in the “Channels” worksheet.

Then enter the command similar to this (the columns can vary) into the “LinkedRecordIDs” column:

=VLOOKUP($Products.D2 (Column of “ParentID” of “Products”); $Channels.$C$2:$D$6 (Columns of “PdmarticleID” and “PdmarticlestructureID” of “Channels”); 2; 0)

This should result in an import file like this:

This file can then be imported here: