CODEWITHSUNDEEP
vertica
roman
roman

Reputation: 667

vertica copy command with based on the content of the csv

I'm trying to run copy command that populate the db based on concatination of the csv.

db columns names are:

col1,col2,col3

csv content is (just the numbers, names are the db column names):

1234,5678,5436

what i need is a way to insert data say like this: based on my example:

i want to put in the db:

col1, col2, col3
1234, 5678  "1234_XX_5678"

should i use FILLERS? if so what is the command?

my starting point is:

COPY SAMPLE.MYTABLE (col1,col2,col3) 
FROM LOCAL 
'c:\\1\\test.CSV' 
UNCOMPRESSED DELIMITER ',' NULL AS 'NULL' ESCAPE AS '\' RECORD TERMINATOR '
' ENCLOSED BY '"' DIRECT STREAM NAME 'Identifier_0' EXCEPTIONS 'c:\\1\\test.exceptions' 
REJECTED DATA 'c:\\1\\test.rejections' ABORT ON ERROR NO COMMIT;

can you help how to load those columns (basically col3)

thanks

Upvotes: 0

Views: 1526

Answers (2)

marcothesane
marcothesane

Reputation: 6721

You don't even have to make the two input columns - which you load as-is anyway - FILLERs. This will do:

COPY mytable (
  col1
, col2  
, col3f FILLER int
, col3 AS col1::CHAR(4)||'_XX_'||col2::CHAR(4)
)
FROM LOCAL 'foo.txt'
DELIMITER ','

Upvotes: 0

Up_One
Up_One

Reputation: 5271

There different ways to do this.

1 - Pipe the data into vsql and do the data edit on the fly using linux

Eg:

cat file.csv |sed 's/,/ , /g' | awk {'print $1 $2 $3 $4 $1"_XX_"$3'} 
|vsql -U user -w passwd  -d dbname -c "COPY tbl FROM STDIN DELIMITER ',';"

2 - Use Fillers

    copy tbl(
v1 filler int ,
v2 filler int ,
v3 filler int,
col1 as v1,
col2 as v2,
col3 as v1||'_XX_'||v2) from '/tmp/file.csv' delimiter ',' direct;

    dbadmin=> select * from tbl;
 col1  | col2  |     col3
------+------+--------------
 1234 | 5678 | 1234_XX_5678
(1 row)

I hope this helps :)

Upvotes: 1

Related Questions