Doub data type "can't get item 1"

Folks-

So I’m pulling data from an Excel spreadsheet like:
12589.46
14621.24
15090.45
16500.51
16100.21
13582.09
17441
19515.25
20720.1
24207.55
26126.27

via:


tell application "Microsoft Excel"
	repeat while incrCOL ≤ fundmaxNO --steps through columns (funds)
		set incrcell to 5
		repeat while incrcell ≤ 15 --steps through cells
			set vincrcell to (incrcell as string)
			set vwhichcell to ""
			set vwhichcell to "R" & incrcell & "C" & incrCOL --specifies cell
			set end of source to (get Value of Cell vwhichcell) -- adds cell value to end of list
			set incrcell to incrcell + 1
		end repeat --steps through cells
		my checkdata()
		set incrCOL to incrCOL + 1
	end repeat --steps through columns (funds)
end tell

this generates the following list, called source:


 {1.258946E+4, 1.462124E+4, 1.509045E+4, 1.650051E+4, 1.610021E+4, 1.358209E+4, 1.7441E+4, 1.951525E+4, 2.07201E+4, 2.420755E+4, 2.612627E+4}

however, when I attempt to get the data back out, via:


		set sourcetemp to (start of source)

or


set sourcetemp to (item 1 of source)

I get errors “can’t get item 1 of source”. Also, the class of item 1 of source appears to be “doub”, and I can’t seem to coerce it into a real, which I for no good reason hope will be more helpful.

stumped,

Ralph

Ralph:

Where do you have those statements? In another handler? This works fine:


set source to {1.258946E+4, 1.462124E+4, 1.509045E+4, 1.650051E+4, 1.610021E+4, 1.358209E+4, 1.7441E+4, 1.951525E+4, 2.07201E+4, 2.420755E+4, 2.612627E+4}
set sourcetemp to (item 1 of source)
sourcetemp's class
--> real

It may be that you need to declare source as global.

Thanks for the reply. I did declare source as global. And when I run your script snippet, I am able to get the value of sourcetemp. Here’s the whole script, which errors out at ‘get item 1 of’. The code is still ugly.


global source
global plotdata
global fundmaxNO
set fundmaxNO to 6
global nonyear --counts number of blank years
set nonyear to 0
global incrCOL --column counter
set incrCOL to 2
global vleftscale -- the marker variable for whether the left scale will be 25-50-60-or 70,000
set vleftscale to 25000
set source to {} --raw data from Excel spreadsheet
set plotdata to {} --this is the data, scaled
global startpt -- this is the y value for the fund starting at 10,000
set startpt to 76

(*pulls one column of data*)
--to pulldata()


tell application "Microsoft Excel"
	repeat while incrCOL ≤ fundmaxNO --steps through columns (funds)
		set incrcell to 5
		repeat while incrcell ≤ 15 --steps through cells
			set vincrcell to (incrcell as string)
			set vwhichcell to ""
			set vwhichcell to "R" & incrcell & "C" & incrCOL
			set vtemp to (get Value of Cell vwhichcell)
			set end of source to (get Value of Cell vwhichcell)
			set incrcell to incrcell + 1
		end repeat --steps through cells
		my checkdata()
		set incrCOL to incrCOL + 1
	end repeat --steps through columns (funds)
end tell

(*checks the data for range and length *)
to checkdata()
	
	tell application "Script Editor"
		repeat with anitem in source
			if anitem < 1 then
				set nonyear to nonyear + 1
			else if anitem > 60000 then
				set vleftscale to 70000
				set startpt to 27.14
			else if anitem > 50000 then
				set vleftscale to 60000
				set startpt to 31.66
			else if anitem > 25000 then
				set vleftscale to 50000
				set startpt to 38
			end if
		end repeat
		if nonyear > 0 then
			my trimdata()
		end if
		my scaledata()
		my makegraph()
		display dialog vleftscale & nonyear
	end tell
	
end checkdata
(*checks the data and converts it *)


(*trims blank dates from data*)
to trimdata()
	repeat (count of nonyear) times
		set source to end of source
	end repeat
end trimdata
(*trims blank dates from data*)


(*scales data according to which vleftscale is being used (25,50,60,70)*)
to scaledata()
	repeat (count of source) times
		set sourcetemp to item 1 of source -- ****this is where it errors"Can't get item 1 of 2.612627E+4."
		set tempvar to (190 * sourcetemp) / vleftscale
		set end of plotdata to tempvar
		set source to end of source
	end repeat
end scaledata
(*scales data according to which vleftscale is being used (25,50,60,70)*)


