-
1. Re: Connecting to Excel
blafond Jun 10, 2009 8:02 AM (in response to luca_gioppo)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 -
2. Re: Connecting to Excel
luca_gioppo Jun 10, 2009 11:57 AM (in response to 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 -
3. Re: Connecting to Excel
rareddy Jun 10, 2009 6:17 PM (in response to luca_gioppo)Are you working on Linux or Windows? There seemed to be issues we faced before on Linux, we will verify.
-
4. Re: Connecting to Excel
luca_gioppo Jun 11, 2009 2:58 AM (in response to luca_gioppo)Windows XP (ODBC via dns should work only on windows if I'm not wrong).
-
5. Re: Connecting to Excel
rareddy Jun 11, 2009 12:20 PM (in response to luca_gioppo)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 -
6. Re: Connecting to Excel
rareddy Jun 11, 2009 1:43 PM (in response to luca_gioppo)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.
-
7. Re: Connecting to Excel
luca_gioppo Jun 12, 2009 3:11 AM (in response to 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 -
8. Re: Connecting to Excel
rareddy Jun 12, 2009 9:23 AM (in response to luca_gioppo)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. -
9. Re: Connecting to Excel
luca_gioppo Jun 15, 2009 3:13 AM (in response to luca_gioppo)I'm using Camtasia and yes I get the preview.