0

Hopefully someone can help.

I have an eccommerce dropship website on Woocommerce which I update via CSV using WPAllImport. I currently download a CSV Spreadsheet from my supplier which contains current product prices and stock quantity. At present I am required to go through this spreadsheet to find the products I am selling and transfer the data from the suppliers spreadsheet to my own spreadsheet which is very time consuming. I am required to do so because the product IDs my supplier uses are different from the Product IDs which Woocommerce gives for my products and cannot upload their CSV direct.

when I download the CSV file from the supplier, I always save this as supplierstock.csv so that every new update overrides/supersedes any previous versions.

Is there anyway I can link my spreadsheet/database to supplierstock.csv so that when the new data arrives, my spreadsheet/database is automatically updated and populated with the most recent values?

Main problem is though, that the suppliers spreadsheet changes in appearance. The layout is the same but where products sit in the spreadsheet changes.

Can I link my product ID to their Product ID and then link the corresponding

stock from their sheet to mine so that when I download the new sheet. My database/spreadsheet recognises the product ID from the suppliers sheet and recognises the stock quantity cell and updates my spreadsheet accordingly?

Example data:

My Form

ID Parent ID Title Stock Price 00001 0 Product 1
00002 00001 Product 1 - Variant 1 10 £1.99 00003 0 Product 2
00004 00003 Product 2 - Variant 1 10 £1.99 00005 00003 Product 2 - Variant 2 10 £1.99

Supplier form

ID Title Stock Price PROD1 Product 1 - Variant 1 20 £2.99
PROD2 Product 2 - Variant 1 20 £2.99 PROD3 Product 2 - Variant 2 20 £2.99

So ideally I would like to link:

00002 -> PROD1 00004 -> PROD2 00005 -> PROD3

And link the price and stock cells associated with the suppliers ID to the cells in my own which associate to my linked IDs
so that when the stock or price of PROD1,2 or 3 changes, the corresponding cells for stock or price on my sheet changes so stock on my sheet should should update stocks to 20 and prices to £2.99.

I have read about linking Tables in Databases but an unsure as to how exactly to go about it or if it would be able to do what I am looking for

When I download my Suppliers sheet, the cell headers remain constant but the products can appear on different lines, so linking by cells wouldnt work. Apparently linking IDs should make things more accurate?

  • The short answer is: you can probably do something like that.  The longer answer is that, AFAIK, Excel has no ability to look at human-intelligible data and make sense of them.  For an even better answer, post examples of your data, including multiple versions of the one that changes.  Please do not respond in comments; edit your question to make it clearer and more complete.  Post your data as text, not as images. – G-Man Nov 6 '17 at 17:11
  • @G-Man I have added as best I could an example of what I am looking for – PaulMcF87 Nov 7 '17 at 10:02
1

You shouldn't need the Libreoffice approach if you are using WP All Import. That plugin allows you to map fields directly as well as set auto-update triggers.

All you'd need to do is create an import template one time with the proper mapping of columns and then set the trigger on the supplier's csv file wherever you upload it.

You might be helped considerably by adding the supplier's ID as your variation SKU or as an alternate SKU using attributes. This will give you a 1:1 mapping of variations between tables which is all you're updating anyway. This way you can have different product titles and as that is usually a bad field to key off of should the supplier change it.

If you're not using the premium version of that plugin which allows for the automatic trigger, you can still do this without the need for LO as an intermediary. (note, you don't 'need' the trigger, that's just if you don't want to have to step through the import process using your mapping template each time the csv changes)

  1. Upload the supplierstock.csv as it's own table in your WP database using phpMyAdmin. (be careful of course)

  2. Set up an UPDATE query using a JOIN between the wp_postmeta table which contains the stock and price info for your variations and the new supplierstock table. See this topic for a simple overview: https://dba.stackexchange.com/questions/21152/how-to-update-one-table-based-on-another-tables-values-on-the-fly

If you want to automate this, you can set up one or more triggers to fire each time you re-import the supplierstock.csv into that table. (be sure to import as an update, not a separate new table) You'll need at least an UPDATE trigger to set the stock and price as you mentioned, (as well as any other changeable fields) but you might also want an INSERT trigger to automatically add new products when the supplier does, and possibly a DELETE trigger to either remove discontinued items, or to use as a way to add a 'discontinued' tag/attribute that also zero's the stock/marks the product out of stock, but doesn't delete it from your database.

You can also accomplish that last part, probably in a safer manner using PHP. (to handle creating parent products first for new additions)

If you're using the built-in Woocommerce importer, I'd still go the phpMyAdmin route with or without triggers. Trying to use LO to process a special import csv first is just extra work. Certainly, you can't use Base on two different csv files or spreadsheet files as you can't run queries on more than one table with that type of datasource in LO. You'd be stuck with something like VLOOKUP and/or some complicated MATCH statements with nested IFs. Also, VLOOKUP requires adjacent columns. If the supplier decides to add a column in between the two you need, everything breaks. The SQL approach won't break on that case.

Essentially, either import tool is doing some sort of UPDATE JOIN function, so why bother doing that first in LO only to do it all over again using Wordpress? I'd just UPDATE JOIN once directly using the supplier.csv and my wp_postmeta table.

  • thanks a lot for your answer. Great detail and I hope will be very useful. Is going to take a lot of research on my part as databases are very new to me. Do You have any literature that you can point me in the direction of to help me achieve this? – PaulMcF87 Feb 8 '18 at 15:52
  • My main concern with this is that, although I am required to use the suppliers price as a guide, my sale price is not directly affected by my suppliers price, for example if my suppliers price increases by £0.10 I would not necessarily increase my price by £0.10 or even at all, I have a margin range I try to stick within which can be different depending on the product, I would update my price once my profit falls below or begins to approach the set margin level – PaulMcF87 Feb 8 '18 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.