1 Reply Latest reply on Feb 19, 2013 6:31 PM by Steven Hawkins

    TEXTTABLE and date formats

    Peter Larsen Newbie

      How do you specify a date format when using TEXTTABLE?  I don't see it documented what the default format expected is, but in most cases I would want to read what-ever date format my source gave me.

       

      I created stocks-jan.csv (see attachment) and want to create a simple view on the file. My very basic transformation looks like:

       

      SELECT

              stock.*

          FROM

              (EXEC YahooQuotes.getTextFiles('*.csv')) AS f, TEXTTABLE(f.file COLUMNS symbol string, trade_date date, open_price decimal, high_price decimal, low_price decimal, close_price decimal, volume long, adj_close decimal) AS stock

       

      Where YahooQuotes is an ODA Flat Data source definition.

       

      First off, when I do a view sample data I get a single row with nonsense data in it (ABCEDEF ... etc for strings, and NULL for everything else) - is there no way to test a text data source without deploying?

       

      Second, when I run the above basic transformation I get

       

      Error Code:ERR.003.029.0018 Message:Could not convert value for column trade_date in the row ending on text line 1 in file:/home/jboss/stocks/stocks-jan.csv.

       

      Clearly, "yyyymmdd" isn't the date format expected. How do I specify the format?

        • 1. Re: TEXTTABLE and date formats
          Steven Hawkins Master

          > First off, when I do a view sample data I get a single row with nonsense data in it (ABCEDEF ... etc for strings, and NULL for everything else)

           

          That would be from using the loopback translator.  The current incarnation of the loopback translator does not make it useful for reproducing sample data.  That was recently discussed here: https://issues.jboss.org/browse/TEIID-2285 but no follow-on issue was yet logged.

           

          > is there no way to test a text data source without deploying

           

          In your case it would seem straight-forward to also deploy a sample data file.

           

          > Clearly, "yyyymmdd" isn't the date format expected. How do I specify the format?

           

          The standard date format is yyyy-MM-dd (the default logic is very restrictive as we need to be precise for pushdown).  The workaround is to initially project the column as string and then use a parseDate function in the SELECT clause.  You can also log an enhancement if you want to specify patterns as part of the texttable itself.

           

          Steve