copy table into file trim squeeze

#1

Hi

I want to export a table to a comma delimited file.

First I thought select a, b, c from t where ...

but this will delimit by '|' and will have fixed width columns.

This is my table
create table t(c char (20), c2 int, c3 varchar(50));
\p\g
insert into t values('asdf', 1, 'asdf ');
insert into t values('asdf', 2, 'asdf aa ');
insert into t values('asdf', 3, 'asdf bb ');
insert into t values('asdf', 4, 'asdf cc ');
insert into t values('asdf', 5, 'asdf dd ');
insert into t values('asdf', 6, 'asdf ee ');
insert into t values('asdf', 7, 'asdf ff ');
insert into t values('asdf', 8, 'asdf gg ');

And I want to see an output file like:
asdf,1,asdf<nl>
asdf,2,asdf aa<nl>
etc
ie all trailing spaces removed.

So next I tried
declare global temporary table tt as select squeeze(c), squeeze(c2),
squeeze(c3)

from t on commit preserve rows with norecovery
\p\g

(Note the use of "squeeze")

copy session.tt(col1=c0comma,
col2=c0comma,
col3=c0nl) into 'b.dat'
\p\g

But I end up with the trailing spaces ie 20 for column c

and 50 for column c3

It seems like a reasonable thing for the copy command to support ?

Thanks.

#2

Where you have *c0* in your COPY...INTO statement, try *text(0)*. e.g.:
Code:
--------------------
copy session.tt(col1=text(0)comma,
col2=text(0)comma,
col3=text(0)nl) into 'b.dat'
\p\g
--------------------

--
rhann

#3

Hi Seldom.

Yes, it would be a nice feature. I think the problem is the c0 or char(0)
format specifiers. They are interpreted as char which means pad with spaces
to the length of the field. I use external tools like awk and sed to trim up
the extra spaces. Try this:

declare global temporary table tt as
select trim(c1) + ',' + trim(c2) + ',' + trim(c3) + ',' as col1
from t on commit preserve rows with norecovery
\p\g
copy session.tt(col1=c0nl) into 'b.dat'
\p\g

and then
cut -f1-3 -d, b.dat > c.dat

or
FOR /F "tokens=1,2,3 delims=," %i in (b.dat) do @echo %i,%j,%k>>c.dat

Here's an example that generates a pipe delimited output with extra spaces
removed.

set SQL="%TEMP%\db_spacereport.sql"

Quote:

%SQL% echo select db='%1', t.system_use, t.number_pages, t.num_rows,
t.row_width, t.table_pagesize, t.table_name, t.table_owner, t.table_type,

f.file_name, t.storage_structure, t.overflow_pages from iitables t,
iifile_info f

Quote:

%SQL% echo where t.table_name = f.table_name and t.table_owner =
f.owner_name
%SQL% echo order by system_use, t.table_owner, t.table_name
%SQL% echo \g
%SQL% echo \q
sql -s %1 < %SQL% | awk "/|/ {gsub(\" \",\"\",$0);print $0}"

del %SQL%
set SQL=

Paul

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of seldom
isnice
Sent: Thursday, 1 September 2011 8:01 AM
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] copy table into file trim squeeze

Hi

I want to export a table to a comma delimited file.

First I thought select a, b, c from t where ...

but this will delimit by '|' and will have fixed width columns.

This is my table
create table t(c char (20), c2 int, c3 varchar(50));
\p\g
insert into t values('asdf', 1, 'asdf ');
insert into t values('asdf', 2, 'asdf aa ');
insert into t values('asdf', 3, 'asdf bb ');
insert into t values('asdf', 4, 'asdf cc ');
insert into t values('asdf', 5, 'asdf dd ');
insert into t values('asdf', 6, 'asdf ee ');
insert into t values('asdf', 7, 'asdf ff ');
insert into t values('asdf', 8, 'asdf gg ');

And I want to see an output file like:
asdf,1,asdf<nl>
asdf,2,asdf aa<nl>
etc
ie all trailing spaces removed.

So next I tried
declare global temporary table tt as select squeeze(c), squeeze(c2),
squeeze(c3)

from t on commit preserve rows with norecovery
\p\g

(Note the use of "squeeze")

copy session.tt(col1=c0comma,
col2=c0comma,
col3=c0nl) into 'b.dat'
\p\g

