update database when spreadsheet data changes

English
, , ,
#1

Hopefully someone can help.

I have also posted the question here: Sync sheets in Libreoffice - Super User

Keen to get an answer asap :slight_smile:

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?

#2

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 :heavy_check_mark: (upper left area of answer).

#3

@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.

#4

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. Sync sheets in Libreoffice - Super User gives an example of what I would like

#5

@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.

#6

@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?

#7

sorry. linked the wrong question above… a more detailed version of whjat I am looking for can be seen here… Sync sheets in Libreoffice - Super User

#8

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.

#9

@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.

#10

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

#11

@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

#12

@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.

#13

So, my question is - If something isn’t working, what is it and how did you test to show it doesn’t work? I’ve already given you a working sample to test with.

#14

@Ratslinger would like to thank you first off for all your help :-)… So if I link the two spreadsheets using Vlookup. then download an up to date spreadsheet from my supplier, with the title cells in the same position but where the stock details may be in different areas, IE product A moved from line 2 to line 22… the vlookup will realise that the data that it referred to on line 2 is now on line 22?

#15

@Paulmcf87 YES, YES,YES! I have stated this multiple times. But again, you can prove it to yourself with the test file sent. For example - Sheet3 (supplier data) MOVE (not just copy) line 2, for example, and place it on line 10. Then change the Stock or Price value. Now look at the result in Sheet2. It will find it in the newly placed spot and has the correct updated info. This is basic testing.

#16

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.

#17

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.

#18

@SteveR
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?

#19

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).

#20

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.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.