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?