How to import CSV with copy and timestamp

Hello, how I can import Data from a CSV with timestamp: My CSV files are named like expMS0.csv - expMS1000. And a row looks like "06.02.2014;01:40:00;7748.78;6438.62;6003.05;104.38;55.44;394.18;1.19;27.57;44.34;152.53;34.93;101.9;46.22;14.45;66.09;2679.35;2442.12;1503.87;50.41;190.21;65.91;1545.27;17.74;836.2;144.62;-42.88;0.95;0.86;0.9;0.91;0.55;0.47;0.94;0.04;0.34;0.47;0.57;0.2;0.47;0.79;0.31;0.6;0.73;0.85;0.78;0.33;0.74;0.62;0.9;0.32;0.96;0.76;-0.24;0.26" So i need to convert the first to (06.02.2014;01:40:00) into a timestamp and insert it into "Datum" the rest should just copy to Power1-Power27 and Phi1-PhiP27. A insert statment looks like that: "INSERT INTO Daten_hour (power1,power2,power3,power4,power5,power6,power7,power8,power9,power10,power11,power12,power13,power14,power15,power16,power17,power18,power19,power20,power21,power22,power23,power24,power25,power26,power27,phi1,phi2,phi3,phi4,phi5,phi6,phi7,phi8,phi9,phi10,phi11,phi12,phi13,phi14,phi15,phi16,phi17,phi18,phi19,phi20,phi21,phi22,phi23,phi24,phi25,phi26,phi27, Datum)" How this works with the "Copy Statment" and where I need to set the files and where I need to execute the command. Thanks for help


  • Hi Sebastian!

    First you need to create the target table

    CREATE TABLE [tableName] (Datum TIMESTAMP, col1 Format1, col2 Format2,..., coln Formatn);

    You would need to structure COPY as follows

    COPY  [tableName] (Datum, col1,col2,...,coln)
    FROM LOCAL 'fileAddress'

    I can see you need to perform a transformation first, that you should do before trying COPY. You can choose to do this manually or scripted once, or I recall a part of the UDF documentation about LOADING UDFs.



    $ cat /tmp/
    Version 1:
    daniel=> create table version1 (d date, t time, tm timestamp default (d || ' '|| t)::timestamp, num1 float, num2 float);
    daniel=> copy version1(d format 'DD.MM.YYYY',t,num1,num2) from '/tmp/' direct delimiter ';' abort on error;
    Rows Loaded
    (1 row)
    daniel=> select * from version1;
    d | t | tm | num1 | num2
    2014-02-06 | 01:40:00 | 2014-02-06 01:40:00 | 7748.78 | 6438.62
    2014-02-07 | 00:12:13 | 2014-02-07 00:12:13 | 1000 | 9999.99
    2014-09-08 | 12:00:00 | 2014-09-08 12:00:00 | 2000 | 8888.99
    (3 rows)

    Version 2
    daniel=> create table version2 (tm timestamp, num1 float, num2 float);
    daniel=> copy version2(datefiller filler varchar, timefiller filler varchar, tm as (datefiller || ' ' || timefiller)::timestamp,num1,num2) from '/tmp/' direct delimiter ';' abort on error;
    Rows Loaded
    (1 row)
    daniel=> select * from version2;
    tm | num1 | num2
    2014-06-02 01:40:00 | 7748.78 | 6438.62
    2014-07-02 00:12:13 | 1000 | 9999.99
    2014-08-09 12:00:00 | 2000 | 8888.99
    (3 rows)

  • Hello,
    thanks for your reply.
    I made my table already thats not the problem.
    Also I can load the data with my own Programm line by line but I think the copy statement would be much much faster. I only need to transform the date and the time into a timestamp the rest i just need to insert. 
    And from local means the path from the root directory?
    The rest values are all float.

  • local - means path that not known and/or not mounted to Vertica cluster: remote, from client machine for example.
    (read not known to Vertica executable instance/binary file)
  • THANKS!!!!
    Helped me so much.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file