9 Replies Latest reply on Mar 31, 2015 2:42 PM by justinc

    Have barcode but retrieve product data from...where?


      I imagine that others have already gone through this process, and so I am hoping they will share their experience.


      What are some good resources for retrieving product data once you have scanned an item? 


      This is more for home/personal use, and so the item could conceivably be anything.  I am not talking about a store that would have a limited set of items and would have their own database of codes and products.


      Specifically I am using this to scan wine bottles, and am hoping to get most of the information about the wine from somewhere so I don't have to type it all in.   



      --  Justin

        • 1. Re: Have barcode but retrieve product data from...where?

          Hey Justin, I'm assuming you're scanning UPC codes? If so, you will probably need to work with a web service like this:


          UPC Database: XML-RPC Web Service


          The last lesson in the Integration Module of FTS: Advanced covers this kind of topic, if you're looking for an example. The basics are using Insert from URL (or a plugin) to call a web service, then parse the text data you get back.


          Hope that helps

          • 2. Re: Have barcode but retrieve product data from...where?

            Well, for my specific application these are most likely UPC or EAN codes.  The issue is finding a database that will reliably have actual data on the items being scanned.  I tried the one you linked but my test item didn't have any info.


            A few that I have tried have at least given me the manufacturer of the item, but no info about the item itself.  And this may be a lost cause, really, to use the UPC; a name search might be better, but I was trying to avoid as much typing as possible and make it easy to enter in lots of things.


            --  Justin

            • 3. Re: Have barcode but retrieve product data from...where?

              Can you send over a few examples of the codes you've scanned? There's almost a web service for everything these days, so there's bound to be something somewhere.

              • 4. Re: Have barcode but retrieve product data from...where?

                730564000344  - No results in most UPC DBs

                [Thurston Wolfe Zinfandel 8 Year Tawny Port

                Red - Fortified, USA, Washington, Columbia Valley, Horse Heaven Hills]


                088586498954 - got some results

                [Columbia Crest Chardonnay Grand Estates

                White, USA, Washington, Columbia Valley ]


                Results pulled from Cellar Tracker, their manual web form version.  I don't think they have an API...which makes sense from their point of view - they want web traffic.    It is the smaller wine makers that will most likely have more troubles.

                • 5. Re: Have barcode but retrieve product data from...where?

                  A number of years ago I started a wine database for my own collection. Ended up turning it into a commercial runtime solution and sold it. This was in the FM 6 years. I wanted to do the same thing with barcode scanning to look up product and review information. At the time the biggest library of info was Wine Spectator. They were willing to give access for thousands of dollars and purchasing ad space in their magazines. No real API or web service in those days. Uh, no.


                  Might be easier today but anybody that has that info is likely to want to charge for it or keep it to themselves and have people subscribe to their service. They may not want a competitor using their data. A lot of the info is also crowd-sourced by subscribers and not necessarily the review of established wine experts.


                  If you want a mobile app, look at Vivino. Scans labels and does a pretty decent job.


                  I've still yet to find a wine database that I really like (beyond my severely outdated system).

                  • 6. Re: Have barcode but retrieve product data from...where?

                    From what I have read UPC EAN is not a good way to sort wines as the same wine carries the same UPC for each vintage.


                    Sounds like you might be doing some manual work.

                    • 7. Re: Have barcode but retrieve product data from...where?

                      Yeah, I saw that cellar tracker seems to be the only site that matched both of those two. If you had the patience to read through their HTML, you could try to parse their search results (searching does return the result). This would work ok as long as they don't change the output of that page, but might break if they do, so definitely not a permanent solution, but one that might help in the mean time.

                      • 8. Re: Have barcode but retrieve product data from...where?

                        Just because a website doesn't have a formal machine-centric API doesn't mean you can't treat it like it has an API anyway. If you can look at it in a web browser, you can probably look at it with Insert From URL, and usually without any more effort than you're likely to spend reading an API's documentation anyway. (The experience can often be less frustrating this way, since there isn't an opportunity for your expectations from reading the documentation to be upset by the reality of the results you get!)


                        For the specific example of Cellar Tracker, it looks like the first URL format is:


                        http://www.cellartracker.com/pickproducer.asp?PickWine=on&szSearch=[UPC goes here]


                        The next step in their process requires a login we'd have more trouble doing with Insert From URL, but we *can* at least get the same information you posted to this thread. Using my TextBetween custom function, which I originally created specifically to facilitate this kind of web scraping, we can pull it out with:


                        Let ( [

                        _searchResults = TextBetween ( $htmlFromInsertFromURL ; "<table class=\"add_search_results\">" ; "</table>" ; 1 ; 1 ) ;

                        _wineRow = TextBetween ( _searchResults ; "<td class=\"name\">" ; "</td>" ; 1 ; 1 ) ;

                        _name = TextBetween ( _wineRow ; "<h3>" ; "</h3>" ; 1 ; 1 ) ;

                        _urlDomain = TextBetween ( $url ; "" ; "/" ; 1 ; 3 ) & "/" ;

                        _labelImageURL = _urlDomain & TextBetween ( _wineRow ; "<a class=\"photo_zoom action_exwide\" href=\"" ; "\"" ; 1 ; 1 ) ;

                        _details = TextBetween ( _wineRow ; "<p>" ; "</p>" ; 1 ; 1 ) ;

                        _details = Substitute ( _details ; ", " ; ¶ ) ;

                        _type = GetValue ( _details ; 1 ) ;

                        _country = GetValue ( _details ; 2 ) ;

                        _state = GetValue ( _details ; 3 ) ;

                        _region = GetValue ( _details ; 4 )

                        ] ;

                        /* ... */



                        You don't have to have much of a clue about HTML to make this work, either. Just:

                        • Get the content you want to scrape open in a web browser.
                        • Deconstruct the URL format to find out what you're automated routine will need to build.
                        • Do text-based finds in the HTML for the information you want, and use the unique-looking delimiters before and after that information with the TextBetween function to isolate those details.
                        • 9. Re: Have barcode but retrieve product data from...where?

                          Hey Jeremy,

                             Wow, thanks for the detailed reply on doing the scraping.  I had thought about going this route as a fallback, but thought the UPC database with an API would be better.  Maybe not.  And you have done much of the thinking work for me. 


                          --  Justin