[Solved] Import data from an ODBC source

Discuss the spreadsheet application

[Solved] Import data from an ODBC source

Postby cesquivel » Thu Jan 10, 2008 1:44 am

]Is there a way to import data into a Calc spreadsheet, from an ODBC source?

I'm a newbie in OpenOffice and I'm looking for a funcionallity like the MS Excel has, in such a way that a range in the spreadsheet is filled with a SQL query on an ODBC source, the query parametriced with the values in one or more cells, and each time one of these cells changes, the range is updated.

Thanks you very much...
Last edited by cesquivel on Tue Jan 22, 2008 2:26 am, edited 2 times in total.
cesquivel
 
Posts: 4
Joined: Thu Jan 10, 2008 1:23 am
Location: Buenos Aires - Argentina

Re: Import data from an ODBC source

Postby Villeroy » Fri Jan 11, 2008 12:58 pm

Create a database, linked to your data via odbc:
File>New>Database... [X]Connect existing... [X]Register database.
The resulting Base document shows the tables. You may want to add queries, forms and reports.
The registered database appears in various data source related tools and in datasources (F4).
Drag a table or query into Calc. You get a refreshable import range (see Data>Define... and try Data>Refresh).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28846
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import data from an ODBC source

Postby cesquivel » Sun Jan 13, 2008 4:56 pm

That works fine, but when I save and close the spreadsheet, and I open again and try to refresh the range, a message appears saying that a XQueriesSupplier is not available. What is wrong?
cesquivel
 
Posts: 4
Joined: Thu Jan 10, 2008 1:23 am
Location: Buenos Aires - Argentina

Re: Import data from an ODBC source

Postby Villeroy » Sun Jan 13, 2008 5:14 pm

I guess, you dragged the table view from the database into the sheet. If you have registered your database, it should be visible in te datasource window (hit F4). If you drag it from there, it will be linked by it's registered name, rather than file-url.
If my guess was right, this is the corresponding issue I have filed recently: http://www.openoffice.org/issues/show_bug.cgi?id=85110
I mean it should be possible the way you did.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28846
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import data from an ODBC source

Postby cesquivel » Mon Jan 14, 2008 1:08 am

yes, that is exactly wath I did!: I dragged the query from the database. Dragging it from the datasource window solves the problem.

One last question...
I put parameters into the query in the form: " ... where name = ? and id = ? ". Their vaues are asked by calc when I refresh the range. Is there any way to link these parameters to cells in the spreadsheet?

Thanks.
cesquivel
 
Posts: 4
Joined: Thu Jan 10, 2008 1:23 am
Location: Buenos Aires - Argentina

Re: Import data from an ODBC source

Postby Villeroy » Mon Jan 14, 2008 12:08 pm

Desperately missing feature. I can offer a simple macro as a workaround.
viewtopic.php?f=21&t=1645
It needs some adjustment according to your needs:
- which import range?
- which source?
- which query?
The latter question relates to the slighly advanced part, because the import descriptor requires a whole SQL-string concatenated in a cell "SELECT ... FROM ... WHERE paramX=" & $A$1
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28846
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import data from an ODBC source [SOLVED]

Postby cesquivel » Tue Jan 22, 2008 2:20 am

Thanks you a lot!
I managed to use this macro to solve my problem, however I think it could be better to include this funcionality as a "built-in" one in Calc.
cesquivel
 
Posts: 4
Joined: Thu Jan 10, 2008 1:23 am
Location: Buenos Aires - Argentina


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests