Hello:
When I export a table in comma delimited format, it works OK, but there is
one problem. If the field is varchar (or char) and has the size, for
instance, 50, there are empty spaces in the text file, so it looks like
this:
"CBAS ","Bass Gambling Supplies ",2
"CBW ","Ben's Wholesale ",3
"CTDI ","3 Diamond ",4
"NONE ","NON-CUSTOM ",1
I need to elimitate these free spaces somehow, so the text file would look
like this:
"CBAS","Bass Gambling Supplies",2
"CBW","Ben's Wholesale",3
"CTDI","3 Diamond",4
"NONE","NON-CUSTOM",1
How would I do it, either manually or programmatically?
I would appreciate your help.
Thank you,
--
Peter AfoninDid you try RTRIM() to remove the trailing spaces from the Stirngs
--
HTH
Satish Balusa
Corillian Corp.
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:efJOp115DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hello:
> When I export a table in comma delimited format, it works OK, but there is
> one problem. If the field is varchar (or char) and has the size, for
> instance, 50, there are empty spaces in the text file, so it looks like
> this:
> "CBAS ","Bass Gambling Supplies ",2
> "CBW ","Ben's Wholesale ",3
> "CTDI ","3 Diamond ",4
> "NONE ","NON-CUSTOM ",1
> I need to elimitate these free spaces somehow, so the text file would look
> like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
> I would appreciate your help.
> Thank you,
> --
> Peter Afonin
>|||Peter,
> When I export a table in comma delimited format, it works OK, but
> there is one problem. If the field is varchar (or char) and has
> the size, for instance, 50, there are empty spaces in the text
> file, so it looks like this:
> I need to elimitate these free spaces somehow, so the text file
> would look like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
You need to use a format file for this. Using the pubs..authors
tables
as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote that
just
returns NULL. It's just a little trick to get the leading quote on
the first
column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
Linda|||Thank you everyone for your help, I'll try this.
Peter
"lindawie" <lindawie@.my-deja.com> wrote in message
news:#f4ibO25DHA.488@.TK2MSFTNGP12.phx.gbl...
> Peter,
> > When I export a table in comma delimited format, it works OK, but
> > there is one problem. If the field is varchar (or char) and has
> > the size, for instance, 50, there are empty spaces in the text
> > file, so it looks like this:
> >
> > I need to elimitate these free spaces somehow, so the text file
> > would look like this:
> >
> > "CBAS","Bass Gambling Supplies",2
> > "CBW","Ben's Wholesale",3
> > "CTDI","3 Diamond",4
> > "NONE","NON-CUSTOM",1
> >
> > How would I do it, either manually or programmatically?
> You need to use a format file for this. Using the pubs..authors
> tables
> as an example, we'll bcp out of a view that looks like this:
> use pubs
> go
> create view authors_csv as
> select null first_quote, * from authors
>
> Note that we are including a dummy column called first_quote that
> just
> returns NULL. It's just a little trick to get the leading quote on
> the first
> column.
> The format file looks like this:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 first_quote ""
> 2 SQLCHAR 0 11 "\",\"" 2 au_id ""
> 3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
> 4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
> 5 SQLCHAR 0 12 "\",\"" 5 phone ""
> 6 SQLCHAR 0 40 "\",\"" 6 address ""
> 7 SQLCHAR 0 20 "\",\"" 7 city ""
> 8 SQLCHAR 0 2 "\",\"" 8 state ""
> 9 SQLCHAR 0 5 "\",\"" 9 zip ""
> 10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
> That dummy column is also in the format file to get the leading
> quote on au_id.
> Here's the command line:
> bcp pubs..authors_csv out
> authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
> Linda
>
Chris Baldwin - MSFT wrote:
Chris Baldwin - MSFT wrote: