Amazon import

kisscass · March 26, 2005

Hi,

I am currently working on a library database to catalogue and track loans for books and cds. A great time-saving feature for adding new records would be the option of searching the Amazon web services database for matching records based on the ISBN or UPC of the item then importing the relevant data into the library database.

I've had a look on the Amazon Web Services website, which is very technical for an XML novice like me. I've also tried out the sample database from this address: xslt library but it doesn't work (I updated the Amazon associates id and the stylesheet location as appropriate). That file seems to be a few years old so perhaps the format is incorrect now.

Could anyone help me to set up this facility? The fields in my library database into which the Amazon data would be imported are:

Item

Composer/Author

Artist/Performer

Genre

Type

Label

Fenton · March 27, 2005

I've been messing about with this, after seeing your post. I remember trying his file, when it first came out, and it worked. Later I tried it, and found out, as you did, that it no longer did. But I didn't investigate. Now I have.

Short answer is that you're right, that file is out of date. There are 2 problems, both fairly easily fixed, with a little work.

The format of the request, in a FileMaker calculation field, begins with the following:

http://xml.amazon.com/onca/xml?v=1.0

In the Amazon Web Services documentation, it says that you now have to use version 3, with this format:

http://xml.amazon.com/onca/xml3?

Otherwise the URL is much the same. You should read more of the documentation in the AmazonWebServices download. You can ignore most of the example folders, as they're not for Macs, nor FileMaker. Open the "index.html" page, in the API Guide folder. It's like a web site. There are examples of what the URL should look like.

Even better, if you go to the Amazon site, to the Web Services, then, in the left panel, near the bottom, there is a "Tools" section, with "XML Scratch Pad." This opens a cool little form, where you enter your criteria for any kind of search, then it builds the URL for you. You can copy it to reconstruct later with FileMaker fields.

You can also Preview the result. This results in a page of text in your browser, just the results (not very useful). But if you View Source you'll see the entire XML returned. Copy/paste that into an xml editor (BBEdit, TextWrangler) to see what you're going to be matching against, with your xsl.*

You must have your own Amazon Developer ID to do this. It's free, only takes a few minutes to get. (I think the one he includes is expired.)

Which brings me to the 2nd problem. His XSL stylesheet that he included is fine. It would still work. BUT, as he points out, it does NOT work as a local file. It MUST be on an a web site, then referenced as a URL. He does this in his FileMaker file, but it is NOT THERE anymore (as he warned could happen). Neither is there a new location (that I could find), even after reading the FileMaker XML discussion board. So you just have to take it and put it on your web site; then you know where it is.

It will work. I've gotten it to work, with only the above minor modifications. But I don't feel comfortable posting an example of the modified file, because there are several copyright notices. Probably it would be fine, but I'd want to contact him first.

A 3rd problem, in your case, is that you want slightly different fields than his did. His is strictly for books. Yours is more for music. I imagine yours is possible, but you'll have to build it. XML means "extensible," which means it's flexible; which generally means you'll need to either build it or tweak it.

*You can also use AppleScript and do shell script "cCurl theURL" to get the XML result

kisscass · March 27, 2005

Hi Fenton,

