LiveCode Forums

Hi, I have a simple problem that's bugging me I'm trying to take an excel file onto my code and then place the values into separate array's. I've tried a bunch of different things and can't work it out any help would be appreciated. Here is my code if it helps.

on mouseup
// Get qualifying athletes' data
local arrayEntryID, arrayL0cation, arrayForename, arraySurname, arrayJumps, arrayAthleteStats

// Generate bib values and write to new file with entry IDs
local arrayWord1, arrayWord2, arryASCIIValue

// Find the highest number of jumping jacks completed
local maxJumps, MaximumIndex

// Display the full name of the athlete(s) who completed the highest number of jumping jacks

initialise
getDetails arrayEntryID, arrayL0cation, arrayForename, arraySurname, arrayJumps, arrayAthleteStats
GenerateBib arrayEntryID, arrayL0cation, arrayForename, arraySurname, arrayJumps, arrayWord1, arrayWord2, arryASCIIValue
FindHighestJump arrayJumps, arraymaxJumps, MaximumIndex
DisplayResults arraymaxJumps, arrayForename, arraySurname, arrayJumps
Test1
end mouseup

on initialise
local loop
// initialse parameters

repeat with loop = 0 to 30
put "" into arrayEntryID[loop]
put "" into arrayL0cation[loop]
put "" into arrayForename[loop]
put "" into arraySurname[loop]
put 0 into arrayJumps[loop]
put 0 into arraymaxJumps[loop]
put 6 into Test1
end repeat

end initialise

on getDetails @entryID, @l0cation, @forename, @surname, @jumps, @arrayAthleteStats
// enter athletes dta and store in appropriate variables

local useFile

local loop

answer file "please select the file with the athletes data."
if the result is not cancel then
put it into useFile
put url("file:" & useFile) into arrayAthleteStats[loop]
end if

repeat with loop = 0 to 30

put (field A of arrayAthleteStats[loop])into arrayEntryID[loop]

put (field B of arrayAthleteStats[loop]) into arrayL0cation[loop]

put (field C of arrayAthleteStats[loop])into arrayForename[loop]

put (field D of arrayAthleteStats[loop]) into arraySurname[loop]

put (field E of arrayAthleteStats[loop]) into arrayJumps[loop]

end repeat
end getDetails

Hi ReissHam03,

welcome to the forum!

Are ou really processing an EXCEL file?
Or rather CSV exported data from an EXCEL file?
I don't see any attempt to access the content of that file?


Best

Klaus

Hi, Klaus at the moment it's from an excel file in the code as you've mentioned there is most likely a mistake there but I can't figure out how to properly program it. Thanks in advance.

Not sure, but I doubt that an EXCEL file, which has probably, at least partly,
BINARY content, can be processed in this straight-forward way.

The scripts are correct so far, but this will only work in an IDEAL WORLD The array -> arrayAthleteStats[]
does NOT have fields or the like: -> put (field A of arrayAthleteStats[loop])...
This is NOT Livecode!

@ReissHam03

As Klaus already wrote Excel files (.xls / .xlsx) are binary data. You cannot read them using

put URL "file:....

The second problem here is that Livecode cannot access/process Excel files out of the box.

There is an open source Excel library available which might help here, but it requires Excel to be installed on the computer where the library is used.
http://aslugontheroad.com/ourproducts/1 ... r-livecode

Download: http://aslugontheroad.com/download/cate ... en-sources


The same author also created an xlsx library for Livecode which works without Excel installed. But that library is not open source. It is included in the commercial version of the Excel library.
http://aslugontheroad.com/29-excel-libr ... -prototype

That library works quite good although it is still a prototype.

Another commercial library to read Excel .xlsx files is Spreadlib
http://livecodeaddons.com/spreadlib.html

Hope this helps.

Matthias

Hi.

What everyone said.

In one of my projects I save data from a stack to an external ".txt" file, and that can later be retrieved intact by LC at any time. Is it possible to save your Excel file as a text file?

I am not an expert on file formats.

Craig

Has anything changed since the last reply to this topic?.

Do more recent versions of LC handle native support for Excel files?.

I am planning to create a button in my card, I need the system to ask me data input, and after this I need to send this to the last available row in an existing excel file.

Thanks

M.

Hi Marco,

Do more recent versions of LC handle native support for Excel files?

no.


Best

Klaus

Thanks for the feedback Klaus.

In your opinion, would working with open-office and similar applications offer any alternative compatibility-wise, while using LC?.

M.

Hi Marco,

no, the rpoblem is the bibary file format of these application.
We can import CSV TEXT files in LC, but something like WORD/EXCEL
files, need a special plugin like this one:
https://livecode.com/extensions/excel-library/1-3-3/

Best

Klaus

marco.deepak wrote: ↑

Wed Sep 08, 2021 10:38 am

Thanks for the feedback Klaus.

In your opinion, would working with open-office and similar applications offer any alternative compatibility-wise, while using LC?.

M.

Hi Marco,

in a previous post i mentioned a opensource library for Livecode which can be used to process Excel files

It requires Excel to be installed on the computer where the library is used.
http://aslugontheroad.com/ourproducts/1 ... r-livecode

Download: http://aslugontheroad.com/download/cate ... en-sources

The same author created a library which can be used without the need of Excel to be installed on the computer. But that library is part of a commercial product.
http://aslugontheroad.com/29-excel-libr ... -prototype

Another commercial library to just read Excel .xlsx files is Spreadlib
http://livecodeaddons.com/spreadlib.html

So at least there are ways to do it in LC.

Hi - Spreadlib natively reads and writes.
Excel is not needed.

You can download and use the free trial
at http://livecodeaddons.com/spreadlib.html

(Recently eSellerate/Digital River went downhill,
so I'm forwarding my store link to LC's Addon store,
but their store does not have Spreadlib.)

I'll get the other store back online pretty soon,
and update addons, after I get over Delta COVID!

Meanwhile if you liked the free trial
and need to purchase Spreadlib,
you can email me and I'll handle
the order manually within a few days.

Spreadlib natively reads and writes.
Excel is not needed.

Hi Curry,
please excuse. I did not know that anymore. It's a long time (many years) since i used it in a project.
Btw. the built standalone is still in use daily.

Regards,
Matthias

Thanks everyone for the prompt feedback!.

@Curry best wishes to you for a swift recovery!