Delimiters other than Pipe and Tab

[id10t]

Hi!

Vertica support any ASCII character in COPY statement except `\000` - end of string. For example char PERCENT `%` is `\045` in OCT BASE.

• Create data with PERCENT as delimiter

  Code: Select all

  %> cat out.dat 
  foo%bar
  baz%egg
  sux%sex
  

• Create table

  Code: Select all

  %> vsql -c "create table PercentDelimiter (col1 varchar(3), col2 varchar(3))"
  CREATE TABLE
  

• Load data with PERCENT as delimiter

  Code: Select all

  %> vsql -c "copy PercentDelimiter from '/home/dbadmin/out.dat' delimiter e'\045' direct" 
  Rows Loaded 
  -------------
             3
  (1 row)
  

• Show data

  Code: Select all

  %> vsql -c "select * from PercentDelimiter"
   col1 | col2 
  ------+------
   baz  | egg
   foo  | bar
   sux  | sex
  (3 rows)
  

---
PS In such way you can use ESCAPE char as delimiter or any non-printable char as delimiter

[rajasekhart]

Hi sKwa,

Thats solved my problem.

Thanks a lot,
Raj.

[rajasekhart]

Hi Knicely,

I am generating the files in the following way.

please find the attachment.

Thanks,
Raj.

[JimKnicely]

Raj,

Are you copying data from Vertica to Vertica? I'm asking because it looks like you're generating your data files from a Vertica database and this post thread began as a question about how to load data into Vertica...

Thanks!

[johnr]

Hi Knicely and all,

Thanks for the replies.

My prob is the data is generated from a different environment and we don't have control over it.
So, we can't explicitly go and change the format of the data after it has arrived since it is multiple files with millions of rows.
More than that, "|" is just one of them. There are a number of other characters as well which have been included in the data.

I did try as suggested in one of the replies different delimiters and went with what was allowed to be loaded. Thanks for the suggestions and recommendations. Much appreciated.

-John

[johnr]

Hi Raj,

Thanks for your reply. I did try with different delimiters and the one that is not part of the data obviously works.

-John

[jpcavanaugh]

Are you exporting from another Vertica environment?