The Data menu in Calc has the entry Data Provider. It provides a dialog with a drop-down list Data Format. The type CSV works in principle (besides tdf#169019 or tdf#169036, for example). Has someone working examples for HTML, XML or SQL?
1 Like
previous attempt(s) → What is Data Provider option in LibreOffie- Calc and how to use that one? - #3 by sv.thiyagarajan
→ LibreOffice Calc crashes when clicking on "Data Provider"
It does not crash for me in a current daily build.
Hello!
In my version 25.2.6.2 Win 10, this command opens a dialog without crashing (even though the command is missing from the Data menu).
Offtop. Data Provider purpose is similar to implementing a subset of Microsoft Power Query. Why play catch-up when you can get way ahead with Python under the hood?
1 Like
As there are no working examples I have written bug reports: tdf#169077 and tdf#169079. BTW, meta bug for Data Provider problems is tdf#120219.
2 Likes
I’m sorry I cannot provide an answer for your question, but I wanted to express my interest in finding an HTML example as well. I’m having issues with Data Provider not displaying a preview of the data from an HTML source. I don’t believe this is user error, but I’ve yet to find examples to ensure I’m following the correct steps. At the moment, my personal solution to work with HTML table data is the OAuth2OOo extension. I hope this can help in some way.
See 169077 – Data Provider dialog does not work with format XML and HTML
Use menu:Sheet>External links… instead.
Use menu:Sheet>External links… instead.
This does work, and it does display the data I need, but I would like to change the look of my data (and remove some unnecessary parts) while still allowing the data to update. When the data added by the External Links option updates, the changes I make are basically undone as it pastes the updated data over my edited portion. This is why I was hoping to use Data Provider, but because it won’t display a preview or insert my data, I now use the OAuth2OOo extension. If there is a way to work around the issues I’m having, I will try it. I even considered somehow making a database with LibreOffice Base, but I haven’t tried that yet. I may make my own post in the near future if I’m still struggling and cannot find a solution. Thank you for your suggestion.
The awkward solution:
- Put the linked HTML on its own sheet and save the document.
- File>New>Database…
2.1. Connect to existing database of type “Spreadsheet”.
2.2. Specify the spreadsheet file you saved in 1.
2.3. Yes, register the database.
2.4. Save the database document. Nothing has been converted nor copied. All the data are still in your spreadsheet. The database document reads sheet contents from your spreadsheet as if they were database tables. - Open the data source window and drag the table icon of your HTML link to some cell on another sheet. This creates a linked database range “Import1”.
- Call Data>Define…, select “Import1”, check extra options “Insert/remove cells” and “Keep formatting”.
File>Save, then Data>Refresh refreshes this database link from the saved spreadsheet document. The reference to “Import1” adjusts automatically as the import data grow and shrink. Charts and formulas referencing this area adjust automatically. Your formatting is kept.
This should update everything:
oAreaLinks = ThisComponent.AreaLinks
oLink = oAreaLinks.getByIndex(0)
oLink.refresh()
ThisComponent.store()
ThisComponent.DatabaseRanges.getByName("Import1").refresh()
1 Like