I have a calc file and I want a way to connect it to a data source, it can be another .ods file, a database or a .csv file. I have been experimenting with =DDE but I do not want to actually import the data in my document, only access it and perform a really trivial lookup using one of the cell.
I’d think you can’t have both. I think a VLOOKUP() will need its own copy of the data in the sheet.
.
If you wish to avoid a copy of the data, use a database and use a query instead of a table as datasource. It depends then on your actual needs, if you can use a fixed query (newest entry / highest value) or need to tell the query some parameter.
A true database is a “lookup machine in itself”. Stop using spreadsheets.
----> No. <-----
Can the query also be select * from or need to filter the data? I need to pull all the pricing list from the data source to be used as lookup for what the customer enter in the article id, so to pull the description and unit price.
IMHO you may use everything the connected database supports. So you are limited for dBase or csv and Calc.
.
But I have a heavily joined query, stored as a view wich I query with parameters from a “power filter” table and the output is dragged to a Calc-template (automacically refresh by macro).
So it is not possible to lookup data without importing the data in the document? Im creating a model, and wanted to split the data source from the data consumer, this way I can update the data source and have it reflected in all the documents derived from the model (data consumer) is it possible?
Let use an example like temperatures for every day. I can use a query like select MAX(temp) from data group by month and get a copy of 12 rows in my spreadsheet in a single step. With an additional where month=1 you could reduce this to get only a single value, as a vlookup would do, but this will have a penalty in efficiency.
.
But the spreadsheet will usually store the last value retrieved,
This works with a true database, and you can link the resulting row set to a database range. With a spreadsheet as data source, you don’t have aggregation functions at hand other than COUNT(*). A query like select MAX(temp) from data group by month in a spreadsheet can be accomplished by means of a pivot table. A pivot table can be derived from an data source.
[Spreadsheet Source] → [Base document] → Pivot_Table in target document
Using macros, you can perform any action: connect to database and execute SQL queries, call the REST API, etc. Moreover, we have Python under the hood.
A question of labor costs, time, efficiency…
… this works for me:
=VLOOKUP(1;'file:///C:/Users/Martin/Desktop/source.ods'#$datasheet.$A$1:$C$3;2;0)
Can I put this in a model and use %path% to target the recipient desktop for documents folder for example?
It is working, however when you update the datalayer, the dataconsumer sheet does not update in real time, I think there is a way to enforce this but becomes unpractical. I guess I’ll have to ship the data along with the model in a separate sheet, but will be complicated to explain the difference between opening as model or deriving a new document from a model to the customer.
It may help a bit if you give a bit more context about your final target…
Colonize Mars. But in the immediate I just want a simple way to lookup data from an external source (possibly .csv or .ods) wihtout adding such data to the active sheet. I cannot give the customer something with macro, he cannot maintain this kind of things, and in case there is a little problem will call me.
Again:
Put in on any (hidden) sheet you want.
Set option “Do not save imported data”. This way, the user will be prompted (without macro) to refresh the linked data on document open.
Database forms are way more user friendly and maintainance free.
1 Like