XLSQL for using Excel (XLS) as datasource in VC

[Former Member]

Hi Benjamin,

Hope you are having a good day. I know its been a while since you worked on this piece ([url] ;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:

Driver: com.nilostep.xlsql.jdbc.xlDriver

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.

Thanks,

BR

[Former Member · Accepted solution]

Thanks a ton Benjamin.

I will go over what you have written and make sure I perform those steps correctly - and report back in here.

BR

[Former Member · Accepted solution]

Hi Benjamin,

This is what I get when I complete the steps and run the TestJDBC.jsp

The see the dropdown from which you can select the table names, but instead of excel file name in that, I see SYSTEM_<table> entries - these are system tables such as SYSTEM_USERS etc.

If this normal, then I guess I am on the right track and I can hope that my system object created on the portal would probably help in pulling the Excel files as tables.

So my next step is to test the same in VC.

Update:

Tested in VC and system shows up.

I can even run a Search for tables.

BUT the Search returns no results.

I do have one XLS file in the directory I had mentioned in the Connection URL.

Should the excel file be anything other than a qa.xls (which is the name of the file)?

Thanks,

BR

[benjamin_houttuin · Accepted solution]

HI BR,

You are really on the right track...

> The see the dropdown from which you can select the table names, but instead of excel file name in that, I see SYSTEM_<table> entries - these are system tables such as SYSTEM_USERS etc.
> 

...This is correct what you see

>I do have one XLS file in the directory I had mentioned in the Connection URL.
>Should the excel file be anything other than a qa.xls (which is the name of the file)?
> 

The XLS file should have a specific format the filename doesn't matter very much but I would advice you not too use spaces and special characters. In the file you should use the first row to define the TABLECOLUMN headers.

I would advise you to create the following XLS file:

Colmn -> A | B | C |

Row 1 "A1" | "B1" | "C1" |

Row 2 "A2" | "B2" | "C2" |

Row 3 "A3" | "B3" | "C3" |

Then upload this file on the Unix FS and restart the JDBC connector via /nwa

After the restart of the jdbc connector check the default trace (also via /nwa) to see if the driver doesnt raises exepction while reading the XLS files

Good luck,

Benjamin Houttuin

[Former Member · Accepted solution]

Hi Benjamin,

The error logs show:

'Unable to recognize OLE stream'

I have two excel files - one created with Excel 2010 (qa.xls) and another with Excel 2007 (test.xlsx).

I am using the same XLSQL driver version that you used (7) .

Any ideas?

Thanks,

BR

[benjamin_houttuin · Accepted solution]

Hi BR,

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/)

Cheers,

Benjamin

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

[Former Member · Accepted solution]

Hi Benjamin,

That helped a lot.I am able to see the Tables now.

So for others who are wondering about this:

It HAS to be Excel 2003 format for the XLSQL7 to work - it is even better if you use Office 2003 to build that file - if not you can always try SAVE AS with Office 2007.

Thanks Benjamin and I hope this thread will help others who drive by.

Thanks,

BR

[benjamin_houttuin · Accepted solution]

it was a pleasure to help...

[benjamin_houttuin]

Hi,

The most important guideline we used was this document:

How Tou2026Configure a BI JDBC System for Visual Composer Version 1.03 u2013 March 2006

LINK: http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/6209b52e-0401-0010-6a9f-d40ec3a09...

Follow all the steps... Some remarks about the steps in this document:

Step 7: add all additional .jar files that are in the /lib folder of the xlsql driver package

Step 11 - 14: ... skip this, after step 15 restart the whole j2ee cluster, this is really needed to load the .jar files

Step 15: although optional and not the configuration you need to get it working for VC it is handy to configure this as it allows you to use the JDBC test page for debuging etc.

The only 2 parameters you need to configure with this step is:

Driver: com.nilostep.xlsql.jdbc.xlDriver

Connection URL: jdbc:nilostep:excel:/reports/spec/

...Now restart the j2ee cluster...

After the restart you can use the following url to see if the driver works: http://<hostname>:<port>/TestJDBC_Web/TestJDBCPage.jsp

Continue the configuration to allow VC to access the driver you need to configuer a System object in the portal...

Follow Step 16 an onwards...

Step 26: Again only fill in the following:

Driver Class name: com.nilostep.xlsql.jdbc.xlDriver

Connection URL: jdbc:nilostep:excel:/reports/spec/

Step 29,30 and 34,35: Usermaping Type can be left untouched

Additional step (do this after step 33): To make the system visible in VC you must set the portal permission for the user that use VC to Read + Enduser. Also when used in runtime later the enduser should have Read+ Enduser permission to use the system object. We used the built-in group Authenticated User for this and set the permissions on this group to Read+Enduser.

This should do the trick...

FAQ 1 - after uploading a new (or overwriting an existent) XLS file to the folder the changes are not visible, how to solve this?

ANSWER 1 - restart the JDBC connector via /nwa > manage > applications > (search for jdbc) > Stop / Start

FAQ 2 - after deleting a XLS file the data is still querieable, even after the restart of the jdbc driver?

ANSWER 2 - this is some kind of caching bug, deleted files are only deleted when you restart the J2EE engine.

FAQ 3 - after a restart of the jdbc driver, the first query firred in VC returns an error.

ANSWER 3 - this is because the first time the XLS files are read and writen into a HSQLDB in memory database, this takes more time. after this the calls are made on this in-memory-copy of the XLS data.

Hope this helps you...

Cheers,

Benjamin Houttuin

Edited by: Benjamin L.F. Houttuin on May 26, 2011 8:04 PM