Excel translator and dynamic filenames

[rmarblack]

Hi all,

All samples I've reached on how to setup a connection to xls files, set up file names as properties in the source model:

...
<property name="importer.ExcelFileName" value="names.xls"/>
...

Is there any way to specify filenames dynamically at run-time?

Best regards

MB!

[rareddy]

MB,

There are two phases in working with resource in translator life cycle when working with Dynamic VDBs. One the metadata collection phase, then query execution phase. The property you show above is for metadata collection, i.e. given the file, the translator is going to read the structure of the file and generate relational table structure from it. Once that is done the VDB will be in ACTIVE state, ready to accept the queries against it.

When executing queries, the file name can be defined in two different places. One is in the data source definition, i.e. in resource-adapter configuration for the "file" configuration, where you define the parent directory where XLS files can be found. Then when querying you will use "getTextFiles" procedure, in which you define the "file" parameter, that is where you need to define your dynamic file name.

Ramesh..

[rmarblack]

Thanks for your help Ramesh,

Still can't figure out how to use the getTextFiles stored procedure here. If I'm not wrong this SP comes from the File Data Source, and allows to read files contents as CLOB (one of the output parameters). There are some samples about how to manage these contents as a resulset via TEXTTABLE function - https://developer.jboss.org/wiki/TextToTableWithTeiid (excellent article by the way )

Is there any equivalent function to manage Excel file contents?

A small piece of code would be appreciated

Best regards

MB!

[rareddy]

MB,

Have you seen this article Microsoft Excel Document into Relational Table ?

Note both designer and dynamic approaches are described there.

Ramesh..

[rmarblack]

Hi Ramesh,

Yes, that was the first article I read to know how to manage Excel files. It's a really good article but it doesn't show how to manage different files dynamically.

In fact, I've been trying to figure out how to link what you described in the aforementioned article and the "getTextFiles" SP. I've spent some time checking out some articles, examples and messages in this forum and other forums in the Internet, but no luck.

Thanks for your time and help.

Best Regards

MB!

P.S.: I'm able to use an Excel file as a data source (I'm following the dynamic approach), I'm just unable to choose which file I want to access to on demand.

[rareddy]

MB,

I understand your usecase now, unfortunately there is no support for dynamic file names currently, but you can add a enhancement request for this, and we will see what we can do.

Ramesh..

[rmarblack]

Thanks for the info Ramesh.

I'll open an enhancement request as you suggest.

Best regards

MB!