1 Reply Latest reply on Sep 22, 2010 10:08 AM by rareddy

    How to model a CSV file?

    carlossc
    carlossc Sep 22, 2010 10:05 AM

      I need to access to a simple CSV file using Teiid (I will later integrate the data from this file with other data from a DB)

       

      The CSV has this structure and the separator is the character '='

       

      key=value
a=0
b=1
...

       

       

      I created a 'Relational Source Model' called 'ds_file1'. Inside it, I created 'Base Table' with the columns 'key' and 'value' with types 'string' and 'int' respectively.

       

      Then, I tried to set a 'Connection Profile' (right click on the new model -> Modelling -> Set Connection Profile)

      There,I created a new 'Flat File DataSource'. After that, I selected the new 'Connection Profile' and when I clicked 'Ok', Teiid displayed the error

       

      No ConnectionInfoProvider resolved for this ConnectionProfile

       

      I continued anyway and created a 'Rational View Model' with a 'View' in it. Then, I dragged the Base Table I created early, into the 'Transformation' dialog of the view and wrote this transformation:

      SELECT
    result.key, result.value
FROM
    (EXEC ds_file1.getTextFiles('file1.csv')) AS f, TEXTTABLE(f.file COLUMNS key string, value integer DELIMITER '=' HEADER) AS result

       

      When I clicked on 'Save/Validate SQL', Teiid displays the error message:

      The query is parsable but not resolvable.
Resolver Error: demo_performance_df_ds.getTextFiles does not exist.

       

      The SQL that I wrote in the transformation may be wrong, but I was just guessing how to do it.

       

      Thanks,

      Carlos.