AS to pull information out of an Excel spreadsheet

Scripting Forums AppleScript | Mac OS X
,
#1

Hi all,

I am trying to use AS to pull information out of an Excel spreadsheet, format it into a “Form Data” string, and send it through Explorer to a CGI to retrieve data, based on what’s in the Excel spreadsheet.

Unfortunately, I seem to be having trouble getting the Excel data formatted in such a way as to get the CGI to recognize it. If I put the same data manually into the script, it works fine!! I’m confused!

Here’s a script -


tell application "Internet Explorer"
	
	Activate
	
        -- the theAccessionString variable contains the data from Excel

	set theAccessionString to theAccessionString as string	
	set sequenceToRetrieve to ""
	set sequenceToRetrieve to ("&id=" & theAccessionString) as string
	set theDataToUse to ("dbase=est_others&getfile=Retrieve" & sequenceToRetrieve) as string
	
	-- Opens the web site, inputs the POST form data
         -- BUT it doesn't work

	OpenURL "http://cgrb.orst.edu/cgi-bin/fastacmd.cgi" FormData ((theDataToUse) as string)
	delay 4
	

         -- this does work!!

	OpenURL "http://cgrb.orst.edu/cgi-bin/fastacmd.cgi" FormData "dbase=est_others&getfile=Retrieve&id=BG840719,BM334929,CD995686,CF647786,BM351010,BM335457,CF023829,CF023829,BM073562,BM953359,CF633455,CD434466,BG836753,CF648831,BE129820,CF638039,CF023734,CF023734,CF349273,BG840294,CF006802,BI674838,BI674761,BI595748,BG840265,CD438593,BU097655,BU097655,BU079671,BE575034,CD527673,BE049861,BE049861,BE049861,BM896227,CD994868,BI273425,BI595789,CD995866,CD995866,CF015814,BM501398,AW438344,CD967512,CF041192,CA399716,BQ486388,CF635552,CD573299,BF729340,BE638776,BE050015,AW424425,CF632832,CF649028,BM501138"

end tell

I have checked to make sure that the variables are what I think they are, before it send the info through Explorer.

The only thing I can think of is that perhaps the text in the variable is in an incorrect format, perhaps in it’s text? Rich text or something? When you send this through ScriptEditor, and record the Events, it shows that the FIRST event sent to Explorer has the FormData information in a different font (Geneva??) than the SECOND event (which seems to be in Courier??)

Please help!

thanks,
John Fowler
fowlerj@science.oregonstate.edu

#2

Perhaps “Unicode text”, and it does affect to IE?
Try this:

set theAccessionString to «class ktxt» of (theAccessionString as unicode text as record)
set sequenceToRetrieve to «class ktxt» of (sequenceToRetrieve as unicode text as record)

Or, better, do not use IE, but “curl”. Take a look here:
http://macscripter.net/faq/comments.php?id=190_0_5_0_C

#3

It’s also possible that there are high ASCII characters (or even spaces) in the string that need to be encoded. (See Apple’s Essential AppleScript Subroutines for more info.)

Jon

#4

thanks Jon,

the first lines of code worked!
I appreciate your help,
John