Exporting and importing all product category links

Sander WestlandSander Westland Member Posts: 32

We are currently moving from eCom to omnichannel. However it's not possible to export the relation between products and the (multiple) categories it belongs to. Lightspeed only exports one category per product in their product export xls file.

But we have 12.000 items which all belong to multiple categories and its too much work to do this all again manually....

What would be the best way to make an export file with the API which can be imported in the new eCom environment en link all the products.

There a few steps involved in my opinion:

  1. Retrieve all productsCategories link through API call categoriesProducts() of current shop.
  2. Use EAN as the unique ID of the products to identify them between the shops.
  3. Map the current Category ID's to the new omni Category ID's as these are not the same.
  4. Make a excel import file containing the EAN code and new Category ID
  5. Import it into the new eCom

Am I missing something here, I've came so far to step 1 and have put all these relations into my dev Database.

Now for the category ID, can I maybe use their names so I don't need to map their ID's?

How to properly built the excel export file, how to built the file, can it be just EAN and CategoryID/Name per row?

1 comment

  • VintageWineGuyVintageWineGuy Member Posts: 115 ✭

    I am on Omni, but we primarily do brick and mortar, so we went the other way (POS to eCom). But a couple thoughts.

    Retail only allows 1 category, and it is mostly for reporting. You may not want your categories in eCom to be the same as POS. In eCom I have "Special Deals" and "90 pt Wines", but in Retail just have the wine in USA/California/Napa so I can report and track inventory.

    Because there is no exposed shared key between eCom and Retail, I copy the System ID into the Custom SKU field in Retail (with the API, I run a nightly batch) because most of my items don't have EANs (a lot of wine doesn't). Custom SKU does get moved to eCom items, so when I run extracts I can more easily join them up. Actually, I have never looked at how this works eCom to Retail, only Retail to eCom, so your situation may be slightly different. Name works most of the time for a join, but because you can have different names in eCom (and we do) it's not reliable.

    If you are working with eCom Items in the API, you won't be able to once you move to Omni. Omni locks Items to Retail, so you have to make updates on the retail side with the retail API. Unfortunately, there are several things in Item that you can't get to practically via API once they are linked - including weight and "Old_Price" - and you can only update them with spreadsheet uploads.

    If you are moving to a new eCom environment, you probably will have to:

    -Pull everything out of the old eCom

    -Import it in to your new Retail side with 1 category. I would suggest not marking everything "publish to eCom" with your first loads - you can still access weight and some other data via API on the retail side if the item isn't published.

    -Make sure products/inventory is right in Retail, then use another import to mark everything as publish to eCom and let Lightspeed automatically push everything to the new eCom

    -Update your Categories in eCom.

    Just some thoughts on some lessons learned from an omni user.

Sign In or Register to comment.