Help with Copy command
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 163
- Joined: Tue Jun 19, 2012 2:44 pm
- Location: Rio de Janeiro
- Contact:
Help with Copy command
Hy everyone ,,
Can enybody help me with this error :!!!
The error is when trying to load a csv file to Vertica .
"COPY: input record has been rejected (Invalid Integer format ' ' for column ............... ) "
- i am trying to copy a table of 35.000.000 rows from an Oracle database . I had made spool in csv format with ' , ' as column delimiter.
- good to remember that i have a lot of empty values in the exported table !!
I am new to toe Vertica , i have went thru the docs but still got the answer.!!!
Thank for the help!!
Can enybody help me with this error :!!!
The error is when trying to load a csv file to Vertica .
"COPY: input record has been rejected (Invalid Integer format ' ' for column ............... ) "
- i am trying to copy a table of 35.000.000 rows from an Oracle database . I had made spool in csv format with ' , ' as column delimiter.
- good to remember that i have a lot of empty values in the exported table !!
I am new to toe Vertica , i have went thru the docs but still got the answer.!!!
Thank for the help!!
trying so hard !!!
-
- Intermediate
- Posts: 163
- Joined: Tue Jun 19, 2012 2:44 pm
- Location: Rio de Janeiro
- Contact:
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Help with Copy command
Hi Adrian,
Welcome to the forums!
In your file how are you representing the NULL values? If it is a double single quote ('') then Vertica will interpret it as a string which will cause the error you are getting.
You can use no values to represent NULL values, i.e. in my example you see the double comma. These load fine:
Or you can specify some sort of text, i.e. "\n" or "NULL" and then use the NULL AS '' option of the copy command:
Welcome to the forums!
In your file how are you representing the NULL values? If it is a double single quote ('') then Vertica will interpret it as a string which will cause the error you are getting.
Code: Select all
dbadmin=> \! cat /usr/home/dbadmin/load.txt;
test1,1,test1
test2,'',test2
test3,3,test3
dbadmin=> \d test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
public | test | col1 | varchar(100) | 100 | | f | f |
public | test | col2 | int | 8 | | f | f |
public | test | col3 | varchar(100) | 100 | | f | f |
(3 rows)
dbadmin=> copy test from '/usr/home/dbadmin/load.txt' delimiter ',' exceptions '/usr/home/dbadmin/load.err';
Rows Loaded
-------------
2
(1 row)
dbadmin=> \! cat /usr/home/dbadmin/load.err
COPY: Input record 2 has been rejected (Invalid integer format '''' for column 2 (col2)). Please see /usr/local/data/intersect/v_intersect_node0001_catalog/CopyErrorLogs/test-load.txt-copy-from-rejected-data, record 1 for the rejected record.
COPY: Loaded 2 rows, rejected 1 rows.
Code: Select all
dbadmin=> \! cat /usr/home/dbadmin/load.txt;
test1,1,test1
test2,,test2
test3,3,test3
dbadmin=> copy test from '/usr/home/dbadmin/load.txt' delimiter ',' null as '' exceptions '/usr/home/dbadmin/load.err';
Rows Loaded
-------------
3
(1 row)
Code: Select all
dbadmin=> \! cat /usr/home/dbadmin/load.txt;
test1,1,test1
test2,null,test2
test3,3,test3
dbadmin=> copy test from '/usr/home/dbadmin/load.txt' delimiter ',' null as 'null' exceptions '/usr/home/dbadmin/load.err';
Rows Loaded
-------------
3
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Intermediate
- Posts: 163
- Joined: Tue Jun 19, 2012 2:44 pm
- Location: Rio de Janeiro
- Contact:
Re: Help with Copy command
Hi there,
I need some help with the COPY command.
I have the date value coming in with a "/" but Vertica accepts only 8 characters.
I want to change this to just YYYYMMDD by using the FORMAT parameter in COPY command.
What is the syntax to include this?
Also, the incoming data has "|" within the data string and the delimiter is also "|". I was wondering if we can get around this by using
ESCAPE AS.
If somebody can help with the syntax for both these parameters that can be included the COPY it would be much appreciated.
Code snippet:
COPY $schema.$tablename from local \’$staging$filename\’ DELIMITER \’|\’ REJECTED DATA ’/home/dbadmin/ACTUALS/reject_sol.txt\’ DIRECT NULL AS \’\’ TRAILING NULLCOLS EXCEPTIONS \’$ERR_LOG_FILE\’;\” /home/dbadmin/scripts/var_sql.sql
Many Thanks,
-John
I need some help with the COPY command.
I have the date value coming in with a "/" but Vertica accepts only 8 characters.
I want to change this to just YYYYMMDD by using the FORMAT parameter in COPY command.
What is the syntax to include this?
Also, the incoming data has "|" within the data string and the delimiter is also "|". I was wondering if we can get around this by using
ESCAPE AS.
If somebody can help with the syntax for both these parameters that can be included the COPY it would be much appreciated.
Code snippet:
COPY $schema.$tablename from local \’$staging$filename\’ DELIMITER \’|\’ REJECTED DATA ’/home/dbadmin/ACTUALS/reject_sol.txt\’ DIRECT NULL AS \’\’ TRAILING NULLCOLS EXCEPTIONS \’$ERR_LOG_FILE\’;\” /home/dbadmin/scripts/var_sql.sql
Many Thanks,
-John
Re: Help with Copy command
Hi John!
- Table:
Code: Select all
CREATE TABLE public.Dates ( d1 date, d2 date );
- COPY statement
Code: Select all
test_db=> copy Dates (d1 FORMAT 'YYYYMMDD', d2 FORMAT 'DDMMYYYY') from stdin DELIMITER ','; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 20120130,31012012 >> \.
Code: Select all
test_db=> copy Dates (d1 FORMAT 'YYYYMMDD',d2) from stdin DELIMITER ','; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 20120130,2000-01-01 >> \.
- COPY - crazy date format with error in input!!!:
Code: Select all
test_db=> copy Dates (d1 FORMAT 'YYYY~MM~DD',d2 FORMAT 'YYYY##MM##DD') from stdin DELIMITER ','; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 2000~03~09,1999##12#31 >> \.
- Result
Code: Select all
test_db=> select * from Dates ; d1 | d2 ------------+------------ 2012-01-30 | 2000-01-01 2012-01-30 | 2012-01-31 2000-03-09 | 1999-12-01 (3 rows)
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Help with Copy command
johnr,
viewtopic.php?f=4&t=179&p=1006#p1006
Please check this post and see if it helps...Also, the incoming data has "|" within the data string and the delimiter is also "|". I was wondering if we can get around this by using
ESCAPE AS.
viewtopic.php?f=4&t=179&p=1006#p1006
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.