Excel
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 2
- Joined: Wed Mar 10, 2021 10:42 am
Excel
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
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
Re: Excel
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
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
-
- Posts: 2
- Joined: Wed Mar 10, 2021 10:42 am
Re: Excel
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.
Re: Excel
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!
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
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]
...
does NOT have fields or the like: -> put (field A of arrayAthleteStats[loop])...
This is NOT Livecode!
-
- VIP Livecode Opensource Backer
- Posts: 190
- Joined: Sat Apr 08, 2006 7:55 am
- Location: Lübbecke, Germany
- Contact:
Re: Excel
@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
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
-
- VIP Livecode Opensource Backer
- Posts: 9582
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: Excel
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
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
-
- Posts: 11
- Joined: Fri Oct 02, 2020 10:24 am
Re: Excel
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.
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.
Re: Excel
Hi Marco,
Best
Klaus
no.Do more recent versions of LC handle native support for Excel files?
Best
Klaus
-
- Posts: 11
- Joined: Fri Oct 02, 2020 10:24 am
Re: Excel
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.
In your opinion, would working with open-office and similar applications offer any alternative compatibility-wise, while using LC?.
M.
Re: Excel
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
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
-
- VIP Livecode Opensource Backer
- Posts: 190
- Joined: Sat Apr 08, 2006 7:55 am
- Location: Lübbecke, Germany
- Contact:
Re: Excel
Hi Marco,marco.deepak wrote: ↑Wed Sep 08, 2021 10:38 amThanks for the feedback Klaus.
In your opinion, would working with open-office and similar applications offer any alternative compatibility-wise, while using LC?.
M.
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.
Re: Excel
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.
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/
Curry Kenworthy
LiveCode Development, Training & Consulting
http://livecodeconsulting.com/
WordLib: Conquer MS Word & OpenOffice
SpreadLib: "Excel-lent" spreadsheet import/export
http://livecodeaddons.com/
-
- VIP Livecode Opensource Backer
- Posts: 190
- Joined: Sat Apr 08, 2006 7:55 am
- Location: Lübbecke, Germany
- Contact:
Re: Excel
Hi Curry,Spreadlib natively reads and writes.
Excel is not needed.
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
-
- Posts: 11
- Joined: Fri Oct 02, 2020 10:24 am
Re: Excel
Thanks everyone for the prompt feedback!.
@Curry best wishes to you for a swift recovery!
@Curry best wishes to you for a swift recovery!