Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.0
  HXTT Cobol v4.0
  HXTT DBF v7.0
  HXTT Excel v6.0
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Paradox v7.0
  HXTT PDF v1.1
  HXTT Text(CSV) v7.0
  HXTT Word v1.0
  HXTT XML v3.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT Excel
Using HXTT Excel driver in ODI
Jody Fedor
2012-02-06 14:27:50.0
I'm looking for examples of how I can setup ODI to query the Excel sheet for what the worksheet names are or accessing worksheets by number rather than by name using the HXTT Excel driver. I've been seeing issues where the tab names have spaces or non-alpha numeric characters causing failure of reading the table (example: RPO & Family Codes <-- as a name of tab). Any help would be appreciated.
Re:Using HXTT Excel driver in ODI
HXTT Support
2012-02-06 16:06:00.0
select * from "RPO & Family Codes"
select * from [RPO & Family Codes]
select * from axls."RPO & Family Codes"
select * from "adirpath/axlsx.xlsx"."RPO & Family Codes"
Re:Using HXTT Excel driver in ODI
Jody Fedor
2012-02-07 10:07:00.0
Is there a way to address the sheet by number rather than by name? For example:
select * from table_reference[1]
select * from sheet2
select * from table_reference_list{2}

The issue is that the name of the tab might change from month to month, If I know that data is on tab 2, do I have to name it or can I query the sheet for available tables to determine which to use. I'm trying to automate this process to remove manual processing.

Thanks in advance for any solutions or examples.
Re:Re:Re:Using HXTT Excel driver in ODI
HXTT Support
2012-02-07 20:32:03.0
>select * from sheet2
It can be used only when your sheet2 is use "sheet2" an sheet name.

>do I have to name it or can I query the sheet for available tables to determine which to use.
It's easy. You can get all table name list from DatabaseMetaData.getTables function.

DatabaseMetaData dbmd = con.getMetaData();
// ResultSet rs = dbmd.getTables(null, null, "%", new String[] { "TABLE" });
// ResultSet rs = dbmd.getTables("49783.xlsx", null, "%", new String[] { "TABLE" });
ResultSet rs = dbmd.getTables(null, null, "tab%", new String[] { "TABLE" });



Re:Re:Re:Re:Using HXTT Excel driver in ODI
Andrew
2012-02-09 11:36:39.0
Is there any intention to support the SQL92 standard approach to provide this information directly by a SQL query?

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

http://troels.arvin.dk/db/rdbms/#cli-list_of_tables
Re:Re:Re:Re:Re:Using HXTT Excel driver in ODI
HXTT Support
2012-02-09 21:44:55.0
According to your suggestion, supported. The new package will be available after about 5 hours.
For instance, SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE' and TABLE_NAME='sheet1';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE' and TABLE_NAME='sheet1' and TABLE_CAT='testzzz.xlsx';
Re:Using HXTT Excel driver in ODI
Jody Fedor
2012-02-13 07:14:34.0
Is there a way to determine which table_name is which sheet number? From my testing, it seems there must be an order by on the return dataset. _rowid_ always comes back with Numeric/Alpha Ascending order. I was hoping to use _rowid_ for the sheet number. The behavior of _rowid_ is different when reading through the rows of an excel spreadsheet and will give the order of the rows as read from the sheet. Can the same behavior be achieved when querying the tab names?

Thanks,
Jody
Re:Re:Re:Re:Re:Re:Re:Using HXTT Excel driver in ODI
HXTT Support
2012-02-14 04:08:56.0
The latet version will add an "ORDINAL_POSITION" column for INFORMATION_SCHEMA.TABLES, and will be available after about 20 hours.
Re:Using HXTT Excel driver in ODI
Jody Fedor
2012-02-22 16:10:04.0
Looking for ORDINAL_POSITION in latest driver for Excel and the column is not available using:

select * from information_schema.tables where ordinal_position = 2;

Found only fields names:
TABLE_CAT
TABLE_SCHEM
TABLE_NAME
TABLE_TYPE
REMARKS
TYPE_CAT
TYPE_SCHEM
TYPE_NAME
SELF_REFERENCING_COL_NAME
REF_GENERATION

ORDINAL_POSITION is missing. Did we loose the build?

Jody
Re:Re:Re:Re:Re:Re:Re:Re:Using HXTT Excel driver in ODI
HXTT Support
2012-02-23 02:15:10.0

>select * from information_schema.tables where ordinal_position = 2;
>ORDINAL_POSITION is missing. Did we loose the build?
Supported now. Please download it.
Re:Using HXTT Excel driver in ODI
Jody Fedor
2012-02-23 10:47:34.0
Can't use the new driver, was compiled using v1.7, production environment only using 1.6.0_25. Can the driver be compiled in v1.6?
Re:Re:Re:Re:Re:Re:Re:Re:Using HXTT Excel driver in ODI
HXTT Support
2012-02-23 16:37:01.0
You can use Excel_JDBC40.jar ,which is released at the same version serial for JDK1.6.

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap