How can I efficiently update prices using a CSV file?

[carol_warenzak]

@rmanke I am working with North Star brands, a church supplier company and we have more than 10,000 SKU's. We are having a very difficult time trying to update our prices via CSV file. I saw under another thread you explaining how to do it one way but I am still very confused on the way I should do it since our suppliers send our price list not in a CSV file. Is there any way you could help? It would be greatly appreciated. 

[LukaszWiktor]

Hi @carol_warenzak

I'd be happy to help you with updating your prices. 

You say that your suppliers send price list not in a CSV file. What file format is it then?

[carol_warenzak]

Hello @LukaszWiktor,

Thank you for offering help. Most of my suppliers send the updated prices in an xlsx file. 

[LukaszWiktor]

I think I'll be able to help you. I've recently released an app named Stockeo that is intended to update inventory and product data from supplier feeds. The app matches variants by SKU so you don't have to do it manually. I've just implemented an option to update prices too.

At the moment Stockeo can read data from CSV and XML files. I'll add support for XLSX files soon.

Two more questions:

1. How do you receive the price lists? Do you get them by email? Or are they available for download somehow?

2. Do you need to apply some calculations on the price? For example, add your margin? Or is there already a recommended retail price in the Excel file?

[carol_warenzak]

Thank you for your suggestion for your app. It sounds like exactly what I need. To answer your questions...

1.) I receive them via email and they come in as an xlsx file most of the time. Their list will include but isn't limited to including the SKU number and the updated price.

