Hi sKwa,
I did try as you have said in the reply but the records still get rejected. The data type of the date columns in table are DATE. The incoming data are included with a "/" instead of just the date parts. I am not sure if FORMAT is working the way it is expected to.
Also, this is not a table to table load. The data is in a delimited file that needs to be loaded into a table with DATE datatype.
Thanks,
-John
Help with Copy command
Moderator: NorbertKrupa
Re: Help with Copy command
Last edited by johnr on Wed Aug 01, 2012 3:04 am, edited 1 time in total.
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: Help with Copy command
Can you post your copy command and a single row from your file that is failing?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Help with Copy command
Hi johnr,
Is your date data in the format 09/19/2012 or 2012/09/19, for example?
If so why not just enclose the values in quotes? Then you should be able to load date relatively easy into columns with a DATE data type...
Is your date data in the format 09/19/2012 or 2012/09/19, for example?
If so why not just enclose the values in quotes? Then you should be able to load date relatively easy into columns with a DATE data type...
Code: Select all
dbadmin=> \! cat /usr/home/dbadmin/date_load_test.txt
"09/19/2012"|"2012/09/19"
dbadmin=> create table tab1 (col1 date, col2 date);
CREATE TABLE
dbadmin=> copy tab1 from '/usr/home/dbadmin/date_load_test.txt';
Rows Loaded
-------------
1
(1 row)
dbadmin=> select * from tab1;
col1 | col2
------------+------------
2012-09-19 | 2012-09-19
(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.
Re: Help with Copy command
Hi knicely,
The date data which am gettig is in the format 25/07/2012 (DD/MM/YYYY). I understand that the default setting in Vertica is MDY but there seems to be no way where we can change the format at a database level. SET DATESTYLE will only works for the session you have logged in.
Thanks,
-John
The date data which am gettig is in the format 25/07/2012 (DD/MM/YYYY). I understand that the default setting in Vertica is MDY but there seems to be no way where we can change the format at a database level. SET DATESTYLE will only works for the session you have logged in.
Thanks,
-John
Re: Help with Copy command
Hi juniorfoo,
Below is a code snippet am using. It runs in a .sh script.
Also, find below a row of data.
echo "COPY $schema.$tablename(SYS_CUST_ID, ACCT_ NOTES ESCAPE AS '_', ) from local '$staging$filename' DELIMITER '|' REJECTED DATA '/home/dbadmin/SOLBRIGHT/reject_sol.txt' DIRECT NULL AS '' TRAILING NULLCOLS EXCEPTIONS '$ERR_LOG_FILE';" > /home/dbadmin/scripts/var_sql.sql
ESCAPE AS doesn't seem to work using the above code snippet.
Thx
-John
Below is a code snippet am using. It runs in a .sh script.
Also, find below a row of data.
echo "COPY $schema.$tablename(SYS_CUST_ID, ACCT_ NOTES ESCAPE AS '_', ) from local '$staging$filename' DELIMITER '|' REJECTED DATA '/home/dbadmin/SOLBRIGHT/reject_sol.txt' DIRECT NULL AS '' TRAILING NULLCOLS EXCEPTIONS '$ERR_LOG_FILE';" > /home/dbadmin/scripts/var_sql.sql
ESCAPE AS doesn't seem to work using the above code snippet.
Thx
-John
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Help with Copy command
johnr,
Can you use the FORMAT parameter of the COPY command to load your dates? I think this is what sKwa was getting at earlier.
Can you use the FORMAT parameter of the COPY command to load your dates? I think this is what sKwa was getting at earlier.
Code: Select all
dbadmin=> \! cat /usr/home/dbadmin/date_test.txt
25/07/2012
dbadmin=> \d test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
public | test | col1 | date | 8 | | f | f |
(1 row)
dbadmin=> copy test (col1 FORMAT 'DD/MM/YYYY') from '/usr/home/dbadmin/date_test.txt';
Rows Loaded
-------------
1
(1 row)
dbadmin=> select * from test;
col1
------------
2012-07-25
(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.
Re: Help with Copy command
Hi there...
Can anyone please tell me what's the syntax to use in COPY command to ignore carriage returns.
The carriage return in the file are a small square box.
I tried ESCAPE AS but its not working.
Any help is much appreciated.
Thanks,
-John
Can anyone please tell me what's the syntax to use in COPY command to ignore carriage returns.
The carriage return in the file are a small square box.
I tried ESCAPE AS but its not working.
Any help is much appreciated.
Thanks,
-John