wuth those csv preprocessed to look like

ReturnCode,LatestCommTime,Address,MacAddress,ModelName,DeviceName,Comment,TotalCounter

0,2015/09/17 16:16:35,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-39E733],[RICOH Aficio SP 3510SF V2.01 / ],47652

0,2015/09/17 16:16:35,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-539A78],[RICOH Aficio SP 3510SF V2.07 / ],23202

0,2015/09/17 16:16:35,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-539AF0],[RICOH Aficio SP 3510SF V2.07 / ],34355

0,2015/09/17 16:16:33,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-539B00],[RICOH Aficio SP 3510SF V2.07 / ],4342

0,2015/09/17 16:16:33,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-53B156],[RICOH Aficio SP 3510SF V2.07 / ],75335

I now can use csv dates, since I used parsetimestamp in the vdb... but can't imagine how to treat those weird <headers>

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="Contatori" version="1">

    <description>Contatori lan</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="ContatoriLan">

        <source name="text-connector" translator-name="file" connection-jndi-name="java:/contatori"/>

    </model>

    <model name="Dati" type="VIRTUAL">

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

        CREATE VIEW Stampanti (

            updated timestamp,

            address string

            )

            AS 

               SELECT PARSETIMESTAMP(x.LatestCommTime, 'yyyy/MM/dd HH:mm:ss'), x.Address

                FROM (EXEC ContatoriLan.getTextFiles('*.CSV')) AS f,

                    TEXTTABLE(f.file COLUMNS LatestCommTime string, Address string HEADER) AS x;

        ]]>

                </metadata>

    </model>

</vdb>

Sorry Ramesh, maybe I was not clear enough...

if I leave the headers like <headername>,

<ReturnCode>,<LatestCommTime>,<Address>

0,2015/09/17 16:16:35,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-39E733],[RICOH Aficio SP 3510SF V2.01 / ],47652

0,2015/09/17 16:16:35,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-539A78],[RICOH Aficio SP 3510SF V2.07 / ],23202

the vdb loads but sql fails, at least if I use a vdb like this (modified to keep those weird headers)

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="Contatori" version="1">

    <description>Contatori lan</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="ContatoriLan">

        <source name="text-connector" translator-name="file" connection-jndi-name="java:/contatori"/>

    </model>

    <model name="Dati" type="VIRTUAL">

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

        CREATE VIEW Stampanti (

            updated timestamp,

            address string

            )

            AS 

               SELECT PARSETIMESTAMP(x.<LatestCommTime>, 'yyyy/MM/dd HH:mm:ss'), x.<Address>

                FROM (EXEC ContatoriLan.getTextFiles('*.CSV')) AS f,

                    TEXTTABLE(f.file COLUMNS <LatestCommTime> string, <Address> string HEADER) AS x;

        ]]> </metadata>

    </model>

</vdb>

then I get this:

15:02:49,389 WARN  [org.teiid.RUNTIME] (teiid-async-threads - 3)  TEIID50036 VDB Contatori.1 model "Dati" metadata failed to load. Reason:TEIID30386 org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "PARSETIMESTAMP(x[*].[*]<LatestCommTime" at line 8, column 39.

Was expecting: "and" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "order" | "similar" ...: org.teiid.metadata.ParseException: TEIID30386 org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "PARSETIMESTAMP(x[*].[*]<LatestCommTime" at line 8, column 39.

Was expecting: "and" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "order" | "similar" ...

        at org.teiid.query.parser.QueryParser.parseDDL(QueryParser.java:472) [teiid-engine-8.12.3.jar:8.12.3]

        at org.teiid.metadata.MetadataFactory.parse(MetadataFactory.java:746) [teiid-api-8.12.3.jar:8.12.3]

        at org.teiid.query.metadata.DDLMetadataRepository.loadMetadata(DDLMetadataRepository.java:40) [teiid-engine-8.12.3.jar:8.12.3]

        at org.teiid.runtime.AbstractVDBDeployer$MetadataRepositoryWrapper.loadMetadata(AbstractVDBDeployer.java:84) [teiid-runtime-8.12.3.jar:8.12.3]

        at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) [teiid-engine-8.12.3.jar:8.12.3]

        at org.teiid.jboss.VDBService$6.run(VDBService.java:395) [teiid-jboss-integration-8.12.3.jar:8.12.3]

        at org.teiid.jboss.VDBService$7.run(VDBService.java:446) [teiid-jboss-integration-8.12.3.jar:8.12.3]

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_91]

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_91]

        at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_91]

        at org.jboss.threads.JBossThread.run(JBossThread.java:122)

Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "PARSETIMESTAMP(x[*].[*]<LatestCommTime" at line 8, column 39.

