Vertica Loading data from file to table

Question

I want to load data from file to a vertical table. The data I am receiving in file is in format 02-APR-17 11.06.31.000000 AM +00:00 but the data type of column is timestamp(0) and the expected format of output is 4/2/2017 12:06:31.000000

And what is your attempt at this? Show us what you've done so far. — blueren, May 02 '17 at 07:38
i tried this query SELECT TO_TIMESTAMP('02-APR-17 11.06.31.000000 AM +00:00','DD-MON-YY HH24:MI:SS.MS AM') AT TIME ZONE 'CST'; the query works fine if the data is already present in the table. but how to while loading from file to table is where i am hung up right now — pinak bhalerao, May 02 '17 at 07:45

score 2 · Accepted Answer · answered May 02 '17 at 14:26

If this is your table:

    CREATE TABLE foo (
      id INT NOT NULL
    , ts TIMESTAMP(0)
    , nm VARCHAR(32)
    );

And this is your data:

    42;02-APR-17 11.06.31.000000 AM +00:00;Arthur Dent
    43;14-APR-17 11.06.31.000000 PM +00:00;Ford Prefect
    44;15-APR-17 09.06.31.000000 PM +00:00;Zaphod Beeblebrox

Then, try this COPY command, where you use FILLER and a derivation:

    COPY foo (
      id
    , ts_buf FILLER VARCHAR(40)
    , ts AS TO_TIMESTAMP(ts_buf,'DD-MON-YY HH24:MI:SS.MS AM') AT TIME ZONE 'CST'
    , nm
    )
    FROM '/home/dbadmin/data/foo.txt'
    DELIMITER ';'
    DIRECT
    ;

.. and this is the table you get:

SELECT * FROM foo;

id|ts                 |nm
42|2017-04-02 11:06:31|Arthur Dent
43|2017-04-14 23:06:31|Ford Prefect
44|2017-04-15 21:06:31|Zaphod Beeblebrox

Is this what you were after?

cheers - Marco the Sane

Vertica Loading data from file to table

1 Answers1