Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
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
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT Excel
Accessing named ranges in Excel driver
Gordon Rose
2009-07-07 14:08:25.0
Hi,

I am currently doing a trial of Excel v2.2 3.0 driver. I have also downloaded the 4.0 driver to trial.

I see that I can easily access individual sheets. Is there a way to access a named range on a sheet and use that as the table?

Also, when I try to access an entire directory as opposed to an individual sheet, e.g. jdbc:excel:///c:/ vs. jdbc:excel:///c:/databasea.xls in SQuirreL, the CPU jumps to 100% (windows) and I do not see any sheets listed.

Thanks,

Gordon
Re:Accessing named ranges in Excel driver
HXTT Support
2009-07-07 17:10:36.0
>I see that I can easily access individual sheets.
>Is there a way to access a named range on a sheet and use that as the table?
You should try PIVOT and UNPIVOT syntax of SELECT sql.
pivot_clause: PIVOT ( aggregate_function(value_column) FOR pivot_column IN (column_list) ) [AS] tableAlias
unpivot_clause: UNPIVOT ( value_column FOR pivot_column IN (column_list) ) [AS] tableAlias

>Also, when I try to access an entire directory as opposed to an individual sheet,
> e.g. jdbc:excel:///c:/ vs. jdbc:excel:///c:/databasea.xls in SQuirreL, the CPU
>jumps to 100% (windows) and I do not see any sheets listed.
You need to wait all xls files, which be parsed once, since xls format will be unknow without parse it.
Re:Re:Accessing named ranges in Excel driver
Gordon Rose
2009-07-14 08:47:59.0
Hi,

Could you please provide an example of iusing the Pivot syntax to access a named range? It looked like the clause is used to retrieve summary data. I would look at adding named range support -- it is extremely useful.

In another area, I am testing the driver with a 3rd party product. It introspects each datasource. When I specify a directory for the database, what the product "sees" is a single workbook. The columns from all the sheet1 sheets in all the workbooks are listed in the sheet1 sheet of the single workbook returned from the introspection. Can you tell me what the JDBC driver returns when it is queried for metatdata information such as catalog and schema? Thanks,

Gordon
Re:Re:Re:Accessing named ranges in Excel driver
HXTT Support
2009-07-24 07:56:09.0
Sorry for missing your reply.

>Could you please provide an example of iusing the Pivot syntax to access a named
> range? It looked like the clause is used to retrieve summary data. I would look
>at adding named range support -- it is extremely useful.
Please send us a xls sample, and let us know what's your want formated data rows.

>It introspects each datasource.
>When I specify a directory for the database, what the product "sees" is a single
>workbook.
You can use a xls file path as database srouce too.

>The columns from all the sheet1 sheets in all the workbooks are listed in the
> sheet1 sheet of the single workbook returned from the introspection.
Maybe the 3rd party product is lame. You can try free SQuirreL SQL Client, or other tools.

>Can you tell me what the JDBC driver returns when it is queried for metatdata
>information such as catalog and schema? Thanks,
catalog/schema can be directory path or xls file path.


Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap