6 Replies Latest reply on Feb 19, 2016 10:23 AM by reijin90

    Using the value of a single Cell (Excel)

    reijin90

      Hello!

       

      I was wondering if it is possible to access the value of a specific cell and fill a row with its data.

      As an example:

      I have a single cell (A2) that contains the project number, but I want it to be exposed as column "projectNo" with each row containing the value from that one cell

      ProjectNosomeOtherData
      1337dwja
      1337dwadaw

       

      I'm afraid this is not a feature as well, since it is not mentioned in the documentation, right?

       

      Best,

      Benedikt

        • 1. Re: Using the value of a single Cell (Excel)
          rareddy

          You mean

           

          select ProjectNo from ExcelSheet

           

          Can give example of result you expecting?

          • 2. Re: Using the value of a single Cell (Excel)
            reijin90

            Suppose my excel file looks like this:

             

            ProjectID:12345678 (this is the ID)
            NoCost descriptionCost
            1new software1000
            2new hardware7000

             

            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:

             

            ProjectNothe 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.

            • 3. Re: Using the value of a single Cell (Excel)
              m.ardito

              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" />

                  <model name="excelfile">

                      <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"/>

                  <metadata type="DDL"><![CDATA[

                      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');

                   ]]> </metadata>  

                  </model>

              </vdb>

               

              Marco

              • 4. Re: Using the value of a single Cell (Excel)
                reijin90

                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.

                • 5. Re: Using the value of a single Cell (Excel)
                  m.ardito

                  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)

                  with

                  - SELECT * from MODEL2

                   

                  like

                  SELECT

                  (SELECT "columnB" from MODEL1 LIMIT 1) as project_ID,

                  M2.field1,

                  M2.field2

                  ...

                  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

                   

                  SELECT *

                  from MERGEDVIEW

                  WHERE project_ID = 12345678

                   

                  You should get something like what you are looking for (or you get just another bad idea by me... )

                   

                  Marco

                  • 6. Re: Using the value of a single Cell (Excel)
                    reijin90

                    I guess that would work. Will try this. Thanks!