Connecting to Excel

[luca_gioppo]

I'm trying to connect to excel from TEIID 6.1 but I'm having troubles.
If I try to connect using SQuirreL SQL I manage to query the worksheet so there have to be something in the designer.
First hint the driver start unchecked, and this should be strange since the jdbcodbc driver is in the rt.jar and should be ready to use.
In any case I force it to use rt.jar and it pick up the driver getting rid of the mark and the error of no driver found.
Than when I try to connect "jdbc:odbc:alias" no luck it says (translated from italian the error message) "connection not opened" from the microsoft driver.
Win 2000 used.
The problem is that with the other tool all work so there must be something around here.
Sorry for the trouble, but any idea?
Luca

[blafond]

Luca,

Your note that adding the rt.jar got rid of the error mark for "no driver found" indicates you were in the JDBC Importer.When importing from JDBC in Designer, your select/point-to your driver jars on your local file system.

For connectors (preview & query execution), teiid-embedded requries the jars to be located relative to a workspace location, so when editing a Connector, the Class Path editor allows you to 1) Select (i.e. import) the same driver jar on your file system or 2) Select (i.e. "Advanced..." an existing jar currently used by one of your other connectors in your workspace.

See detailed instructions at:

https://www.jboss.org/community/wiki/SourceDriverInstallationExample

http://www.jboss.org/community/wiki/TeiidDesignerConnectorClasspathPropertyEditor .

Barry

[luca_gioppo]

Yes, that is the problem.
I tryed to import a new model from JDBC connection and choose MS Excel (since I wanted to read an excel file it was the most obvious choice).
Created a new source and choose the MS excel, but with my surprise there was a statement that said (just a warning I must admit) that there was no jars specified and the classpath would have been used.
Since I do not like warnings I told explicitly where rt.jar was (even if in my humble opinion it should be able to discover it on its own).
Done this tested the connection, but no success with the error from previous post.
So the problem is not in setting the driver, but in the driver usage by the designer.

Having hit that wall I tryed to use ODBC via DSN (this time I left the warning on its own).
tested the connection and ... magic ... it seems to work so I proceed with the import wizard until I arrive to the select database object.
There I find the file, but no way to select the sheet/table column list from that file (since there are some more files in there there is one that happens to list tables).
Tha main difference is that the sheet name does not contain _ or white spaces.
Could this be the problem?

So 2 walls in the same day ... ;-)
It seems that the Excel driver does not work, and the odbc via dns could work better but there could be a problem in the sheet naming.
Luca

[rareddy]

Are you working on Linux or Windows? There seemed to be issues we faced before on Linux, we will verify.

[luca_gioppo]

Windows XP (ODBC via dns should work only on windows if I'm not wrong).

[rareddy]

Ignoring the warnings about the jar file during the JDBC add source dialogue I used the Excel as source, and using the DSN less URL to 2003 Excel sheet, I could import a simple sheet. The URL looked like this

jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\\teiid\\Book.xls

with no userid and password.

When Excel Sheet was in Excel 95 format, it gave me errors during connection.

When I used DSN based route, I could make the connection, but the import did fail to recognise any tables to import from Excel sheet

[rareddy]

If you have driver (.so file), you could create a DSN even in the Linux. We did that for MetaMatrix product line before, so I know it can be done.

[luca_gioppo]

Thanks!!!!!!!!!!!!!!!!!!!
It work it was really TOO easy, I made the mistake of setting the wrong values in the connection URL.
This is really wanderful ... think will make another tutorial soon ;-)
Luca

[rareddy]

Great!

My example even created a connector which let me preview the data automatically. Did you have luck with that?

Looking forward for the tutorial. By the way what software do you use to create your videos, they look good and thanks for creating them.

We been thinking about re-writing the Excel connectors using the Apache POI, so that it will work on all the platforms seamlessly, but so far it did not make to roadmap as other competing issues deserve more attention.

[luca_gioppo]

I'm using Camtasia and yes I get the preview.