Was expecting: "and" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "order" | "similar" ...

        at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:214) [teiid-engine-8.12.3.jar:8.12.3]

        ... 11 more

15:02:49,408 INFO  [org.jboss.as.server] (HttpManagementService-threads - 81)  JBAS015865: Replaced deployment "contatori-vdb.xml" with deployment "contatori-vdb.xml"

15:02:49,440 INFO  [org.jboss.as.repository] (HttpManagementService-threads - 81)  JBAS014901: Content removed from location /opt/teiid-8.12.3/standalone/data/content/10/e46e431c7cbd9e9b50389fc953fe2cad6d5900/content

Thanks Steven, I tried to apply your suggestion (if I got it right):

vdb is now

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="Contatori" version="1">

    <description>Contatori lan</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="ContatoriLan">

        <source name="text-connector" translator-name="file" connection-jndi-name="java:/contatori"/>

    </model>

    <model name="Dati" type="VIRTUAL">

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

        CREATE VIEW Stampanti (

            updated timestamp,

            address string

            )

            AS 

               SELECT PARSETIMESTAMP(x.LatestCommTime, 'yyyy/MM/dd HH:mm:ss'), x.Address

                FROM (EXEC ContatoriLan.getTextFiles('*.CSV')) AS f,

                     TEXTTABLE(f.file COLUMNS LatestCommTime HEADER '<LatestCommTime>' string, Address HEADER '<Address>' string HEADER 5) AS x

        ]]>

                </metadata>

    </model>

</vdb>

while data is now back to its standard headers

#Contatore totale

#Format Version:4.2.3.0

#Data/Ora esportazione: 2015/10/05 11:13:17

#Codice Risultato,Informazioni contatore più aggiornate,Indirizzo,Indirizzo nodo,Modello,Nome,Commento,Contatore totale

<ReturnCode>,<LatestCommTime>,<Address>

0,2015/09/17 16:16:35,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-39E733],[RICOH Aficio SP 3510SF V2.01 / ],47652

0,2015/09/17 16:16:35,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-539A78],[RICOH Aficio SP 3510SF V2.07 / ],23202

but loading this vdb fails:

16:16:53,740 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-8)  JBAS015876: Starting deployment of "contatori-vdb.xml" (runtime-name: "contatori-vdb.xml")

16:16:53,763 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-6)  TEIID40118 VDB Contatori.1 added to the repository - is reloading false

16:16:53,767 INFO  [org.teiid.RUNTIME] (MSC service thread 1-6)  TEIID50029 VDB Contatori.1 model "ContatoriLan" metadata is currently being loaded. Start Time: 26/02/16 16.16

16:16:53,770 INFO  [org.teiid.RUNTIME] (MSC service thread 1-6)  TEIID50029 VDB Contatori.1 model "Dati" metadata is currently being loaded. Start Time: 26/02/16 16.16

16:16:53,771 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 2)  TEIID50030 VDB Contatori.1 model "ContatoriLan" metadata loaded. End Time: 26/02/16 16.16

16:16:53,775 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID50030 VDB Contatori.1 model "Dati" metadata loaded. End Time: 26/02/16 16.16

16:16:53,780 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31080 Dati.Stampanti validation error: TEIID31100 Parsing error: Encountered "f.file COLUMNS LatestCommTime [*]'<LatestCommTime>'[*] string," at line 1, column 168.

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

16:16:53,788 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID40073 The metadata for the VDB Contatori.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

16:16:53,791 INFO  [org.jboss.as.server] (HttpManagementService-threads - 94)  JBAS015859: Deployed "contatori-vdb.xml" (runtime-name : "contatori-vdb.xml")

16:16:53,792 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (teiid-async-threads - 1)  TEIID40003 VDB Contatori.1 is set to FAILED

Ok, no problem, if it will be corrected, I'll try that again, because it could be really useful...