Great minds think alike. I was just about to update my post as I have gotten the ISBN search to work by changing the URL format in accordance with the 3.0 specification of the Amazon Web Services. (They've got version 4 now but it looks a lot more complicated to me right now.)

I was able to get it to work by linking to the stylesheet within the import script instead of having the stylesheet online.

I'll still have to add some extra bits to the stylesheet to include music items but at least I can see it working at last.

Thanks,

PJ

Fenton · March 28, 2005

Yes, I guess the "stylesheet must be online" only applies to using the XSL stylesheet option within the Amazon URL. I sort of wondered about that, but then forgot, because it worked when I put the XSL onto my web site. FileMaker has its own XSL option also.

The music and the tracks are about the same as books. I wasn't able to import the tracks as multiple repeating fields. Either it can't be done, or I did something wrong. It looked right, but only got the 1st repeat. It's easy enough to do a 2nd scripted import, and bring them into their own table, as records; which is actually more relationally correct.

kisscass · March 29, 2005

I've been playing around with searches and discovered that AWS doesn't allow UPC searches for non-US sites. This makes it difficult to find cds, which I'm most interested in looking up. As I'm in the UK I am finding that only the ISBN searches work. I was thinking about trying keyword searches instead, but these often bring up multiple records for each query. Is there a way of limiting the number of imported records to just the first one?

Fenton · March 30, 2005

Yes, it wouldn't be hard to limit the results to the 1st one.

<xsl:for-each select="/ProductInfo/Details[1]">

But, as you said, you could get many results. The chance that the 1st one is the one you want are pretty slim, unless you created a very tight search.

It is also easy to get the number of results returned, as well as the number of "pages" (10 per page). So, if you only found 1, you'd know you got the one you wanted, maybe.

I'm using that to know how many times to run the import, rather than his Max_results field. Either way works. They are both top level elements in the results returned. So I create a FileMaker fields for them. I just bring it in with each detail, rather than write a special routine to set it into a global field in only the 1st record; which would be more efficient. I'm lazy.

<xsl:attribute name="FOUND"><xsl:value-of select="/ProductInfo/TotalResults"/></xsl:attribute>

<xsl:for-each select="/ProductInfo/Details">

<ROW>

<xsl:attribute name="MODID">0</xsl:attribute>

<xsl:attribute name="RECORDID"><xsl:value-of select="position()"/></xsl:attribute>

<COL>

<DATA>

<xsl:value-of select="../TotalPages"/>

</DATA>

</COL>

But just getting the 1st hit is not really what you want to do. What you want is to do your search, but only get the 1 that matches your UPC.

It's pretty simple to write a test for that in the XSL, if you could hard-code the UPC into the xsl, or create a parameter or variable with the value. At the top level:

<xsl:param name="theUPC" select="'default value'"/>

<xsl:for-each select="/ProductInfo/Details[upc=$theUPC]">

But (and this is a big but), how to do that with FileMaker is a little difficult. One way would be to export the XSL stylesheet as a text export, on the fly, holding the entire thing in a global or unstored calculation, so you could put the UPC in first. Seems like that would work.

Another way would be to use another tool, which would allow you to pass a parameter to a stylesheet which was dedicated to this UPC test. I was just messing about, and managed to do this with AppleScript, using a Scripting Addition "XSLT Tools.osax" from LateNight Sofware:

http://www.latenightsw.com/freeware/XSLTTools/index.html

The main reason for using it is that it has a "with parameters" command. It's fairly easy to get the value from FileMaker, and fairly easy to use the above command.

I used curl in AppleScript to do the request, saving the result to a file (I had trouble otherwise, because of the " escaping in AppleScript; but there's probably a way around that).

For those still awake, this is what my AppleScript looks like. I haven't included the FileMaker stuff yet, but that wouldn't be hard. This is just a test:

set theSearch to "Savoy%20Brown"

set theUPC to "042284401821"

set myID to "[your Amazon developer ID]"

set the_URL to "http://xml.amazon.com/onca/xml3?locale=us&t=webservices-20&dev-t=" & myID & "&ArtistSearch=" & theSearch & "&mode=music&type=heavy&page=1&f=xml"

set the_URL to quoted form of the_URL

set xsl_path to "Macintosh HD:Users:fej:Documents:FileMaker:My_FM:XML:amazon_ws:Music:XSL:AS XSLT Tools:music_UPC_hvy.xslt"

--this is the path to your xsl file

set xml_path to "Macintosh HD:Users:fej:Documents:FileMaker:My_FM:XML:amazon_ws:Music:XSL:AS XSLT Tools:amazon_music_hvy2.xml"

--this is the path to save the full xml search result

set xml_file_path to "Macintosh HD:Users:fej:Documents:FileMaker:My_FM:XML:amazon_ws:Music:XSL:AS XSLT Tools:UPC.xml"

--this is the path to the UPC result

set output_path to quoted form of POSIX path of xml_path

--set theText to do shell script "cURL " & the_URL without altering line endings --test, gets raw xml

set theXML to do shell script "curl " & the_URL & " -o " & output_path without altering line endings

set theParameters to {class:XSLT parameter, name:"theUPC", contents:theUPC}

transform XML alias xml_path using alias xsl_path with parameters theParameters saving as file xml_file_path

tell application "Finder"

activate

try

reveal alias xml_file_path

end try

end tell

Fenton · March 31, 2005

I have a question for kisscass or anyone. I am having mucho trouble getting FileMaker to use a local xsl file to transform raw xml returned from the Amazon http request. It works fine if I upload the xsl to my web site, then use Amazon's stylesheet option to link to it. But if I specify a local file I get a cryptic socket error message and/or FileMaker quits.

That is one reason I went through all the conniptions with AppleScript. At least it's stable. FileMaker Import XML is not, in this scenario.* I'd just like to know if it's only me.

*It will also freeze if you try and define an import file reference to an existing file. The dialog goes completely gray, and nothing but nothing happens. You have to force quit. On reopening the script you were creating is gone. Sometimes the xsl file is gone too. Fortunately these are just example files I'm playing with.

STP · June 1, 2005

I was fooling around with this thing tonight as well, and it seems I'm almost there, but not quite. As far as I can tell, my problems are in the xsl template. I have the URL calculation formatted correctly, but I can't get FMP to recognize or do anything with the info. Can anybody help? All I really want is to type in a UPC code of a CD and get back the Artist and Title information. Been pulling my hair out all night....

Fenton · June 1, 2005

Why don't you post your xsl file, if that is the problem. We can see if there's anything obvious wrong. There are other factors however. I only got the xsl to work by loading the xsl file to my web site, then using the Amazon option to specify the xsl file, at the end of its URL. I could not get it to work with a local xsl file, specifying it within the XML Import step. I didn't finish the UPC search, though I got the Artist search working fine.

STP · June 1, 2005

Yeah, I have the XSL template sitting on my server. I'm pretty much using the same one that came with the xslt library, and have been trial-and-erroring my way through it. Little to show for my efforts so far. I'm wondering if this line is still correct:

Since the closest I can get to making this actually work is to have it go through the whole process but import zero records, I'm thinking maybe that line represents some sort of file path that's out of date now? Just a guess....

STP · June 1, 2005

Oh crap.... I should have posted it this way:

Sorry 'bout that....

STP · June 1, 2005

Grrr... For some reason I'm not getting the opportunity to preview my post. Anyway, i was trying to post the line that includes the file pathh "ProductInfo/Details" which in in the RESULTSET section of the template.

OK, I've cluttered this place up quite enough!

QuinTech · June 1, 2005

Fenton It is my understanding that an HTTP xslt is much more reliable than a local xslt. I think it boils down to, basically, a bug in FMP. If you want to use the local one, apparently rebuilding the script from the ground up is necessary.

STP: When posting XML code, use the BBCode tags for code -- that is, [ c o d e ] (with no spaces) and [ / c o d e ]. Also, instead of writing the usual 'less than' symbol to start a tag, use & l t ;

STP · June 2, 2005

Thanks QT -- I knew it was something like that! If I can make the code tags work, below you will see my xsl template as it currently stands. How I got this far with it, I'm not real sure anymore, but this version almost seems to work, except there is no data in the imported record. Grrr.... Anyway here we go: (Oh, and if the rest of this post ends up blank or otherwise mucked up, just ignore me and I'll go away! )


<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aws="http://webservices.amazon.com/AWSECommerceService/2004-11-10" exclude-result-prefixes="aws">

	<xsl:template match="/">

		<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

			<ERRORCODE>0</ERRORCODE>

			<PRODUCT BUILD="" NAME="" VERSION=""/>

			<DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="{count(/*/*)}" TIMEFORMAT="h:mm:ss a"/>

			<METADATA>

				<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Artist" TYPE="TEXT"/>

				<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Title" TYPE="TEXT"/>

				<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Asin" TYPE="TEXT"/>

				<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="ProductGroup" TYPE="TEXT"/>

			</METADATA>

			<RESULTSET>

			<xsl:attribute name="FOUND">1</xsl:attribute>

			<ROW>

					<xsl:attribute name="MODID">0</xsl:attribute>

					<xsl:attribute name="RECORDID"><xsl:value-of select="position()"/></xsl:attribute>

					<COL>

							<DATA>

								<xsl:value-of select="aws:ItemLookupResponse/aws:Items/aws:Item/aws:ItemAttributes/aws:Artist"/>

							</DATA>

						</COL>

						<COL>

							<DATA>

								<xsl:value-of select="aws:ItemLookupResponse/aws:Items/aws:Item/aws:ItemAttributes/aws:Title"/>

							</DATA>

						</COL>

						<COL>

							<DATA>

								<xsl:value-of select="aws:ItemLookupResponse/aws:Items/aws:Item/aws:ItemAttributes/aws:Asin"/>

							</DATA>

						</COL>

						<COL>

							<DATA>

								<xsl:value-of select="aws:ItemLookupResponse/aws:Items/aws:Item/aws:ItemAttributes/aws:ProductGroup"/>

							</DATA>

						</COL>

				</ROW>

			</RESULTSET>

		</FMPXMLRESULT>

	</xsl:template>

</xsl:stylesheet>

Fenton · June 3, 2005

I don't know what URL you're using for a request. But it is not the same as I used. I got a quite different return. I don't want to post my Amazon ID (though I doubt anyone cares); and in this case I just used an AppleScript to get the URL return, and I don't know if you're on a Mac (I didn't do the UPC search in my FileMaker file yet, just the Artist and Book ones).

