H2 parsedatetime of CSV file without knowing which columns are TimeStamps
Pablo Lozano
I have a csv file that need to import using CSVREAD. The issue is that it has date time format in a different way so a need to parse it. Can some one give me an example of how i am supposed to do it?
I tried: merge into MESSAGE (MESG_DATE_FROM,MESG_DATE_TO,MESG_DISPLAY_SEQ,MESG_TIME_DELAY,MESG_ID,REASONTYPE_MAJOR) SELECT MESG_DATE_FROM , MESG_DATE_TO, MESG_DISPLAY_SEQ,MESG_TIME_DELAY,MESG_ID,REASONTYPE_MAJOR) FROM CSVREAD('cache\MESSAGE_0.dat');
This querys are made programatically so they cannot be handmade. The problem is that some columns are datetime type and are in a different datetime format from the one in H2, at the moment of parsing i have no precise way of determining which columns will be datetime so i cannot easily put a PARSEDATETIME and the CSV file doesnt contains any column names or info, just the values. Like this: 2011-11-18 00.00.00.00,2030-12-31 00.00.00.00,1,20000,1,0, ...
...
An Sql file will be generated to load this CSV to each table but it seems i need to know if a column is of TIMESTAMP type to add PARSEDATETIME(MESG_DATE_FROM,'yyyy-mm-dd hh.mm.ss.uu') as MESG_DATE_FROM to the sql. Tables and Columns are autogenerated by code and i can't control if a column will be of certain type or a new column is added. As of right now the only way i can imagine of adding the PARSEDATETIME(MESG_DATE_FROM,'yyyy-mm-dd hh.mm.ss.uu') as MESG_DATE_FROM is to ask each column for its type at runtime.
In DB2 we could use a timestampformat=YYYY-MM-DD HH.MM.SS.UU in the merge query so this was done in a default way for tables. Is there anythis similar in H2?
Thomas Mueller
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.