Export table in comma delimited format - how to eliminate empty spaces

  • Thread starter Thread starter Peter Afonin
  • Start date Start date
P

Peter Afonin

Hello:

When I export a table in comma delimited format from an Access project, 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

I use a TransferText method:

DoCmd.TransferText acExportDelim, , "dbo.tblStatus", _
"\\Win2000\Everyone\NewGamePayout\Test.txt"

How would I do it?

I would appreciate your help.

Thank you,
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Instead of exporting directly from the table, create a query/view as
the export object. In the query put the Trim() function (Access
function) around the columns that you want to remove extra spaces
from. In SQL'r View use RTrim(). If there are both leading &
trailing spaces in the SQL'r column use both LTrim() & RTrim(), e.g.:

LTrim(RTrim(column_name))

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQB2cKIechKqOuFEgEQLWewCdG1h0g7r7NoUXvNa+nKSKO+rRgFQAoJpk
r2TFYRXT+1D1yjo6hdalHCxs
=gBGI
-----END PGP SIGNATURE-----
 
Back
Top