-
1. Re: Excel translator and dynamic filenames
rareddy Jul 20, 2015 9:01 AM (in response to rmarblack)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..
-
2. Re: Excel translator and dynamic filenames
rmarblack Jul 26, 2015 6:21 AM (in response to rareddy)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!
-
3. Re: Excel translator and dynamic filenames
rareddy Jul 26, 2015 1:49 PM (in response to rmarblack)MB,
Have you seen this article Microsoft Excel Document into Relational Table ?
Note both designer and dynamic approaches are described there.
Ramesh..
-
4. Re: Excel translator and dynamic filenames
rmarblack Jul 26, 2015 3:34 PM (in response to rareddy)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.
-
5. Re: Excel translator and dynamic filenames
rareddy Jul 26, 2015 7:57 PM (in response to rmarblack)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..
-
6. Re: Excel translator and dynamic filenames
rmarblack Jul 29, 2015 2:21 PM (in response to rareddy)Thanks for the info Ramesh.
I'll open an enhancement request as you suggest.
Best regards
MB!