Page 1 of 1
Read from a Spreadsheet?
Posted: Tue Mar 17, 2020 3:51 pm
by cmhjon
Hi all,
I am looking into possibly building a purchase order app that would get its vendor/product data from a centrally-stored spreadsheet. Can LC natively "read" a spreadsheet? My idea would be to have one worksheet (tab) for each vendor and then within each sheet, there would be columns for product data. I know that LC has a Data Grid feature but I was wanting a more central location to store/access the data as this app would be used my multiple users.
I tried searching for 'Spreadsheet' and 'Excel' in the forums but didn't find anything conclusive.
Best regards,
Jon
Re: Read from a Spreadsheet?
Posted: Tue Mar 17, 2020 4:39 pm
by dunbarx
Hi.
LC can read and write to external files. Generically:
Code: Select all
answer file "Select file to read" --your excel file here
put it into fileLoc
open file fileLoc for read
read from file fileLoc until EOF --or wherever you want to stop
close file fileLoc
The contents are placed into the local variable "it"
Craig
Re: Read from a Spreadsheet?
Posted: Tue Mar 17, 2020 5:17 pm
by cmhjon
Hi dunbarx,
Thank you for the reply. Although the script works, all I get is gibberish in the resulting text field.
Thanks,
Jon
Re: Read from a Spreadsheet?
Posted: Tue Mar 17, 2020 5:32 pm
by FourthWorld
Most office suite formats these days are Zip containers for XML and other files. You can use the revZip external to pull them out, and the revXML external to parse the XML data.
But that's a lot of work. Curry Kenworthy's SpreadLib can do the heavy lifting for you for a modest fee:
http://livecodeaddons.com/
Re: Read from a Spreadsheet?
Posted: Wed Mar 18, 2020 12:50 am
by mtalluto
This is a general response considering the information presented. Feel free to write back if you want to hone in on these or other ideas.
All spreadsheets can do I/O as a CSV. You could import the CSV into LiveCode and parse the data into a dataGrid or field with tab stops.
Or, you could import the CSV into a database and operate on the data with LiveCode. Databases can exist on a server in the office or in the cloud. You can choose what best fits your needs.
If the data must return to the spreadsheet, you could export the data as a CSV for the spreadsheet to consume.
Re: Read from a Spreadsheet?
Posted: Wed Mar 18, 2020 6:14 am
by bogs
FourthWorld wrote: ↑Tue Mar 17, 2020 5:32 pm
But that's a lot of work.
Well, it is a lot of work to figure out
maybe the first time you figure it out, but after that, it shouldn't be very much work, right?
Re: Read from a Spreadsheet?
Posted: Wed Mar 18, 2020 6:32 am
by FourthWorld
bogs wrote: ↑Wed Mar 18, 2020 6:14 am
FourthWorld wrote: ↑Tue Mar 17, 2020 5:32 pm
But that's a lot of work.
Well, it is a lot of work to figure out
maybe the first time you figure it out, but after that, it shouldn't be very much work, right?
That's true of most things, including wheels.
Re: Read from a Spreadsheet?
Posted: Wed Mar 18, 2020 7:00 am
by bogs
Absolutely, , so I hope the OP gives it a try, and possibly learns from it
