On 09/06/2014 10:34 AM, Abelard Hoffman wrote:
On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
On 09/06/2014 12:32 AM, Abelard Hoffman wrote:
[snip]
So, my question is, what's the simplest way to generate tab-escaped
TSV-formatted reports with the first line containing the list of
column
names?
create table tsv_test (id int, fld_1 varchar);
insert into tsv_test values (1, 'test value');
insert into tsv_test values (2, 'test value');
insert into tsv_test values (3, 'test value');
\copy tsv_test to 'data.tsv' with csv header delimiter ' ';
aklaver@panda:~> cat data.tsv
id fld_1
1 "test value"
2 "test value"
3 "test value"
Thanks, Adrian. That works, but since we're using quotes to embed the
delimiter, we lose the simplicity of TSV. I can't just do a split on
/\t/ to get the fields and then unescape the values. At that point it's
probably simpler to just switch to standard CSV.
Using your example, the output I'd prefer is:
id fld_1
1 test\tvalue
2 test\tvalue
3 test\tvalue
I guess it depends on what you are using.
In Python:
In [30]: with open('data.tsv', 'rb') as c_file:
c_reader = csv.reader(c_file, delimiter='\t', quotechar = '"')
for row in c_reader:
print row
....:
['id', 'fld_1']
['1', 'test\tvalue']
['2', 'test\tvalue']
['3', 'test\tvalue']
In [33]: with open('data.tsv', 'rb') as c_file:
c_reader = csv.reader(c_file, delimiter='\t', quotechar = '"')
for row in c_reader:
print row[1]
....:
fld_1
test value
test value
test value
The Postgres docs have a good note on the CSV format:
http://www.postgresql.org/docs/9.3/interactive/sql-copy.html
"Note: Many programs produce strange and occasionally perverse CSV
files, so the file format is more a convention than a standard. Thus you
might encounter some files that cannot be imported using this mechanism,
and COPY might produce files that other programs cannot process."
So it always an adventure:)
I looked at the options for COPY's CSV format, but I don't see a way to
disable quoting but still have escaping.
This works, although it's not exactly simple:
DROP TABLE IF EXISTS tsv_test;
CREATE TABLE tsv_test (id int, fld_1 varchar);
INSERT INTO tsv_test VALUES (1, 'test value');
INSERT INTO tsv_test VALUES (2, 'test value');
INSERT INTO tsv_test VALUES (3, 'test value');
SELECT * FROM tsv_test WHERE FALSE; -- to generate header row
COPY tsv_test TO STDOUT;
And then run that through psql with the --no-align --field-separator
'\t' --pset footer=off options.
With that, I'd probably generate the report into a temp table, and then
run the above to actually export that table as TSV.
@Thomas, yes, I was hoping to stick with just psql, but I'll look at
other tools if necessary.
Any other ideas?
Thanks.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
