Help with Copy command
Moderator: NorbertKrupa
Re: Help with Copy command
Hi!
Carriage return "\r" is in field or in several field or it record terminator?
[1] If it record terminator (ie END OF LINE) so you can define COPY parameter RECORD TERMINATOR as '\r\n'.
[2] If it in single field just remove with FILLER
[3] If it in several field, so: or delete it with other tool like 'tr','sed','awk', or apply [2] on each field
Carriage return "\r" is in field or in several field or it record terminator?
[1] If it record terminator (ie END OF LINE) so you can define COPY parameter RECORD TERMINATOR as '\r\n'.
[2] If it in single field just remove with FILLER
[3] If it in several field, so: or delete it with other tool like 'tr','sed','awk', or apply [2] on each field
Re: Help with Copy command
Hi sKwa,
Thank you for your reply.
The carriage returns at the EOF is fine. But I had to ignore the one's within the data string. I got around that using ESCAPE AS E'\015' which is to ignore ^M.
But now my problem is the data strings in some of the cols have data such as '_______' . I tried using VARBINARY as the data type but everything gets rejected.
Thanks again,
-John
Thank you for your reply.
The carriage returns at the EOF is fine. But I had to ignore the one's within the data string. I got around that using ESCAPE AS E'\015' which is to ignore ^M.
But now my problem is the data strings in some of the cols have data such as '_______' . I tried using VARBINARY as the data type but everything gets rejected.
Thanks again,
-John
Re: Help with Copy command
Hi!
Case FILLER:
Case FILLER:
- Table
Code: Select all
:~> vsql -At -c "select export_tables('','esc')" CREATE TABLE public.esc ( s1 varchar(80), s2 varchar(80) );
- Data
Code: Select all
:~> echo -e "foo\rbar|baz\regg\ntux\rfox|moo\rzoo\n" > /tmp/d.dat :~> cat /tmp/d.dat egg|baz zoo|moo
- Load data:
Code: Select all
:~> vsql -c "copy esc(f1 filler varchar, f2 filler varchar, s1 as translate(f1, e'\r','%'), s2 as translate(f2, e'\r', '~')) from '/tmp/d.dat'" Rows Loaded ------------- 2
- Output
Code: Select all
:~> vsql -c "select * from esc;" s1 | s2 ---------+--------- foo%bar | baz~egg tux%fox | moo~zoo (2 rows)
- Data
Code: Select all
:~> cat /tmp/d.dat egg|baz zoo|moo
- Load data:
Code: Select all
:~> cat /tmp/d.dat | tr -t '\r' ' ' | vsql -c "copy esc from stdin"
- Ouput:
Code: Select all
:~> vsql -c "select * from esc;" s1 | s2 ---------+--------- foo bar | baz egg tux fox | moo zoo foo%bar | baz~egg tux%fox | moo~zoo (4 rows)
Re: Help with Copy command
Hi sKwa, thanks for your reply.
Hope this works for the single column as you have said because we don't want to translate the actual <EOF> at the end of each record.
Will try it out and let you know.
Many Thanks again,
-John
Hope this works for the single column as you have said because we don't want to translate the actual <EOF> at the end of each record.
Will try it out and let you know.
Many Thanks again,
-John
Re: Help with Copy command
Hi John!
1. FILLER works per column
2. To ensure that you do not deletes '\r' in end of string or last column use in REGEXP_REPLACE + zero-width negative lookahead:
1. FILLER works per column
2. To ensure that you do not deletes '\r' in end of string or last column use in REGEXP_REPLACE + zero-width negative lookahead:
Code: Select all
...as REGEXP_REPLACE(column, e'\r(?!\n)', '')...
Code: Select all
dbadmin=> select regexp_replace(e'foo\rbar\r\nbar\rboo\r\n', e'\r(?!\n)', '<--->');
regexp_replace
----------------------------
foo<--->bar
bar<--->boo
(1 row)
Re: Help with Copy command
Hi sKwa,
I tried using the filler and TRANSLATE as you had suggested but it gives the following error:
ERROR 2764: COPY: Expression for column ACCT_NOTES cannot be coerced
Code snippet below:
echo "COPY $schema.$tablename(CUST_ID, f1 filler varchar, f2 filler varchar, ACCT_NOTES AS TRANSLATE(f1, e'\r','~'), CUST_NOTES AS TRANSLATE(f2, e'\r', '~')) from local '$staging$filename' DELIMITER '}' REJECTED DATA '/home/dbadmin/rejects/reject_sol.txt'
Thanks,
-John
I tried using the filler and TRANSLATE as you had suggested but it gives the following error:
ERROR 2764: COPY: Expression for column ACCT_NOTES cannot be coerced
Code snippet below:
echo "COPY $schema.$tablename(CUST_ID, f1 filler varchar, f2 filler varchar, ACCT_NOTES AS TRANSLATE(f1, e'\r','~'), CUST_NOTES AS TRANSLATE(f2, e'\r', '~')) from local '$staging$filename' DELIMITER '}' REJECTED DATA '/home/dbadmin/rejects/reject_sol.txt'
Thanks,
-John
Re: Help with Copy command
Hi!
What type and constrains of ACCT_NOTES column?
What type and constrains of ACCT_NOTES column?