<tankf33der>
i found new simple challenges to be able to implement on picolisp
aw- has quit [Ping timeout: 245 seconds]
aw- has joined #picolisp
avocadoist has quit [Ping timeout: 246 seconds]
avocadoist has joined #picolisp
razzy has joined #picolisp
<razzy>
Hello, anyone done integration of ms excel with pil DB? for example with ODBC driver or power querry interface?
<abu[7]>
I did imports of XLSX files, and exports of CSV
<abu[7]>
(in (list "xlsx2csv" "-dtab" "-aei" @) ...
<razzy>
abu[7]: excel have many interfaces that upon one click connect to a database (mostly SQL) ,download current data, display data in predefined way. It is very seamless way of presenting data to users. customer use it as main data output from database system.
<razzy>
abu[7]: I was wondering if somebody solved it for picolisp.
<abu[7]>
It will be you then
<razzy>
I do not know where to start yet.
<abu[7]>
Importing uploaded files is simple. I don't think you can easily connect from a server with a Pil application to a user's Excel on his Window machine
<abu[7]>
Why bother? The user clicks on an upload button and pil does the rest
<abu[7]>
on the server
<razzy>
abu[7]: current situation with pil, user needs to download file, open in excel, copy and verify multiple analysis, make decision.
<razzy>
with sql and ODBC, user only click on reload predefined querry from database, make decision.
<abu[7]>
It is easier: User clicks on the doc in the app, and Excel opens the document
<abu[7]>
I think there is no direct way, as Pil alwas is a web app
<abu[7]>
But perhaps you find some api that allows remote access? Sounds like a big security problem.
<abu[7]>
I don't think you ever need both directions
<razzy>
abu[7]: what if user makes his own excel magic and want to use his excel magic in future.
<abu[7]>
Either the user opens a report in the application, then displays it with a single click in Excel
<razzy>
abu[7]: api is only for reading data.
<abu[7]>
Or he imports Excel data into the DB
<abu[7]>
It makes no sense do do both in a single use case
<razzy>
reading data from DB to excel
<abu[7]>
Because operating on the DB is better done in the GUI
<abu[7]>
It must be synchronized with other users etv.
<abu[7]>
and needs interactive responses from the DB
<abu[7]>
"reading data from DB to excel" is just a single click
<abu[7]>
I have dozens of reports, and all have a button to open Excel with it
<abu[7]>
trivial
<razzy>
abu[7]: but your reports does not store user excel changes.
<abu[7]>
Data *import* from Excel must be programmed for each case exlicitly
<abu[7]>
yes, as I say, this makes no sense
<abu[7]>
User input must be validated
<abu[7]>
and synchronized
<abu[7]>
this is better done in the GUIT
<abu[7]>
GUI
<abu[7]>
It must be a well-designed *Import*
<abu[7]>
An Excel sheet is not valid DB data by itself
<abu[7]>
In my experience, a direct API would not be useful
<razzy>
abu[7]: i agree with you. but you are missing one use-case. user wants, download data from database, make his own graphs, and month later use same graphs without his or my work.
<abu[7]>
This is what I mean. No upload to the Pil DB, right? Using it later in Excel again.
<abu[7]>
"download" is not necessary
<abu[7]>
click on a link an Excel opens
<abu[7]>
You can make a graph, no problem
<abu[7]>
but a graph cannot be re-imported into the DB
<razzy>
graph is not reimported to DB. graph stays in "old" excel. with your aproach "new fresh" excel forgot user graph changes in next use. with "my" aproach "old" excel stays the same, only new data is imported.
<abu[7]>
So, yes, you can do. But user once saves this document to disk, then drags it to the "upload" button
<abu[7]>
This is not worth a complicated API
<abu[7]>
(if even possible, see above)
<abu[7]>
I think you can't have a web app access your local Excel
<razzy>
abu[7]: awesome, but I cannot imagine parsing excel file and changing data.
<abu[7]>
Parsing is trivial, I wrote the line above
<razzy>
abu[7]: it is not that trivial, do you know excel data cubes, and excel contingency table?