(*makes graph*)
to makegraph()
	tell application "Adobe Illustrator"
		make new document with properties {color space:CMYK}
		set spot646 to make new spot in document 1 with properties {name:"PANTONE 646 C", color:{cyan:65.0, magenta:30.0, yellow:0.0, black:11}, color type:spot color}
		tell document 1
			make new path item at layer "Layer 1" with properties {entire path:{{100, 100}, {250.03, 100}}, stroke width:1.0, name:"line1", filled:false, opacity:100.0, stroke cap:projecting, stroke color:{class:CMYK color info, cyan:0.0, magenta:0.0, yellow:0.0, black:100.0}, stroked:true}
			make new path item at layer "Layer 1" with properties {entire path:{{100, 118}, {250.03, 118}}, stroke width:0.5, name:"line1", filled:false, opacity:30.0, stroke cap:projecting, stroke color:{class:CMYK color info, cyan:0.0, magenta:0.0, yellow:0.0, black:100.0}, stroked:true}
			make new path item at layer "Layer 1" with properties {entire path:{{100, 136}, {250.03, 136}}, stroke width:0.5, name:"line2", filled:false, opacity:30.0, stroke cap:projecting, stroke color:{class:CMYK color info, cyan:0.0, magenta:0.0, yellow:0.0, black:100.0}, stroked:true}
			make new path item at layer "Layer 1" with properties {entire path:{{100, 154}, {250.03, 154}}, stroke width:0.5, name:"line3", filled:false, opacity:30.0, stroke cap:projecting, stroke color:{class:CMYK color info, cyan:0.0, magenta:0.0, yellow:0.0, black:100.0}, stroked:true}
			make new path item at layer "Layer 1" with properties {entire path:{{100, 172}, {250.03, 172}}, stroke width:0.5, name:"line4", filled:false, opacity:30.0, stroke cap:projecting, stroke color:{class:CMYK color info, cyan:0.0, magenta:0.0, yellow:0.0, black:100.0}, stroked:true}
			make new path item at layer "Layer 1" with properties {entire path:{{100, 190}, {250.03, 190}}, stroke width:0.5, name:"line5", filled:false, opacity:30.0, stroke cap:projecting, stroke color:{class:CMYK color info, cyan:0.0, magenta:0.0, yellow:0.0, black:100.0}, stroked:true}
			my plotline()
			make path item at layer "Layer 1" with properties {entire path:{{100, 100}, {115, 115}, {130, 130}, {145, 145}, {160, 160}, {175, 175}, {190, 190}, {205, 205}, {220, 220}, {235, 152}, {250, 152}}, filled:false, stroke width:2.0, name:"Path2", stroke color:{class:spot color info, tint:100.0, spot:spot "Pantone 646 C"}}
		end tell
	end tell
	
end makegraph

(*makes graph*)
to plotline()
	
	if nonyear = 0 then
		tell application "Adobe Illustrator"
			make path item at layer "Layer 1" with properties {entire path:{{100, startpt}, {115, (item 1 of plotdata)}, {130, (item 2 of plotdata)}, {145, (item 3 of plotdata)}, {160, (item 4 of plotdata)}, {175, (item 5 of plotdata)}, {190, (item 6 of plotdata)}, {205, (item 7 of plotdata)}, {220, item 8 of plotdata}, {235, (item 9 of plotdata)}, {250, (item 10 of plotdata)}}, filled:false, stroke width:2.0, name:"Path2", stroke color:{class:spot color info, tint:100.0, spot:spot "Pantone 646 C"}}
		end tell
	end if
	
end plotline
(*makes graph*)

-Ralph

ok… so I ask the list “source”


Display dialog (first item of source)
--returns 12589.46
set sourcetemp to (first item of source)
--returns error "can't get value of 1258946E+4
return class of (first item of source)
-- returns "doub"

is there some coercion i need to be performing?

Ralph

A workaround. Instead of loading all the values to a list, I’ll load them directly to variables, which seems to work:


tell application "Microsoft Excel"
	set vtemp to (get Value of Cell "R5C2")
	set newvalue to (190 * vtemp) / 10000
end tell

I still think the values coming out of Excel are of an odd format.

Ralph:

Your values from Excel should be fine. I believe your issue is within the checkdata() handler. For instance, you have a Script Editor tell block in there that makes no sense. The only reason to tell Script Editor to do anything is when you need to actually create a script on the fly and then run it. The other issue in that handler is your repeat with anitem in source looping section. Ostensibly, you have built it just fine, but when doing a referenced repeat like this, it is often necessary to explicitly use the values of the items in the list for you comparisons to function properly:

to checkdata()
	repeat with ais in source
		set anitem to (contents of ais) --Now, anitem is the value of the referenced item in the source list
		if anitem < 1 then
			set nonyear to nonyear + 1
		else if anitem > 60000 then
			set vleftscale to 70000
			set startpt to 27.14
		else if anitem > 50000 then
			set vleftscale to 60000
			set startpt to 31.66
		else if anitem > 25000 then
			set vleftscale to 50000
			set startpt to 38
		end if
	end repeat
	if nonyear > 0 then
		my trimdata()
	end if
	my scaledata()
	my makegraph()
	display dialog vleftscale & nonyear
end checkdata

Try that out and see if it helps. I don’t have Illustrator, so I cannot comment on that part of the script. Good luck,

OK- i took out the ‘tell Script Editor’ wrap.

I fixed my error. I think it was when I was decrementing the list I managed to chop it down to nothing, and then was unable to get ‘an item’ out of it. I was using

set thelist to end of the list

instead of

set list to rest of list