How to load a simple CSV?

[JimKnicely]

Yang, Does the following help?

Here is the data file:

Code: Select all

dbadmin=> \! cat test1.txt
"a""b","c
d"

I want to load the table TEST with the data. The table looks like this:

Code: Select all

dbadmin=> \d test;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | test  | a      | varchar(512) |  512 |         | f        | f           |
 public | test  | b      | varchar(512) |  512 |         | f        | f           |
(2 rows)

Here's the COPY command I'll use first:

Code: Select all

COPY test (
  fa FILLER VARCHAR(512),
  fb FILLER VARCHAR(512),
  a AS TRIM(BOTH '"' FROM fa),
  b AS TRIM(BOTH '"' FROM fb))
FROM '/usr/home/dbadmin/test1.txt'
  DELIMITER ','
  ESCAPE AS E'\001'
  RECORD TERMINATOR E'"\n';

Now I'll run the COPY command:

Code: Select all

dbadmin=> COPY test (
dbadmin(>   fa FILLER VARCHAR(512),
dbadmin(>   fb FILLER VARCHAR(512),
dbadmin(>   a AS TRIM(BOTH '"' FROM fa),
dbadmin(>   b AS TRIM(BOTH '"' FROM fb))
dbadmin-> FROM '/usr/home/dbadmin/test1.txt'
dbadmin->   DELIMITER ','
dbadmin->   ESCAPE AS E'\001'
dbadmin->   RECORD TERMINATOR E'"\n';
 Rows Loaded
-------------
           1
(1 row)

Here's the data in the table after running the COPY command:

Code: Select all

dbadmin=> select * from test;
  a   |  b
------+-----
 a""b | c
d
(1 row)

If you don't want the double quotes in the columns, here is an alternate method to get rid of 'em:

Code: Select all

COPY test(
  fa FILLER VARCHAR(512),
  fb FILLER VARCHAR(512),
  a AS TRIM(BOTH '"' FROM REPLACE(fa, '""', '"')),
  b AS TRIM(BOTH '"' FROM REPLACE(fb, '""', '"')))
FROM '/usr/home/dbadmin/test.txt'
  DELIMITER ',' 
  ESCAPE AS E'\001'
  RECORD TERMINATOR E'"\n';

Output from running the alternate COPY command:

Code: Select all

dbadmin=> COPY test(
dbadmin(>   fa FILLER VARCHAR(512),
dbadmin(>   fb FILLER VARCHAR(512),
dbadmin(>   a AS TRIM(BOTH '"' FROM REPLACE(fa, '""', '"')),
dbadmin(>   b AS TRIM(BOTH '"' FROM REPLACE(fb, '""', '"')))
dbadmin-> FROM '/usr/home/dbadmin/test.txt'
dbadmin->   ESCAPE AS E'\001'
dbadmin->   DELIMITER ','
dbadmin->   ESCAPE AS E'\001'
dbadmin->   RECORD TERMINATOR E'"\n';
 Rows Loaded
-------------
           1
(1 row)

Finally here is the data in the TEST table after running both COPY commands:

Code: Select all

dbadmin=> select * from test;
   a    |   b
--------+--------
 a""b   | c
d
 a"b | c
d
(2 rows)

[khine]

Why they throwing me the error this error
"VMart_Schema=> \! cat test1.txt
cat: test1.txt: No such file or directory "
Thanks for advance

[JimKnicely]

Hi khine,

You sure are having directory issues

The error you are getting indicates the file is not located in your current directory.

You can issue operating command from within vsql using the \! meta-command.

For example, to find out my current directory I can do this:

Code: Select all

dbadmin=> \! pwd
/usr/home/dbadmin

Note, you can also full qualify the file name you are running by providing the entire path!

As an example, if I want to run the vmart_queries.sql script in the /opt/vertica/examples/VMart_Schema directory, I would do this:

Code: Select all

dbadmin=> \i /opt/vertica/examples/VMart_Schema/vmart_queries.sql

[khine]

Hi Jimey i did follow your advice ,but why i can't create a txt file using \! ?
Below is the command that i had try .Thank you
VMart_Schema=> \i pwd
pwd: No such file or directory
VMart_Schema=> \! pwd
/opt/vertica/examples/VMart_Schema
VMart_Schema=> \! cat test1.txt
cat: test1.txt: No such file or directory
VMart_Schema=> create test1.txt
VMart_Schema-> "a""b","cd"
VMart_Schema-> \d test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
public | test | a | varchar(512) | 512 | | f | f |
public | test | b | varchar(512) | 512 | | f | f |
(2 rows)

VMart_Schema-> COPY test(
VMart_Schema(> fa FILLER VARCHAR(512),
VMart_Schema(> fb FILLER VARCHAR(512),
VMart_Schema(> a AS TRIM(BOTH '"'FROM fa),
VMart_Schema(> b AS TRIM(BOTH '"'FROM fb))
VMart_Schema-> FROM '/opt/vertica/examples/VMart_Schema/test1.txt'
VMart_Schema-> DELIMITER','
VMart_Schema-> ESCAPE AS E'\001'
VMart_Schema-> RECORD TERMINATOR E'"\n';
ERROR 4856: Syntax error at or near "test1" at character 8
LINE 1: create test1.txt

[JimKnicely]

You can create a text file from within vsql using the cat command.

Code: Select all

bash-3.2$ vsql
SET
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> \! cat > jim.txt
"a""b","cd"

dbadmin=> \! cat jim.txt
"a""b","cd"

dbadmin=> \! ls -lrt jim.txt
-rw-r--r-- 1 dbadmin dbadmin 12 Apr  8 09:43 jim.txt

By the way, after typing the \! cat > jim.txt command, enter the data you want in the file and press <control> <D> to stop entering data...

[khine]

Hi
May i know how to create csv file and load the data in vertica ?
Below is the data that i want to put in the csv file ,Thanks you
"Cat A Jan 2013 1st Rd :S$92100
Cat A Jan 2013 2nd Rd :S$91010
Cat A Feb 2013 1st Rd :S$87109
Cat A Feb 2013 2nd Rd :S$78301
Cat A Mar 2013 1st Rd :S$74689
Cat A Mar 2013 2nd Rd :S$64209"

[khine]

May i ask what the meaning of this following commands ?
"COPY test (
fa FILLER VARCHAR(512),
fb FILLER VARCHAR(512),
a AS TRIM(BOTH '"' FROM fa),
b AS TRIM(BOTH '"' FROM fb))
FROM '/usr/home/dbadmin/test1.txt'
DELIMITER ','
ESCAPE AS E'\001'
RECORD TERMINATOR E'"\n';"
Thanks in advance for any answer and may i know that as soon as possible .