3 Replies Latest reply on Apr 9, 2013 8:15 AM by Ramesh Reddy

    Flat File Parsing

    devin pinkston Newbie

      Hello,

       

      Using Teiid and a dynamic VDB I'm trying to use a flat file data source (.txt file) but keep receiving errors when trying to query.  I have tried to follow the example found here but i keep receiving an error: https://community.jboss.org/wiki/TextToTableWithTeiid

       

      The error I'm receiving when issuing this query:

       

      Query:

      "select edinburgh.* from (call weather.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS index INT, theYear int, day int, time int, ap int, rainfall double, windspeed double, temp double DELIMITER ',' HEADER) edinburgh";

       

       

      Error:

      org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "f.file COLUMNS index [*]INT[*], theYear" at line 1, column 96.

      Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...

       

       

      Here are the first few rows of my .txt file:

       

      index, year, day, time, ap, rainfall, windspeed, temp

      101,2013,67,1558,1004,0,1.917,3.342

      101,2013,67,1559,1004,0,1.854,3.406

      101,2013,67,1600,1004,0,3.396,3.37

      101,2013,67,1601,1004,0,3.354,3.311

       

      I have been using that link to try and return some results but have had no such luck.  Would it be possible to create a view instead?

       

      Thanks in advance!

        • 1. Re: Flat File Parsing
          Ramesh Reddy Master

          It is telling you to change "INT" to "integer"

          • 2. Re: Flat File Parsing
            devin pinkston Newbie

            Ramesh,

             

            I orginally had it as integer, but i changed it because i thought i may of had it wrong due to this error.  When i use it this way:

             

            String sqlq = "select edinburgh.* from (call weather.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS index integer, year integer, day integer, time integer, ap integer, rainfall double, windspeed double, temp double DELIMITER ',' HEADER) edinburgh";

             

            I receive this error.  This is what I have been receiving:

            org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "index integer, [*]year[*] integer," at line 1, column 105.

            Was expecting: id

             

            Is this telling me that I need a primary key by chance?  That column "year" is just "2013" everytime.

             

            Thanks for your help, I appreciate it.

            • 3. Re: Flat File Parsing
              Ramesh Reddy Master

              "index", "year", "day", "time" all are reserved words, so either change it to some other name, or put double quotes around it.