Help with Copy command

[johnr]

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

[jpcavanaugh]

Can you post your copy command and a single row from your file that is failing?

[JimKnicely]

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...

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)

[johnr]

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

[johnr]

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

[JimKnicely]

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.

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)

[johnr]

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