Import Excel Data into Property List

[ctp webforumsuser@macromedia.com]

I'd like to be able to import an excel table into director and make a property list out of it. I'd like to be able to access the data like a database table. I assume that saving the excel file as a comma or tab delimited file is the best way to start, but I don't know how to get the info out of the text file and into a list in director. I tried importing the text file as a cast member and then using the value() function to turn it into a list, but that didn't work.

The tab delimited text file has about 150 records and looks like:
Prod001 29 80.01 33.21% Yes Low cost
Prod002 33 79.99 29.01% No High cost
....

the script I used that didn't work looks like:
on startMovie
list=value(member("textfile").text)
if listP(list) then
gData = list
else
alert "Not a List"
end if
end

I tried variations on the syntax but I always get the error "Not a List"

Any Help would be greatly Appreciated.

Thanks, Colin

[Agustín María Rodríguez]

"ctp" [email]webforumsuser@macromedia.com[/email] wrote:

> I'd like to be able to import an excel table into director and make a property list out of it.

Hi, Colin. If you don´t need this to be done on the fly, then you can
select the Excel table, copy it, paste it in Word as a table, select all
the table, convert it to text separated by items, select all, copy /
paste into Director and the result is a text member delimited by some item.

HTH,
--
Agustín María Rodríguez | [email]agustin@OnWine.com.ar[/email] | [url]www.OnWine.com.ar[/url]

[ctp webforumsuser@macromedia.com]

I can get the excel file into a tab or comma delimited file OK, but I need to get that file into a list in Lingo so I can acess each cell of information separately. ie the tab delimited text file looks like:

Prod001 29 80.01 33.21% Yes Low cost
Prod002 33 79.99 29.01% No High cost
....

I need to turn this into a list that looks like:
list=[["Prod001","29","80.01","33.21%","Yes","Low cost"],["Prod002","33","79.99","29.01%","No","High cost"],...]

so I can access the info pieces:
newtext=string(list[1][1]) && string(list[1][6]) && "vs." string(list[2][1]) && string(list[2][6])
put newtext -- "Prod001 Low cost vs. Prod002 High cost"

Or if anybody knows a better way to do this without using an expensive xtra, I'd appreciate the advice.

[Gretchen webforumsuser@macromedia.com]

I can get the excel file into a tab or comma delimited file OK, but I need to get that file into a list in Lingo so I can acess each cell of information separately. ie the tab delimited text file looks like:

Prod001 29 80.01 33.21% Yes Low cost
Prod002 33 79.99 29.01% No High cost
....

I need to turn this into a list that looks like:
list=[["Prod001","29","80.01","33.21%","Yes","Low cost"],["Prod002","33","79.99","29.01%","No","High cost"],...]

so I can access the info pieces:

on turnIntoList thefilepath
-- read the file
inst = new(xtra "fileio")
inst.openFile(thefilepath,1)
stringErr = inst.error(inst.status())
if stringErr <> "OK" then
alert stringErr
return ""
end if
txt = inst.readFile()
if stringErr <> "OK" then
alert stringErr
return ""
end if
inst.closeFile()
-- parse the text
lins = the number of lines of txt
the itemDelimiter = TAB
theList = []
repeat with x = 1 to lins
subList = []
oneLine = txt.line[x]
if x = lins and (oneLine = "") then exit repeat
items = the number of items of oneLine
repeat with y = 1 to items
append subList,oneLine.item[y]
end repeat
append theList,duplicate(subList)
end repeat
return theList

Put in movie type of script and call like so:

theList = turnIntoList("C:\folder\tabbedData.txt")


put theList
-- [["Prod001", "29", "80.01", "33.21%", "Yes", "Low cost"], ["Prod002", "33", "79.99", "29.01%", "No", "High cost"]]


Gretchen Macdowall
updateStage, inc.

[ctp webforumsuser@macromedia.com]

Thank you Gretchen, that's exactly what I was looking for.

There is one (very minor) strange thing that seems to be happening with this script: it puts the RETURN from the end of each line on the text file into the first item of the next line. So if the text file looks like:
Prod001 12 34 56
Prod002 78 90 12
Prod003 34 56 78
....
and I want to output :
"Prod001 vs. Prod002" (on one line) with:
string=theList[1][1] && "vs." && theList[2][1]
it actually outputs:
"Prod001 vs.
Prod002" (two lines)

Like I said, very minor detail, and should be easy enough to fix

[Gretchen webforumsuser@macromedia.com]

Change this:

if x = lins and (oneLine = "") then exit repeat

to this:

if oneLine = "" then next repeat


Gretchen Macdowall
updateStage, inc.

[Gretchen webforumsuser@macromedia.com]

Actually the last fix won't fix it, although it will prevent blank lines in the data from being put on the list. I think the prob is that the file was created on Win and has cr,lf line endings. Lingo likes Mac (cr only) line endings. Here's a fix:

repeat with y = 1 to items
oneItem = oneLine.item[y] -- new
if charToNum(oneItem.char[1]) = 10 then oneItem = oneItem.char[2..length(oneItem)] -- new
append subList,oneItem -- new


Gretchen Macdowall
updateStage, inc.