2.) For the price I do certain percentages off of the retail price and they go in increments of 5%, 10%, 15%, and 20%. So, for example, they will provide their new retail price and if there is a discount I provide for that price it will go into our "variant price" and the original retail price will go into the "variant compare at price" when working on a CSV file. (Supplier's updated retail= $456 (Variant Compare at Price) North Star Brands Price= $433.20 (5% off and would be our Variant Price)

[LukaszWiktor]

Thank you for the clarification @carol_warenzak. I'll be working towards making the price modifications possible next week.

[MrRawTOcleanCSV]

We specify that it is we who use the application to process your file that you send us in Excel, csv, or other spreadsheet format.

You can use any column with numerical or alphanumeric data and even text (strictly structured like data in shopify's "handle" column) provided that the formats are the same in the new price list and the old price list. It's up to you to choose between SKUs, IDs, EAN, UPC, Handles...

It is up to you to send us the lists containing the new and old prices or other data that you wish to replace. As soon as a product from the old list is detected in the new price list, the old price (or old stock, old description, SEO, image URLs, whatever you want to replace.. ......) will be done automatically.

After 2 lists, one for the old products excluded by the supplier and another corresponding to the products newly introduced by him are automatically generated.

Feel free to contact us with further questions if you have any.

Thanks.

[LukaszWiktor]

Hi @aromaexclusive,

At the moment the SKU is required to macht variants in your store with the entries in the feed file. I'm open to implementing another option. What other parameter instead of SKU would you like to use?

Price updates are already possible. You just need to specify the SKU column, and the price column.

You can also define a price formula. For example, you can modify the price recommended by your supplier or you can add a markup to wholesale prices.

[motocross4u]

Hi Lukas

We have a deal with 1 supplier we get a % off there trade price can you system work this out?

Thanks

Sam

[MrRawTOcleanCSV]

Sir, 

Sorry I saw your message too late. Of course, we normally have to build a new listing with your sale prices calculated using the discount obtained from your supplier.

Thanks

Gérard TALLA

[Rein]

Hi, @carol_warenzak 

I understand that you are looking for a way to update prices in a bulk action using a CSV. However, your supplier gives you product updates via a non-CSV. 

In this case, I would highly recommend first off liaising with your supplier and providing them with a sample CSV file, or a copy of your own file first. 

To export your product list from your Shopify admin, go to Products:

1. Click Export.

2. From the dialog box, choose the products you want to export:

   • The current page of products
   • All products
   • Products you have selected
   • Products that match your search and filters.

3. Select which type of CSV file you want to export:

   • CSV file for Excel, Numbers, or another spreadsheet program. Use this format if you plan to use a spreadsheet program to edit your product CSV file.
   • Plain CSV file. Use this format if you plan to use a plain-text editor with your product CSV file.

4. Click Export products.

Sorting your CSV file in a spreadsheet program can cause product variants or image URLs to become disassociated. Importing a CSV file that has been sorted might overwrite your existing products with bad data, which can't be recovered.

Sending this file to your supplier and having them edit it would work best. However, say for example you want to copy a column from one to the other such as "Pricing" that would also totally work.

Then importing the products would only be a matter of uploading the file back to your admin, you can follow this guide here to accomplish this.

If the above is not what you were expecting, and the partner you talked with previously had a different method, can you give me an idea of what this might have been? Perhaps I could help decipher it for you. 

There is an app with a free and paid plan that might suit your needs a bit better here though. It is called Matrixify, formerly Excelify, which you can find here on our app store.

For other alternatives that might work for you, you can read up on other apps here.

[carol_warenzak]

Hello @Rein,

Thank you for your reply. I have done what you described to a certain extent. I have been taking an updated price sheet from my supplier with their SKU number and then exporting a CSV file from Shopify for the supplier's products I have. I then one by one find the matching SKU number from the supplier's list and then type the new price in on the CSV file I exported. I have almost 1,000 products with different variants that need these updated prices so it takes hours and I was curious if there was a faster and easier way. I will try asking for a CSV file from my suppliers but some do not provide one. 

[Nick007]

use a vlookup function to match the price with the sku in seconds.  just google "vlookup in excel" on google for a step by step tutorial.  

[Alison_Ablestar]

Hi Carol,

My name is Alison, I'm the Customer Success Specialist for Ablestar apps.  I'm sorry for the late reply on this, but our Bulk Product Editor app can update Shopify products with either a CSV or an XLSX file.

When you run a spreadsheet edit with the Bulk Product Editor app, the first column in the file is always the match field.  This is the field the app will use to identify the products and/or variants to edit.  For price and inventory updates, we usually use 'sku', 'barcode', or 'variant id' for this.  Any other columns included can be used to update product data, but you only need to include the columns you want to edit.  If there are more columns than that it is ok, you can 'unmap' those columns before you run the edit.  You can read more about spreadsheet edits here, https://support.ablestar.com/article/23-editing-products-by-spreadsheet.

Another advantage to using the Bulk Product Editor is that you can 'undo' edits.  This is especially useful for setting up sales, as you can 'undo' the edit when the sale is over.

The app currently has a free version available that allows for 5 edits in any 30day period, and each edit can consist of up to all the products on your store, so you can try it out without incurring any costs.

If you have any questions about the app you can reach us directly at support@ablestar.com.

Best,

Alison

[MrRawTOcleanCSV]

Dear Members,

I may be too late, but better late than never. I manage the back office of a shop with more than 200,000 products (from more than 30 suppliers, some having the same products but with different unit prices). In many cases, inventories and prices change every week, or even every day, and suppliers do not always have the possibility of synchronizing their platform with ours. We have an application on VBA (which is a language just hidden behind Microsoft Excel) which allows us to review prices, quantities and packaging and above all to compare (by putting side by side the prices of several suppliers for the same products). We can review/compare 20,000/30,000 product prices in less than 10 minutes. In addition, by comparing the old price file and the new one, we show you the list of products that have been newly introduced and those that have been released. This is unique since not all of the apps listed in this post that I use do this. Imagine a case where your supplier excludes from its previous inventory 1512 products out of 32420 and integrates 453 new products. You will receive a file of 32420-1512+453=31361 products. But between the lines, it will not be easy for you to have a listing of excluded products and that of new products inserted. Your supplier may have mistakenly or knowingly increased or lowered certain prices excessively or even modified the sales units of some products without your knowing it, which may lead to sales price errors. We handle all this case and do it for you in a timely manner. Our limit is that of the number of rows and columns of Excel. Also imagine the case where you have inventories of more than 20,000 products from 3/5 different suppliers having more than 5,000 products in common. It will be very difficult for you to know who in the end is the cheapest and on which product. We can do the price comparison for you.

Our billing method

A) PRICE REVISION AND LIST OF NEW PRODUCTS AND PRODUCTS EXCLUDED FROM INVENTORIES

1) Minimum of 30$ up to 5000 rows and 20 columns of Excel file of products

2) Beyond the number of rows and columns above, an additional $5 per column or batch of 1000 additional rows

3) 5$ each lot of 0 to 500 excluded or new products.

B) COMPARATIVE STUDY OF PRICE LISTS FROM VARIOUS SUPPLIERS
- Invoicing on case study

C) Apart from these types of tasks we also help to build the product files by creating an assignment table of image URLs from the catalog of your suppliers' websites. We submit an offer according to the case and according to the feasibility.

You can contact me inbox at the e-mail address: gerardtalla@yahoo.fr

Thanks

[MrRawTOcleanCSV]

I saw this message very late. What is the format the product file received from your supplier? Pdf? MS Word? MS Excel?