Using the COPY command

[chithu21]

Hi,

I have to load data from a csv file to a table in vertica DB.
The mapping is like the 2nd field in the csv file should be loaded to the 3rd column in the table.

Is it possible to do this using COPY command or in any other way in Vertica?

Thanks in advance

[JimKnicely]

Hi,

Welcome to the forums! Check out the FILLER option of the COPY command. You can use it to skip columns.

For example:

Code: Select all

dbadmin=> create table test_data (a varchar(1), b varchar(1), c varchar(1), d varchar(1), e varchar(1), f varchar(1));
CREATE TABLE
dbadmin=> \! cat /home/dbadmin/test_data.txt
a|b|c|d|e
dbadmin=> copy test_data (af filler varchar(1), bf filler varchar(1), cf filler varchar(1), df filler varchar(1), ef filler varchar(1), b as af, c as bf, d as cf, e as df, f as ef) from '/home/dbadmin/test_data.txt';
 Rows Loaded 
-------------
           1
(1 row)

dbadmin=> select * from test_data;
 a | b | c | d | e | f 
---+---+---+---+---+---
   | a | b | c | d | e
(1 row)

See how the data is shifted one column to the right?

[nnani]

Hello chithu21,

You can refer this link for loading a CSV file.

http://www.vertica-forums.com/viewtopic ... ller#p3110

Hope this helps..

[chithu21]

Thank you all

I need a single row to be loaded with data from multiple files.
Is it possible?

[nnani]

#chithu21

I don't see the requirement of a NOT NULL column in the table schema, if you don't want to populate it.

You can't skip the NOT NULL columns while loading data.

Instead you can try and follow this approach

1. You need to have two tables one as a stage and one as a target

2. Set the all the columns to NULL in stage and set the actual NOT NULL columns same in Target table

3. Then while loading use COPY and load into stage table

4. Then load target table according to your calculated values using INSERT INTO TABLE SELECT AS statement.

OR

if you have a empty string while loading the NOT NULL column, try populating an empty string with some other value using the NULL option in COPY command

Hope this helps..