Just to see if we're on the same page, in AppleScript the request looked kind of like (missing a couple variables, but you get the idea)

"http://xml.amazon.com/onca/xml3?locale=us&t=webservices-20&dev-t=" & myID & "&AsinSearch=" & theUPC & "&mode=music&type=lite&page=1&f=xml"

The XSL:

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" exclude-result-prefixes="xsi">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">

</METADATA>

<xsl:attribute name="FOUND">1</xsl:attribute>

<xsl:for-each select="/ProductInfo/Details">

<ROW>

<xsl:attribute name="MODID">0</xsl:attribute>

<xsl:attribute name="RECORDID">1</xsl:attribute>

<COL>

<DATA>

<xsl:value-of select="Asin"/>

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="ProductName"/>

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="Catalog"/>

</DATA>

</COL>

<COL>

<DATA>

<xsl:for-each select="Artists/Artist">

<xsl:value-of select="."/>

<xsl:if test="position() != last()">

<xsl:text>, </xsl:text>

</xsl:if>

</xsl:for-each>

</DATA>

</COL>

</ROW>

</xsl:for-each>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

</xsl:stylesheet>

UPC_lite_lite.zip

STP · June 3, 2005

Thanks Fenton -- I will try your version and see if I have any better luck. In the meantime, this is the URL I'm using for the request, which I formatted according to the instructions in the Amazon E-Commerce Service Developer Guide. If you take the "format" bit off the end, you can see that it does return the requested info. But then when you apply the template, it stops working.

