Page 1 of 1
Remove column header info. in out file
Posted: Mon Jun 11, 2012 12:52 pm
by Jbaskin
Hi all, I hope everyone had a nice weekend.
Can someone help me figure out how to not include header info. in an out file when running a select statement via the vsql command line?
For instance, in the following example I don't want the column header information to be included in the out file. I just want the two records returned from the select...
Code: Select all
vert1$ vsql -U dbadmin -c "select * from no_out" -o /usr/home/dbadmin/test.txt
vert1$ cat /usr/home/dbadmin/test.txt
num | text
-----+-------------------------------------------------------------------
1 | I just want this data
2 | I just want this data too, not the column header in the out file!
(2 rows)
Thanks!
Re: Remove column header info. in out file
Posted: Mon Jun 11, 2012 2:13 pm
by id10t
Hi Jbaskin!
Meta-command: "-At"
- -A --no-align switches to unaligned output mode. (The default output mode is aligned.)
- -t --tuples-only disables printing of column names, result row count footers, and so on. This is equivalent to the \t command.
- -F separator --field-separator separator specifies the field separator for unaligned output (default: "|") (-P fieldsep=). (See -A --no-align.) This is equivalent to \pset fieldsep or \f.
Code: Select all
$ vsql -c "select * from BackUpTable limit 2";
id | val
--------------------+-----
769262974800598314 | ebc
943701414829961214 | abc
(2 rows)
Code: Select all
$ vsql -A -c "select * from BackUpTable limit 2";
id|val
769262974800598314|ebc
943701414829961214|abc
(2 rows)
Code: Select all
$ vsql -At -c "select * from BackUpTable limit 2";
769262974800598314|ebc
943701414829961214|abc
Code: Select all
$ vsql -At -F '===' -c "select * from BackUpTable limit 2";
769262974800598314===ebc
943701414829961214===abc
Re: Remove column header info. in out file
Posted: Tue Jun 12, 2012 8:46 am
by rajasekhart
Daniel,
Thats a very good explanation from you about file generation without headers.
Regarding this im having a small doubt.
Once if i have generated a file with such field separator ('===') , could i load the generated file into a table using copy command (placing delimiter as '===' ) ?
In copy command , we have to specify the delimiter as '===' in order to load the file which we have generated.
Eg: copy tablename from '/home/dbadmin/filename.txt' delimiter '===' exceptions '/home/dbadmin/filename.txt' rejected data '/home/dbadmin/filename.txt' ;
When i tried it, it is showing an error, that the delimiter should contain only a single character instead of three('===')
1.Do we have any chance to allow delimiter to have more than a single character?
2. And what is the meaning of "Escape As" in copy command? what is its significance? can u plz explain me with an example..
Thanks,
Raj
Re: Remove column header info. in out file
Posted: Tue Jun 12, 2012 12:00 pm
by id10t
Hi rajasekhart !
1.Do we have any chance to allow delimiter to have more than a single character?
Example for FILLER:
- Table
Code: Select all
CREATE TABLE public.DelimiterFillerExample
(
col1 varchar(80),
col2 varchar(80),
col3 varchar(80)
);
- DATA:
Code: Select all
value1===value2===foo
foo===bar===baz
egg===spam===val3
- Copy command
Code: Select all
test_db=> copy DelimiterFillerExample (f filler varchar(64000), col1 as SPLIT_PART(f,'===',1), col2 as SPLIT_PART(f,'===',2), col3 as SPLIT_PART(f,'===',3)) from stdin;
Code: Select all
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> value1===value2===foo
>> foo===bar===baz
>> egg===spam===val3
>> \.
- Output:
Code: Select all
test_db=> select * from DelimiterFillerExample;
col1 | col2 | col3
--------+--------+------
value1 | value2 | foo
foo | bar | baz
egg | spam | val3
(3 rows)
2. And what is the meaning of "Escape As" in copy command? what is its significance? can u plz explain me with an example..
If you have data that contains char sequences like '\n','\t' or '\r' and you want to load as is. How to do it?
Example:
- Data:
Code: Select all
'1',foo
'2','bar'
3,'baz'
4,egg
'5',\n\r
6,\t\v
___
data mixed - some values enclosed by `'` some not but ENCLOSED BY solves it like a charm
- Table:
Code: Select all
CREATE TABLE public.EscapeExample
(
id int,
val varchar(10)
);
- Copy command:
Code: Select all
test_db=> copy EscapeExample from stdin delimiter ',' direct ENCLOSED BY E'\'' ESCAPE AS E'\001';
Code: Select all
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> '1',foo
>> '2','bar'
>> 4,egg
>> 3,'baz'
>> '5',\n\r
>> 6,\t\v
>> \.
- Output:
Code: Select all
test_db=> select * from EscapeExample;
id | val
----+--------
1 | foo
2 | bar
4 | egg
5 | \n\r
6 | \t\v
(5 rows)
---
compare data with output.
Re: Remove column header info. in out file
Posted: Tue Jun 12, 2012 12:03 pm
by id10t
PS
Loading UTF-8 Format Data
Also you can use linux utilities and copy from STDIN or PIPE:
- Table:
Code: Select all
CREATE TABLE public.PipeExample
(
col1 char(3),
col2 char(3),
col3 char(3)
);
- Data:
Code: Select all
dbadmin@suse:~> cat > pipe_example.data
foo===bar===baz
egg===qux===tux
etc===bin===var
dbadmin@suse:~> sed 's/===/|/g' pipe_example.data
foo|bar|baz
egg|qux|tux
etc|bin|var
- Load data
Code: Select all
dbadmin@suse:~> sed 's/===/|/g' pipe_example.data | /opt/vertica/bin/vsql -c "copy PipeExample from STDIN direct delimiter '|'"
- Output:
Code: Select all
dbadmin@suse:~> /opt/vertica/bin/vsql -c "select * from PipeExample"
col1 | col2 | col3
------+------+------
egg | qux | tux
etc | bin | var
foo | bar | baz
(3 rows)
Re: Remove column header info. in out file
Posted: Sat Jun 16, 2012 12:33 pm
by Jbaskin
As always, thanks for the great help on this guys!
Re: Remove column header info. in out file
Posted: Thu Feb 07, 2013 5:22 pm
by vissu220985
In Vertica 6 do this (I don't know if this is available in earlier versions):
1. \pset tuples_only
2. \o file_path/file_name.sql
3. select statement
4. \o
5. \i file_path/file_name.sql