Help with Copy command

[id10t]

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

[johnr]

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

[id10t]

Hi!


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)
  

Case many fields contains '\r' and there are few files to load:

• 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)
  

[johnr]

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

[id10t]

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:

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)

[johnr]

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

[id10t]

Hi!

What type and constrains of ACCT_NOTES column?