XLSQL for using Excel (XLS) as datasource in VC
Hope you are having a good day. I know its been a while since you worked on this piece ([url] DATE issues when using BI JDBC Excel datasource in VC;url] ) but we are trying to do the same thing you had done. Use xlSQL to connect to an excel file as a datasource for VC modeling.
We want to use the BI Java connector. I have loaded the JAR files and when we get to the Manage Connections, not sure what we enter for the Driver, URL, Schema, username and Password.
What I now see if that the Connection Test (for the BI JDBC system) works fine with the following:
Connection URL (Unix box): jdbc:nilostep:excel:/reports/spec (this is the folder where I have the xls file)
FixedSchema: (should my excel file name go in here?)
FixedCatalog: (should my excel sheet name go in here?)
Username: none used
Password: none used
When I do all of this and search for TABLES in VC on that system, it says None found. Any light shed on this would be very helpful.
Benjamin Houttuin replied
I myself (@home on my test system) used Excel 2007 and saved the files with the "Save As 97 - 2003" function.
The customer still uses Excel 97.
I've not yet seen that error you have yet.
I guess the driver is only compatible with the XLS 97 - 2003 format as the driver dates from 2004.
We used the XLSQL Y7 stable version that is available on Sourceforge.org (http://sourceforge.net/projects/xlsql/files/)
Update on this post, I did some test on my home system for you and these are the results:
- Excel 2007 --> Save = <filename>.XLSX --> not even read by driver
- Excel 2007 --> Save As 97 -2003 = <filename>.XLS --> read by driver and loaded OK
- Excel 2007 --> Save = <filename>.XLSX but manual rename to <filename>.XLS -->read by driver but trows exception:
May 31, 2011 7:44:52 PM com.nilostep.xlsql.database.excel.io.jxlReader readWorkbooks INFO: /tmp/excel/map3.xls-java excel api reports: 'Unable to recognize OLE stream' ..?
Hope this helps a bit
Edited by: Benjamin L.F. Houttuin on May 31, 2011 7:49 PM