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
|
>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.
|
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
|
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.
|
Main
webmaster@hxtt.com