Online databases: getting book data from Amazon
The macro described in this article uses Amazon's free E-Commerce Service database to fill in book details in a Cardbox database, given the ISBN of the book. The macro is simply an example and you can use it as a basis for your own applications.
You can download the macro, together with a database that works with it.
A quick description of the macro
Here are the important parts of the macro.
Const AmazonAccessKey="E16M1FTZZR05C1JB9YJ82" Const WhichAmazon=".co.uk"
The Amazon Access Key is required so that Amazon know who is making the request: you can obtain one for yourself from Amazon, as described below. WhichAmazon lets you choose between ".com" for amazon.com and ".co.uk" for amazon.co.uk. The information on the two databases is often different, and you will have to decide which one suits you best.
If Editing Then ... End If
We have designed the macro like this: if you are already editing a record then it takes the ISBN from the current record and retrieves the book data for it. If you aren't, it asks you for an ISBN, retrieves the data, and creates a new record for that book.
Function GetDataFromISBN(ASIN_In) Set MSXML = CreateObject("MSXML.DOMDocument") XMLURL = "http://webservices.amazon" & WhichAmazon & "/onca/xml?Service=AWSECommerceService" + _ "&AWSAccessKeyId=" & AmazonAccessKey + _ "&Operation=ItemLookup" + _ "&ItemId=" + ASIN_In + _ "&ResponseGroup=ItemAttributes,Subjects,EditorialReview" Loaded = MSXML.Load(XMLURL)
The macro uses CreateObject to create a copy of the Microsoft XML Parser. It then creates a request for information about the specified ISBN and asks the Microsoft XML Parser to send the request and receive a response from Amazon. The format of the request is defined in Amazon's documentation for the E-Commerce Service.
Set authors=entry.getElementsByTagName("Author") For j=1 to authors.length : ReportAuthor authors(j-1) : Next Sub ReportAuthor(author) If txtAuthors<>"" Then txtAuthors=txtAuthors & ", " txtAuthors = txtAuthors & author.text End Sub
Some elements of Amazon's response can have multiple values. For example, a book may have more than one author. In these cases the macro creates a single string containing all the authors' names separated by commas.
txtPublisher=TextValueOf(entry.SelectSingleNode("ItemAttributes/Publisher"))
Other elements, such as the name of the publisher, can only have a single value, so the macro extracts the information directly.
How you can play the macro
You can use Tools > Play Macro and select the macro by name.
You can press the F5 key and select the macro by name.
You can press the "New Record" button on the toolbar.
You can press the anonymous grey button to the right of the ISBN field in the Cardbox record.
Signing up for Amazon Web Services
Before you can use this macro you need an Amazon Web Services account, which is free of charge. To get one, go to Amazon Web Services and follow the instructions. After you have signed up you will receive an Amazon Access Key, which you should put into the appropriate place at the beginning of the macro.
The service that is used by this sample macro is the Amazon E-Commerce Service. At the time of writing, Amazon made no charge for this, and they are unlikely to charge for it in the future. They do stipulate some terms and conditions which you should read carefully before you use the service.
Full documentation on the requests you can make to the Amazon E-Commerce Service and the information you can retrieve is in the Technical Documentation section of the E-Commerce Service "Resource Center". We cannot give links to this directly because the links are deep inside the Amazon site and may change.
Possible extensions to this macro
Batch editing - if you decide to fill in book information for a whole set of records, it is straightforward to adapt the macro to do this; but be aware that Amazon currently require you to submit not more than one request per second. You will need to insert a command such as
Sleep 1000
into your macro so that it obeys this condition.
Barcode readers - if you are cataloguing an existing collection then you may want to input the ISBNs using a barcode reader rather than typing them in by hand. You will have to extend the macro so that it converts the barcode from the 13-digit UPC to the 10-digit ISBN format. The simplest barcode readers behave exactly as if you had typed the digits of the barcode at the keyboard. Some more sophisticated ones can type extra characters at the beginning and end (usually called "preamble" and "postamble") and you could streamline your operations still further by getting the "preamble" to launch the Cardbox macro directly, so that you never have to touch the keyboard at all.
Searching by author or title - the Amazon database can be searched in various ways, not just by ISBN. You could extend the macro so that it searches by author or title, but you would have to modify it to handle the possibility of more than one book being found by the search.
Querying other services - you can apply the principles illustrated in this macro to other Amazon databases, and to other online databases in general, as long as they accept HTTP requests and return data in XML format.