And by the way -- I really appreciate your help!

http://xml-us.amznxslt.com/onca/xml?Service=AWSECommerceService&SubscriptionId= [put your id here] &Operation=ItemLookup&ItemId=632911000222&SearchIndex=Music&IdType=UPC&Style=http://www.code18.com/amazon/amazon_test2.xslt

STP · June 3, 2005

Holy smokes -- after a little trial-and-error with your files, it seems to actually be working! Thank you so much! This is my first foray into XML (if you couldn't tell...) Now after lunch I can dig deeper and see what else I can learn about this stuff. I really really really appreciate your help!

Fenton · June 3, 2005

I see you were using a slightly different URL command, a lookup instead of a search. So it makes sense that you'd get a different return. Without seeing exactly what you got however there's no way to know whether your xsl would work. As you've found, it doesn't take much of an error in your xsl to get no results whatsoever.

That's why I use some other tool, such as AppleScript, using do shell script "curl theURL", to see the raw result before even trying to import into FileMaker.

Also, I use TestXSLT, a free, easy-to-use (but occasionally quits) XML parser, to test out the transformation to FileMaker xml. It's here: http://www.entropy.ch/software/macosx/

direct download link: http://www.entropy.ch/software/macosx/#testxslt

Also there is a BBEdit xsl glossary, which is invaluable for actually writing the darn stuff. I don't use a dedicated xml/xsl program, because they all seem to be Java, which has such primitive file navigation that it drives me crazy (I'm used to Default Folder).

The free version of BBEdit, TextWrangler, does not support glossaries I don't think. But it does have syntax coloring.

AppleScript code. Copy/paste below into Script Editor. Alter the xml_file_path to where you want the resulting file to end up.

You can alternatively use the raw xml command at the bottom (currently commented out). But the xml returned has AppleScript quote escaping ", which you then have to remove. Easier to write it to a file.


set theUPC to "B000006U2T"

set myID to "put your Amazon ID here"

set the_URL to "http://xml.amazon.com/onca/xml3?locale=us&t=webservices-20&dev-t=" & myID & "&AsinSearch=" & theUPC & "&mode=music&type=lite&page=1&f=xml"

set the_URL to quoted form of the_URL



--To save as file, modify path

set xml_file_path to "Macintosh HD:Users:fej:Documents:FileMaker:My_FM:XML:amazon_ws:Music:XSL:UPC FM:UPC_lite:UPC_lite_raw.xml"

--this is the path to the UPC result

set output_path to quoted form of POSIX path of xml_file_path



set theXML to do shell script "curl " & the_URL & " -o " & output_path without altering line endings



--test, gets raw xml

--set theText to do shell script "cURL " & the_URL without altering line endings

STP · June 3, 2005

I will try out your tips -- many thanks again for your great advice!

Sign In

Amazon import

Recommended Posts

kisscass

Fenton

kisscass

Fenton

kisscass

Fenton

Fenton

STP

Fenton

STP

STP

STP

QuinTech

STP

Fenton

STP

STP

Fenton

STP

Create an account or sign in to comment

Create an account

Sign in

Browse

Site Support

Forums

Blogs

Marketplace

Activity

Important Information