COPY with pipe in string

[nnani]

Hello All,

I have come across a scenario where we need to load some data in the Vertica table.

The data here is in a special format

Flat file

Code: Select all

A|B|C|D|edfg|hi|jk|L|J|R|
A|B|C|D|hdjf|ih|gm|L|J|R|

Here the data in small letters has 2 pipes in them, which Vertica takes as delimitters, But it is a part of one string in the 5th column of flat file.
So that completely is a string which needs to be loaded into single column in Vertica table.

I am using the copy command for this, but Vertica gives "Column mismatch error" as interpreted by Vertica.

How can I load this data into Vertica in a table with 8 columns.

[id10t]

Hi!

Code: Select all

daniel=> copy nnani(c1,c2,c3,c4, f1 filler varchar, f2 filler varchar, f3 filler varchar, c5 as f1 || f2 || f3, c6,c7,c8) from stdin direct delimiter '|';  
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> A|B|C|D|edfg|hi|jk|L|J|R|
>> A|B|C|D|hdjf|ih|gm|L|J|R|
>> \.
daniel=> select * from nnani ;
 c1 | c2 | c3 | c4 |    c5    | c6 | c7 | c8 
----+----+----+----+----------+----+----+----
 A  | B  | C  | D  | edfghijk | L  | J  | R
 A  | B  | C  | D  | hdjfihgm | L  | J  | R
(2 rows)

[nnani]

Hi skwa,

Actually there is no fixed width or fixed number of pipes in the expected 5 th column.
The column is a description column. So there can be any number of strings expected in the 5th column.

[nnani]

Hello,

I studied the data in the flat file and came to a observation that the data is with specific patters in the required columns


Flat file

Code: Select all

ABC|EDF||EDF|DFT
CDE|DEW|||FGT
GHY|BFV|BFV|FDW

Now this is sure that after every 2nd delimiter there has to be a delimiter which is considered as a delimiter by Vertica ( but is a part of the string next to it),After the 3rd delimiter ther can a string or another delimiter. If there is another delimiter then the column we need to replace the 3rd delimiter with some character, if there is no delimter after 3rd delimiter and is a string, then the 3rd delimiter has to be considered a a part of string.

Can anybody help me to achieve this

[peeterskris]

If you use | as a DELIMITER, you can only use it in your input if you escape it. There is no other way for vertica to know which | is part of the column and which is an actual delimiter.

You can do two things to fix this:

- Use something else as a DELIMTER. Maybe '\t' and specify this DELIMITER when you load the data. eg:
COPY my.table DELIMITER E'\t' DIRECT FROM '/home/dbadmin/input.txt';

- Escape all occurrences of | in your input data. The default ESCAPE character in Vertica is \. So replace all | with \| in your input. Note, of course, don't do this for your actual DELIMITERS.