The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
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
0
Comments
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'
WITH DELIMITER ',';
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.
Version 2
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.
(read not known to Vertica executable instance/binary file)
Helped me so much.