select ProjectNo from ExcelSheet
Can give example of result you expecting?
Suppose my excel file looks like this:
ProjectID: 12345678 (this is the ID) No Cost description Cost 1 new software 1000 2 new hardware 7000
Please ignore the coloring of the first row. The first row would basically tell me which project this file belongs to. Now what I want is, I want this to be exposed like:
ProjectNo the other data 12345678 ... 12345678 ...
So I can query sth like: SELECT cost FROM myexcel.sheet1 WHERE ProjectID = "12345678"
I'd expect to receive the cost column for this particular project.
Best would then be to have all the excelfiles for each project in one large merged source (this problem goes hand in hand with the automated adding of excel files noted here: How can I (Automatically) add multiple Excel files in the same folder as a datasource? ). So I could query: SELECT cost FROM myexcel.sheet1 JOIN myexcel2.sheet1 JOIN .... WHERE ProjectID = "12345678"
or SELECT cost FROM combinedexcel.sheet WHERE ProjectID = "12345678"
Right now I'm fully aware I'd have to add each excel file to the query with a JOIN statement.
You can set which row is the first "data row" in the metadata
as for columnd aliases, that you can also set in the metadata
the header row is specified as a model property, instead
this is working for me, but is taken form the teiid quickstarts:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Excel" version="1">
<description>Excel Dynamic VDB</description>
<property name="UseConnectorMetadata" value="true" />
<property name="importer.headerRowNumber" value="1"/>
<property name="importer.ExcelFileName" value="test.xls"/>
<source name="excelconnector" translator-name="excel" connection-jndi-name="java:/excel-file"/>
SET NAMESPACE 'http://www.teiid.org/translator/excel/2014' AS teiid_excel;
CREATE FOREIGN TABLE Sheet1 (
ROW_ID integer OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_excel:CELL_NUMBER" 'ROW_ID'),
ACCOUNT_ID integer OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '1'),
PRODUCT_TYPE string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '2'),
PRODUCT_VALUE string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '3'),
CONSTRAINT PK0 PRIMARY KEY(ROW_ID)
) OPTIONS ("teiid_excel:FILE" 'test.xls', "teiid_excel:FIRST_DATA_ROW_NUMBER" '2');
Thanks, but what I need is an option to NOT ignore the first row and instead use the single value 12345678 to fill a column with.
Uh ok. I guess you can't change your xls files adding a simple formula like "=$B1" in all first column cells...
I don't think Teiid can use metadata to help you get what you want directly, but maybe this could be a workaround, perhaps (in theory, never tried):
for each xls file, create 2 different models for the same xls file.
- the first, MODEL1, reads the xls, but has the first row as "data"
- the second, MODEL2, reads the same xls, but has the second row as "headers" ,and the third as "data start"
then create a vdb with those two sources.
then (either in the vdb, as a view or in your client, as a query), you can create a view that merges
- SELECT "columnB" from MODEL1 LIMIT 1 (since "columnB", "row1" has you project ID)
- SELECT * from MODEL2
(SELECT "columnB" from MODEL1 LIMIT 1) as project_ID,
FROM MODEL2 AS M2
and you can probably also use UNION to create a "master" multiproject table from all xls files
Then you can query THIS merged view as
WHERE project_ID = 12345678
You should get something like what you are looking for (or you get just another bad idea by me... )
I guess that would work. Will try this. Thanks!