[Solved] Import data from an ODBC source

cesquivel · Post by **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...

edit this topic's initial post · Post by **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).

cesquivel · Post by **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?

edit this topic's initial post · Post by **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.

cesquivel · Post by **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.

edit this topic's initial post · Post by **Villeroy** » Mon Jan 14, 2008 12:08 pm

Desperately missing feature. I can offer a simple macro as a workaround.
http://user.services.openoffice.org/en/ ... =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

cesquivel · Post by **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.

[Solved] Import data from an ODBC source

[Solved] Import data from an ODBC source

Re: Import data from an ODBC source

Re: Import data from an ODBC source

Re: Import data from an ODBC source

Re: Import data from an ODBC source

Re: Import data from an ODBC source

Re: Import data from an ODBC source [SOLVED]