Ask Your Question
0

update database when spreadsheet data changes

asked 2017-11-06 13:14:35 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hopefully someone can help.

I have also posted the question here: https://superuser.com/questions/12660...

Keen to get an answer asap :-)

I have an eccommerce dropship website. 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.

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?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2017-11-06 16:57:18 +0200

Ratslinger gravatar image

updated 2017-11-08 00:31:28 +0200

Hello,

A couple of items first. Please do not post questions as Wiki as it doesn't help anyone. Also, no need to delete one question and post the same content as a new question. Just edit the original question with a minor change and it will again rise to the top of the list. If I recall correctly, your original question stated you were only using Calc but open to using Base. You should state that in your question as the original was deleted.

Now, your question. I hope anyone else with Calc expertise will answer as my main experience is in Base.

From my observations, why not import the supplier spreadsheet into your spreadsheet as a new sheet. Then using your list of products get the needed information from the supplier sheet using something like 'lookup' or 'vlookup'? It seems then that whenever a new supplier list is available, just import it & done.

Edit 11/07/2017:

Just to give an example I put the attached together. Sheet1 is just as your sample. Sheet3 represents the Supplier info and here is where you would update that data. Sheet2 is almost a copy of Sheet1 except "Stock" & "Price" columns use vlookup. To see what happens on Sheet2 when new information is placed on Sheet3, change a Price or Stock value on Sheet3. The view Sheet2 to see results.

Sample: vlookupTest.ods

If this answers your question please click on the ✔ (upper left area of answer).

edit flag offensive delete link more

Comments

@Ratslinger Thank you for your reply. I have been importing the products in this way until now. However I have 400 products each with at least 3 variations. My supplier has in excess of 2000 products again with variations. trying to find the products in amongst this takes a lot of time. I work 9-5 and have a family so do the website on the side and need something quicker to help.

Paulmcf87 gravatar imagePaulmcf87 ( 2017-11-07 12:05:36 +0200 )edit

The other problem is that my suppliers spreadsheet is in no particular order. different variations of the same product can be at different ends of the spreadsheet. https://superuser.com/questions/12660... gives an example of what I would like

Paulmcf87 gravatar imagePaulmcf87 ( 2017-11-07 12:06:17 +0200 )edit

@Paulmcf87 Not sure you saw what I wrote about possibly using 'vlookup'. The only common ground between your table and the suppliers is the "Title". Even in Base you need a common field to 'Link' items in one table to items in another. So right now your link is the Title. As for supplier info in no order, vlookup can be set for that. Chapter 13 of the LO Calc docs (found here) discusses vlookup.

Ratslinger gravatar imageRatslinger ( 2017-11-07 15:09:44 +0200 )edit

@Ratslinger I will need to have a look into vlookup, not sure how it works. I was under the impression that with databases, linking tables could be used. Would vlookup be a similar idea?

Paulmcf87 gravatar imagePaulmcf87 ( 2017-11-07 17:28:40 +0200 )edit

sorry. linked the wrong question above... a more detailed version of whjat I am looking for can be seen here... https://superuser.com/questions/12660...

Paulmcf87 gravatar imagePaulmcf87 ( 2017-11-07 17:30:39 +0200 )edit

vlookup is fairly simple to use. This is possibly the easiest route. With databases, as I stated, you CAN link one table to another - you just need a common field for the link (actually a relationship). You will find the same is true with vlookup - using a cell value in your information the supplier information is searched for a match & when found the appropriate secondary cell data is returned. Once you set this up it should be just dropping in new supplier file & your data is updated.

Ratslinger gravatar imageRatslinger ( 2017-11-07 19:33:01 +0200 )edit

@Ratslinger Thank you for taking the time to create that spreadsheet to demonstrate and I can definitely see, from playing around with it, how it could work.

My main issue, though, is that my suppliers spreadsheet has no order to it...