But I end up with the trailing spaces ie 20 for column c

and 50 for column c3

It seems like a reasonable thing for the copy command to support ?

Thanks.
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

#4

cool. Is text() an undocumented feature or are my manuals outdated?

I found the text() format removed the trailing spaces from chars but not
varchars.
Using this data:

create table t(c1 char (20), c2 int, c3 varchar(50));
\p\g
insert into t values('asdf ', 1, 'asdf ');
insert into t values(' asdf', 2, ' asdf');

copy t(
c1=text(0)comma,
c2=text(0)comma,
c3=text(0)nl) into 'b.dat' \p\g

cat -vet b.dat
asdf,1,asdf $
asdf,2, asdf$

I need to copy the data to a side table to trim the spaces.

Paul

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Ingres
Forums
Sent: Thursday, 1 September 2011 8:46 AM
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] copy table into file trim squeeze

Where you have *c0* in your COPY...INTO statement, try *text(0)*. e.g.:
Code:
--------------------
copy session.tt(col1=text(0)comma,
col2=text(0)comma,
col3=text(0)nl) into 'b.dat'
\p\g
--------------------

--
rhann

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

#5

Paul White wrote:

Quote:

cool. Is text() an undocumented feature or are my manuals outdated?

I am sure it's been there since Day 1 because the text(n) type is a
fossil dating back to the early Quelassic period.

Quote:

I found the text() format removed the trailing spaces from chars but not
varchars.

Blanks can be safely stripped from CHARs because they will always be
repadded when you load them, whereas trailing spaces on VARCHARs are
presumed to be significant so they cannot be blindly repadded. For that
reason it is wrong to want to strip spaces from VARCHAR values in the
data file in the first place.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

#6

Roy Hann wrote:

Quote:

Paul White wrote:

cool. Is text() an undocumented feature or are my manuals outdated?

I am sure it's been there since Day 1 because the text(n) type is a
fossil dating back to the early Quelassic period.

PS: I should add that I had forgotten about the TEXT(n) format for COPY
until a couple of years ago when Karl rediscovered it by reading the
code and reminded me of it.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

#7

Assumed Ingres 10 is being used, '-S' (upper case) option can be used.
See for detail:
http://tinyurl.com/4xc229m

################################ traditional output
echo "select 'Hello', 'World'\g"|sql iidbdb
INGRES TERMINAL MONITOR Copyright 2010 Ingres Corporation

┌──────┬──────┐
│col1 │col2 │
├──────┼──────┤
│Hello │World │
└──────┴──────┘
(1 row)

################################ '-S' output
unix prompt> echo "select 'Hello', 'World'\g"|sql -S iidbdb
col1 col2
Hello World

################################ '-S' & separator example with ','
output
unix prompt> echo "select 'Hello', 'World'\g"|sql -S -v',' iidbdb
col1 ,col2
Hello ,World

Thanks - Bilgihan

--
bilgihan

#8

Hi All,

text() is described in the Ingres10 SQL Reference guide. IIRC it was not described in earlier guides which was a documentation bug.

If your using Ingres10 you should also look at Karls csv and ssv delimiters.

Marty

-----Original Message-----
From: Roy Hann [mailto:specially (AT) processed (DOT) almost.meat]
Sent: 01 September 2011 08:36
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] copy table into file trim squeeze

Roy Hann wrote:

Quote:

Paul White wrote:

cool. Is text() an undocumented feature or are my manuals outdated?

I am sure it's been there since Day 1 because the text(n) type is a
fossil dating back to the early Quelassic period.

PS: I should add that I had forgotten about the TEXT(n) format for COPY
until a couple of years ago when Karl rediscovered it by reading the
code and reminded me of it.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

#9

Hmm.

It's useful to know about tricks with *sql* and the -S and -v flags
(and \trim for that matter) because you don't always want the data for a
database, but I certainly would not recommend learning or using them
before mastering COPY...INTO. COPY...INTO is quite carefully designed to
prevent problems and you subvert it at your peril.

--
rhann

#10

Try this:

copy session.tt(col1=vchar(0)comma,
col2=vchar(0)comma,
col3=vchar(0)nl) into 'b.dat'
\p\g

The vchar specification trims white space off both char and vchar
fields. It's very useful.
I don't think it's documented however.