Flat File Parsing

[pinkstondevin]

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!

[rareddy]

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

[pinkstondevin]

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.

[rareddy]

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