for example if I download it today product 1 variant 2 could be on line 2 but tomorrow it could be on line 22.

Paulmcf87 gravatar imagePaulmcf87 ( 2017-11-08 10:44:46 +0200 )edit

I wish their spreadsheets where better organised lol

So ideally, I need something where the sheet recognises the products unique id and then knows take the corresponding value from, say, column d to populate the data.

so I am matching terms by ID as opposed to Cell

Paulmcf87 gravatar imagePaulmcf87 ( 2017-11-08 10:46:43 +0200 )edit

@Ratslinger had another play around. it seems the only way to search is via Column number. for this to work for me, I would need to be able to search via Rows so I can select Row D for example as being the row which holds the relevant data

Paulmcf87 gravatar imagePaulmcf87 ( 2017-11-08 13:05:05 +0200 )edit

@Paulmc87 I really don't understand you last few comments only because all of what you stated already works plus more you don't realize. A) Look at the documentation mentioned; B) Sequence, table size, and even blank lines does not affect the results - Have you even tried it because I did before posting; C) If the Title isn't unique (and you want to search on something else) you'll have to add that field to your info & use that for searching; D) It already returns row information.

Ratslinger gravatar imageRatslinger ( 2017-11-08 16:03:50 +0200 )edit
0

answered 2018-01-28 03:55:35 +0200

Adrien gravatar image

I posted an answer on your SuperUser topic. I really don't think LO is the right tool for this job, and is certainly unnecessary if you're using WP All Import plugin.

What you want to do here is update database table columns when the same columns in another table update and you want to tie the two columns based on the variation ID. (one ID used by WC, the other by your supplier) With much fuss and effort you 'might' be able to accomplish the task with LO Calc, it's just not necessary given the workflow you already have.

edit flag offensive delete link more

Comments

As a follow-up to Adrien's response concerning whether '"LO is the right tool for this job"; LO can be the right tool. I believe that you have a structural problem. The purpose of a database is to store information. The purposed of a spreadsheet is to evaluate scenarios. So your data should be stored in a database. Changes to the database and/or filtered results, would then be evaluated by the spreadsheet. Essentially your structure is backwards.

Steve R. gravatar imageSteve R. ( 2018-01-28 15:26:48 +0200 )edit

@Steve R. I have since outed to use purely spreadsheets and use a vlookup.

Would there be a more user friendly/simple option available using spreadsheets and DBs together?

Paulmcf87 gravatar imagePaulmcf87 ( 2018-01-30 12:17:16 +0200 )edit

First, you should examine how your supplier stores their data. The implication from your post is that the suppler has it as a spreadsheet, which would be unfortunate. In any event, you would need to create a database that mimics the suppliers fields. This would include a logical field (established by you) to identify which products you sell and which you don't. After downloading the supplier information, you would need to import the suppliers information into the database. (see next comment).

Steve R. gravatar imageSteve R. ( 2018-01-30 17:06:24 +0200 )edit

Don't worry about importing irrelevant data from the supplier. That issue is solved by the logical field identifying which products you sell and which you don't. Subsequent database queries, based on that logical field would give you a list of your products and quantities available. (This is a brief outline of the approach required, not a complete solution.)

Steve R. gravatar imageSteve R. ( 2018-01-30 17:13:56 +0200 )edit
0

answered 2018-04-02 17:04:20 +0200

Good question about database and spreadsheet, there's so much we can do with that that we'd need a whole article to dig more into it. Actually our team at Zenkit dug into this topic and wrote and article on our blog, we'd love to have your feedbacks: https://blog.zenkit.com/spreadsheets-vs-databases-e11d5f1ca92c

edit flag offensive delete link more

Comments

This answer and link provides no solution to the actual question!

Ratslinger gravatar imageRatslinger ( 2018-04-02 17:11:08 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2017-11-06 13:14:35 +0200

Seen: 979 times

Last updated: Apr 02 '18