Excel

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
ReissHam03
Posts: 2
Joined: Wed Mar 10, 2021 10:42 am

Excel

Post by ReissHam03 » Wed Mar 10, 2021 10:48 am

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

Klaus
Posts: 12459
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Excel

Post by Klaus » Wed Mar 10, 2021 11:51 am

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

ReissHam03
Posts: 2
Joined: Wed Mar 10, 2021 10:42 am

Re: Excel

Post by ReissHam03 » Wed Mar 10, 2021 11:57 am

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.

Klaus
Posts: 12459
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Excel

Post by Klaus » Wed Mar 10, 2021 12:18 pm

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 8)

Code: Select all

...
  put url("file:" & useFile) into arrayAthleteStats[loop]
end if 
repeat with loop = 0 to 30
  put (field A of arrayAthleteStats[loop])into arrayEntryID[loop]
...
The array -> arrayAthleteStats[]
does NOT have fields or the like: -> put (field A of arrayAthleteStats[loop])...
This is NOT Livecode!

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 140
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: Excel

Post by matthiasr » Wed Mar 10, 2021 1:51 pm

@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

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7809
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: Excel

Post by dunbarx » Wed Mar 10, 2021 3:17 pm

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

marco.deepak
Posts: 11
Joined: Fri Oct 02, 2020 10:24 am

Re: Excel

Post by marco.deepak » Wed Sep 08, 2021 10:27 am

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.

Klaus
Posts: 12459
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Excel

Post by Klaus » Wed Sep 08, 2021 10:31 am

Hi Marco,
Do more recent versions of LC handle native support for Excel files?
no.


Best

Klaus

marco.deepak
Posts: 11
Joined: Fri Oct 02, 2020 10:24 am

Re: Excel

Post by marco.deepak » 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.

Klaus
Posts: 12459
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Excel

Post by Klaus » Wed Sep 08, 2021 11:09 am

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

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 140
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: Excel

Post by matthiasr » Wed Sep 08, 2021 9:14 pm

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.

Curry
Posts: 111
Joined: Mon Oct 15, 2007 11:34 pm
Location: USA
Contact:

Re: Excel

Post by Curry » Wed Sep 08, 2021 10:38 pm

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.
Best wishes,

Curry Kenworthy

LiveCode Development, Training & Consulting
http://livecodeconsulting.com/

WordLib: Conquer MS Word & OpenOffice
SpreadLib: "Excel-lent" spreadsheet import/export
http://livecodeaddons.com/

matthiasr
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 140
Joined: Sat Apr 08, 2006 7:55 am
Location: Lübbecke, Germany
Contact:

Re: Excel

Post by matthiasr » Wed Sep 08, 2021 10:55 pm

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

marco.deepak
Posts: 11
Joined: Fri Oct 02, 2020 10:24 am

Re: Excel

Post by marco.deepak » Thu Sep 09, 2021 2:06 pm

Thanks everyone for the prompt feedback!.

@Curry best wishes to you for a swift recovery!

Post Reply

Return to “Talking LiveCode”