Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Important Excel files with (.xls) extention

More
12 May 2016 18:11 #1 by Khadija Elyo
Excel files with (.xls) extention was created by Khadija Elyo
Hello,
I'm trying to read data from a source file with (.xls) extention.
The problem is that i can't detect the correct catalog name while creating my Metadata and i have the error "Children List Error".
How can i read data from a (.xls) file?
NB: i'm using the driver : "com.stambia.jdbc.driver.excel.XLSXDriver"
Thank you.
More
12 May 2016 18:14 #2 by Thomas BLETON
Replied by Thomas BLETON on topic Excel files with (.xls) extention
Hello,
The excel driver can handle "xlsx" files, not "xls".
See here for more information: www.stambia.org/kb/21-designer-issues/10...ng-an-excel-document
More
12 May 2016 18:16 #3 by Thomas BLETON
Replied by Thomas BLETON on topic Excel files with (.xls) extention
If you are contrained to "xls" format, you can also setup an ODBC datasource.
More
12 May 2016 18:23 #4 by Khadija Elyo
Replied by Khadija Elyo on topic Excel files with (.xls) extention
Yes in fact I'am constrained to xls format.I didn't really get the solution you suggested in last post. Could you please clarify more ? :) Thank you so much
More
13 May 2016 11:40 #5 by Thomas BLETON
Replied by Thomas BLETON on topic Excel files with (.xls) extention
Here is more information about using ODBC datasources.

First you need to get familiar with ODBC and setup an ODBC datasource for your xls file. On Windows systems this can be done with the ODBC control panel. You will surely find help on the internet for this step.

Then, from Stambia, create an Excel metadata and choose the "JDBC-ODBC Bridge" driver. Simply provide your ODBC DSN in the jdbc url, for example "jdbc:odbc:mydatasource". This should work fine if you are using Java 7. Let me know if you are using Java 8 ore later: there is some extra configuration to do, because Oracle removed "JDBC-ODBC Bridge" in these versions.
More
13 May 2016 15:47 #6 by Khadija Elyo
Replied by Khadija Elyo on topic Excel files with (.xls) extention
It doesn't work for me. I can't connect my Metadata.
In fact i'm using java 8 Update 91.
Could you please tell me the configuration i have to do?
thank you
More
13 May 2016 16:41 - 13 May 2016 16:47 #7 by Thomas BLETON
Replied by Thomas BLETON on topic Excel files with (.xls) extention
With Java 8+, the idea is to extract the required libraries from a Java 7 archive. And then install them into your Stambia environment.
Afterwards, Stambia will be able to use them.
We are preparing an article which will detail how to do this. it is not ready yet, but here is some information so that you can work:

About installing the missing librairies:
- Get these two files from a Java 7 distribution: "jre/lib/rt.jar", "jre/bin/JdbcOdbc.dll" for Windows, "jre/bin/JdbcOdbc.so" for Linux
- Installing the Java 7 libraries in the Runtime
  - Copy "rt.jar" and "JdbcOdbc.dll" (or .so) into the stambiaRuntime/lib/jdbc directory
  - Restart the Runtime
- Installing the Java 7 libraries in the Designer
  - Append the following lines to your stambia.ini (or stambia32.ini) file:
       -Djdbc.odbc.wrapped.lib.path=stambiaRuntime/lib/jdbc/JdbcOdbc.dll
       -Djdbc.odbc.wrapped.jar.path=stambiaRuntime/lib/jdbc/rt.jar
  - In the Designer, open Window/Preferences/SQL Editor/JDBC Drivers
  - If the "JDBC ODBC Bridge Wrapper" entry does not exist then create if with the "Add" button:
     Name: JDBC ODBC Bridge Wrapper
     Example URL: jdbc:odbc:<alias>
  - Edit the "JDBC ODBC Bridge Wrapper" entry:
     In the "Extra class path" tab, Add Jar "stambiaRuntime\lib\jdbc\stambia.jdbc.driver.odbc.jar" and click "List drivers"
     The Driver class name should be automatically set to "com.stambia.jdbc.odbc.JdbcOdbcDriverWrapper"

About creating a metadata in the Designer:
- Create an excel metadata as usual
- Make sure you select the "JDBC-ODBC Bridge Wrapper" driver
- set the jdbc url to jdbc:odbc:<your-odbc-alias>

We are insterested in your feedback, please keep us informed :)
Last edit: 13 May 2016 16:47 by Thomas BLETON.
More
13 May 2016 19:29 #8 by Khadija Elyo
Replied by Khadija Elyo on topic Excel files with (.xls) extention
I've done all steps but the metadata is still disconnected :S
I'm using jars in the capture
Attachments:
More
16 May 2016 11:31 #9 by Khadija Elyo
Replied by Khadija Elyo on topic Excel files with (.xls) extention
Nice, It's working :lol:
thanks for your help
More
17 May 2016 09:26 #10 by Thomas BLETON
Replied by Thomas BLETON on topic Excel files with (.xls) extention
OK, perfect :)