Update shopify stock levels via csv with no handle only sku

[CasaFenix]

I have csv stock level sheets that need uploading daily but they are in different formats, this is not the issue, the problem I have is I cant update stock via csv with only a sku/stock code number, unless I export from shopify and cross reference the exported handle name and my suppliers sku. This obviously takes a very long time.

I can keep a csv sheet with the handles listed but as items are added/removed etc, it means the master sheets still need constant updates.

In other software it has been easy, just a sku number and item total, but seems not to be possible with shopify, any ideas would be welcomed.

[Jonathan-HA]

Hey there,

If you're open to using a 3rd-party app, our EZ Inventory app allows updating the available inventory quantity in Shopify with just the SKU and Quantity columns.

In the app, you basically just create a configuration where you specify which columns in the CSV file to use for the mapping.  You can then keep re-using the same configuration every time you want to upload updated quantity data.

If you have a publicly accessible Google Sheets spreadsheet, you can also automate the process by pasting the spreadsheet URL in the app and specifying a schedule when to run it.

Feel free to contact us if you have any questions about this.

[Ablestar_Daniel · Accepted solution]

Hello,

This is something we've seen with a lot of stores and there's two ways I know of to fix it.

Google Sheets

If you want a free option you can use Google Sheets to generate the spreadsheet in Shopify's format for you. At a high level you would:

1. Export the inventory spreadsheet from your store and import it into tab 1
2. Create a second tab and copy your SKU + Inventory spreadsheet in there
3. Use the XLOOKUP() formula to fill in the inventory levels in tab 1 with the values from tab 2
4. Export the first tab as a CSV and go to 'Products -> Inventory' to import the CSV and update the inventory levels

Then, when you get the next version of the file, you can just copy it into tab 2, tab 1 will automatically update and you can send then export + import into Shopify.

Here's an example Google Sheets document I made showing how it works: https://docs.google.com/spreadsheets/d/1m4Y9UPw9vJuaK_MTCVAb93_9BriWAv96pzF4tnGVKEg/edit#gid=0

A third-party app

If you want to do this with less effort I would recommend checking out our Ablestar Bulk Product Editor. With the app you won't need to reformat your spreadsheet, you can undo modifications with a click and also have it automatically download the spreadsheet from the web.

Here's a short video walkthrough showing how to update products by SKU:

Happy to answer any questions you might have, either about the app or the Google Sheets method.

Best,

Daniel

[CasaFenix]

Thank you Daniel, that would indeed be a better work around, however it is looking like an app will make life easier. I will go look at the suggested, thank you.

[CasaFenix]

Hi

I wonder if you could help a little further please?

I am trying to recreate your suggestion in an excel workbook but cannot get the formula working, would it be different to the one you created in google docs? =XLOOKUP(I2,'SKU + Inventory Levels'!A:A,'SKU + Inventory Levels'!B:B, "")

I will attach a screed shot.

Thank you in advance

Michael

[Ablestar_Daniel]

It looks like there was some issue converting the file from Google Sheet -> Excel. Can you try:

1. Open up https://docs.google.com/spreadsheets/d/1dcUNE1Fw6wQ-aJ3TGuxkWjWcIxaoWw0H/edit?usp=drive_link&ouid=11...
2. Click on File -> Download

That should give you an Excel version of the file where the formula is working. If that still doesn't work could you tell me which version of Excel you're using?

Best,

Daniel

[Kalen_Jordan]

Out of curiosity, why do you need to update the spreadsheet/csv manually? What's the source of the inventory updates? Might be able to automate the whole process so that you don't need to deal with the spreadsheet at all.

[CasaFenix]

Hi, the stock level csv sheets come from a number of different suppliers
and are not in the same format as each other. The main issue is cross
referencing the 'handle' when updating stock levels.

[Kalen_Jordan]

Hmm but couldn't we build an automation to handle each format separately?

[CasaFenix]

Yes I assume that is possible. 

[Hippielife]

The Spreadsheet solution offered here isn't helping me as I have multiple suppliers. My export contains thousands of products that I don't want to update inventory, intermixed with those that I do. Therefore, the columns on a sheet created from my store export do not line up in position with columns on the supplier provided CVS. This is getting silly now! There must be a way of Shopify recognising and overwriting by the SKU instead of the handle when updating by CVS? (I can't pay the £60 per month that these apps are asking for to do such a primitive thing!)

[tewe]

@Hippielife 

using our WP Price Updater you can update your prices via the SKU only. But as far as I understand your request you want more. Could you share a sample spreadsheet so that we can investigate how far away we are from your request.

Kind regards

Thomas