btw, is there any limit to the text files size, when using such connector?

We have, in another department, other csv files quite big - several megabytes, and up to 40 -which I get from a partner (no other option until now...) updated at least weekly,

which are not currenlty managed through teiid, but if they could be integrated, too, it would also help a lot...

Thanks

Thanks, I'll test that in ther next release, and then mark this as solved, hopefully.

About size, yesterday I successfully read my 40mb CSV file, after solving an encoding-related issue: this file comes from a partner, and encoding is not specified, turns out to be "ansi", ie windows-1252 or cp-1252.

Adding that property loads the file without problems. I'll explore also caching features, before this hits production.

Marco

And I can confirm that in latest 9.0.0.Alpha2 also this now works...

As for other issues, I'll wait next 8.13.3 stable to mark this as solved.

So many thanks, Teiid devs.

Marco

I still have some problem: it works with the latest test vdb above, which has

TEXTTABLE(f.file COLUMNS LatestCommTime HEADER '<LatestCommTime>' string, Address HEADER '<Address>' string HEADER 5) AS x

but it does not if (as it is really) my fields have spaces, dots. eg:

TEXTTABLE(f.file COLUMNS LatestCommTime HEADER '<LatestComm Time>' string, Address HEADER '<Address>' string HEADER 5) AS x

TEXTTABLE(f.file COLUMNS LatestCommTime HEADER '<LatestComm.Time>' string, Address HEADER '<Address>' string HEADER 5) AS x

TEXTTABLE(f.file COLUMNS LatestCommTime HEADER '<LatestComm. Time>' string, Address HEADER '<Address>' string HEADER 5) AS x

I also tried to use ESCAPE \ or ESCAPE '\'  with

TEXTTABLE(f.file COLUMNS LatestCommTime HEADER '<LatestComm\ Time>' string, Address HEADER '<Address>' string HEADER 5) AS x

but this doesn't work either....

I'm sure I'm doing something wrong, but what? I can't find a similar example in the TEXTTABLE reference...

[edit]

Yes, this is still failing with my real use case which is, like said in the starting post,

#Contatore totale

#Format Version:4.2.3.0

#Data/Ora esportazione: 2015/10/05 11:13:17

#Codice Risultato,Informazioni contatore più aggiornate,Indirizzo,Indirizzo nodo,Modello,Nome,Commento,Contatore totale

<ReturnCode>,<Latest Comm. Time>,<Address>,<MacAddress>,<Model Name>,<Device Name>,<Comment>,<Total Counter>

0,2015/10/05 11:12:58,[192.168.x.y],[00-00-00-00-00-00],[Aficio SP 3510SF],[3510SF-39E733],[RICOH Aficio SP 3510SF V2.01 / ],48173

[/edit]

Marco

Maybe I was tired, or too confused, but now I just tried to correct the vdb xml, setting the view query like this

SELECT PARSETIMESTAMP(x.LatestCommTime, 'yyyy/MM/dd HH:mm:ss'), x.Address

FROM (EXEC ContatoriLan.getTextFiles('*.CSV')) AS f,

TEXTTABLE(f.file COLUMNS

LatestCommTime HEADER '<Latest Comm. Time>' string,

...

HEADER 5) AS x

the vdb loaded active, but it failed in squirrel, with log line:

2016-03-23 18:21:42,317 WARN  [org.teiid.PROCESSOR] (Worker229_QueryProcessorQueue58387) 8ozdZCYIDdZc TEIID30020 Processing exception for request 8ozdZCYIDdZc.5 'TEIID30181 HEADER entry missing for column name LatestCommTime in file:/mnt/common/TEST/20150917161732_Lan_T.CSV.'. Originally TeiidProcessingException TextTableNode.java:503. Enable more detailed logging to see the entire stacktrace.

...I was about to write about the fail, but then realized I had two CSV files there, and the log was referring to one in particular...

and there was the cause: my fault! Struggling to find what failed before (before seeing the typo you spotted), I changed the headers in the CSV files: I tried removing dots and spaces, from files headers, and in that way it worked, but then I restored the original headers (with dots and spaces) only in one file... I forgot about the second one: 20150917161732_Lan_T.CSV.

Now it works, again...

Thanks

Marco

Confirmed working in 8.13.3 stable.

Thanks

Marco