Help with Copy command

[johnr]

Hi sKwa,

The target datatype of CUSTOMER_NOTES in Vertica Table is VARBINARY and the incoming data is a binary string.

Thanks,
-John

[id10t]

Hi!

If data type is VARBINARY why do you define FILLER as VARCHAR?

• Will emulate BINARY data:

  Code: Select all

  dbadmin=> SELECT 'abc'::BINARY(3);
   ?column? 
  ----------
   abc
  

• Lets see it as binary data - 1010101:

  Code: Select all

  dbadmin=> SELECT TO_BITSTRING('abc'::varbinary(3));
         TO_BITSTRING       
  --------------------------
   011000010110001001100011
  

• Replace 'a' with '@'.

  Code: Select all

  dbadmin=> select BITSTRING_TO_BINARY(REPLACE(TO_BITSTRING('abc'::varbinary(3)),TO_BITSTRING('a'::binary), TO_BITSTRING('@'::binary)));
   BITSTRING_TO_BINARY 
  ---------------------
   @bc
  

NOTE: Don't forget to convert final result to binary data type if you want it as binary/varbinary data type, but I recomend you to convert binary data to varchar.

[johnr]

Hi sKwa,

Sorry to keep fussing on this...

To re-itereate the problem. There are control characters within a data string. So anything I do to convert it the carriage return at the eof also gets removed and the subsequent record is brought to the same line and gets treated as one single long record.

Eg:

700015265|AGENCY| auth by Craig Anderson <control character>___________________Bernadette
700045452|INDIVIDUAL| Roy Derek <control character>___________________Louisa

When I try to replace or remove the control chars in each of the above recs, the end of record gets eliminated and the subsequent record (700045452) moves up and gets treated as a single record as part of 700015265.

We want to preserve each record as it is but remove the control characters within the data strings in each of the records.
Will it be possible to do it using COPY? Or you reckon we should use a script to clean up the file first and then try loading?

I am not sure if we can use TO_BINARY because COPY doesn't seem to have the provision to provide this as a parameter.

Sorry to harp on this again. Your help is much appreciated.

Many Thanks,
-John

[id10t]

Hi John!

Can you provide a sample of data file?

thnx
~Daniel

[johnr]

Hi Daniel,

Thanks for your reply.

How do I attach a screenshot? It is not allowing me to attach any file type. I tried .bmp, .txt and .doc

Thanks again,
John

[id10t]

Hi John!


Can you upload sample file (max 10 rows) to some free file hosting and share it with us? There are many free file hosting services - Google Drive, DropBox, filehosting.org (http://www.smashingapps.com/2008/08/28/ ... files.html).
Also, can you repeat on table definition (columns amount and data type, names of columns less important to me)?

[johnr]

Hi Daniel,

Thanks again.

I managed to resolve the carriage return in between strings within excel itself using

=SUBSTITUTE(SUBSTITUTE(<cell_number.Eg:M4>,CHAR(13)," "),CHAR(10)," ")

What I noticed strange was when I see this file in the desktop it shows the control characters. When I downloaded on to my laptop I couldn't see the control characters. Instead, it was showing a space.

Will get back to you a little later if I am able to upload the file.

Thanks,
-John