Quite often, when getting Product data for updating from suppliers or other data sources, you have just the product SKU, but not the Product ID or Handle to identify the item, and you need to update Shopify products by SKU.
That’s ok – now the Excelify.io app can identify your Product by only the SKU number, and do all the same advanced updates with it.
- Update existing Product Handle and/or Title just by having Variant SKU
- Update existing Product base fields (like Tags, Published, and so on) by having just the Variant SKU;
- Update existing Product Variant fields (like Variant Inventory Qty, Variant Price, and so on) by having just the Variant SKU;
- Update existing Product Metafields by just the Variant SKU;
- Import Product Images by identifying products by Variant SKU;
- Delete Variant from the Product by having just the Variant SKU;
- Delete the Product itself by having just the Variant SKU.
SKU number column is called “Variant SKU” (even if your product doesn’t have variants).
How the import by SKU works?
In Shopify, the SKU is the attribute of the Variant. That means that each Product can have one or many variants, each having its own SKU number.
First, the app will index all SKUs in your import file – grouping rows by SKU value. Then the app will go through your whole store and update all the products/variants which will match the SKUs from your import file. It will not change any other variants or products.
On your store, SKUs don’t have to be unique. The app will update all SKUs that will match SKUs from your import file.
Logically, if you import table of many SKU numbers, then some of SKUs might belong to the same Product. Before making any changes to that product, the app is taking all that into account and is grouping together SKUs that belong to the same product. That will ensure the consistency of data in all product variants and will improve import speed because each product will be updated just once.
After the import, in your Import Results file, you will see the additional columns “ID”, “Handle (Ref)”, and “Variant ID” added from your store, so that you see, which products and variants actually were updated.
Here are examples of all the mentioned scenarios.
Update Product Handle or Title by SKU
You can also update your Product Handle and Title by adding [ID] to your Variant SKU column name, telling the app to force identify Products by the SKU not Handle or Title as it would be by default.
Check out more detailed tutorial on to force Shopify to identify Product by Variant SKU with Excelify.
|Variant SKU [ID]||Handle||Title|
Update Product base fields by SKU
For example, to update product Tags for each SKU, import columns like this:
|SKU-1001||Tag1, Tag2, Tag3|
|SKU-1002||Another Tag1, Tag2, Tag3|
|SKU-1003||And Another Tag1, Tag2, Tag3|
Update Product Variant fields by SKU
For example, to update product price and inventory quantity for each SKU, import columns like this:
|Variant SKU||Variant Inventory Qty||Variant Price||Variant Cost|
You can add additional columns to this import, to update them all at once.
Notice the empty Variant SKU value – that row will show as Failed.
Update Product Metafields by SKU
For example, to update product Tags, inventory quantity, and Metafields, you would import columns like this:
|Variant SKU||Tags||Variant Inventory Qty||Metafield: your.metafield_name|
If the several SKU will belong to the same product, then the Tags and Metafields for that product will be set only from the first SKU that belongs to that product.
Import Product Images by identifying products by Variant SKU
If you need to add images to Products, but you only have the Variant SKU, you can do import like this:
|Variant SKU||Image Src||Image Position||Image Alt Text|
|SKU-1001||https://images.com/image1.png||1||Image about first thing|
|SKU-1001||https://images.com/image2.png||2||Image about second thing|
|SKU-1001||https://images.com/image3.png||3||Image about third thing|
All the products that will have this SKU, will get those images imported.
Delete Variant from the Product by SKU
If products have several variants, and you just need to delete specific variants and keep the other variants of those products unchanged, import table like this:
|Variant SKU||Variant Command|
Also, notice here the empty Variant SKU value. That row will show as Failed and will not do anything.
Delete Product by SKU
Notice the difference from the previous example where you deleted just one variant from that product – here you will delete the whole product. In this case, even if the Product will have 100 variants, if just one of its SKU will be found, the whole product with all its variants will get deleted:
Typically. you would want to use this method to delete products which have just one variant. But you can see how you can go beyond that, too.
Good to know
- If you need to make your SKU numbers unique across your whole store, you can do the full export of Products with their Variants, and create the Pivot Table in Excel, where you put “Variant SKU” as Rows, and count Variant SKU values. Then you can see, which Variant SKUs count is more than 1 – then you can fix those in your store manually, or using the Excelify.io app to do that in bulk.
- If you need to get the list of Products which have certain SKUs, you can just import the file with one column “Variant SKU”. In the Import Results file you will see all the products, their ID, Handle and Variant ID – which has which of your SKUs from your table. Then you can update their Titles or even Handles.
- You can add additional columns to this import, to update them all at once. You can combine base columns, Variant columns, and even image columns in the same import.
- Column order doesn’t matter.
- You can update Product by knowing the Barcode or Options of Variants, but then you need to have Product ID or Handle too. This is how to do it.
- You can also use Excelify to bulk update your Shopify Product SKUs.