February 4, 2003 at 2:08 pm
DTS wizard, when exporting, will place the comma field terminator at the end of the data in the field (i.e. truncate trailing spaces). The bcp comma-delimited export places the comma after the trailing spaces. Does anybody know how I can get bcp to truncate the trailing spaces for each field?
My data fields are all varchar fields, so the trailing spaces seem to be something bcp is putting in on export - as if they were fixed length fields.
Example: (dots stand for spaces)
002....,Mary Ann.......,City.........,
vs
002,MaryAnn,City,
Thanks!!
February 6, 2003 at 12:16 pm
I am unaware of a bcp command that will trim the fields. I would recommend using a view that is created like
CREATE VIEW exportMyTable AS
SELECT RTRIM( column1 ) as column1,
RTRIM( column2 ) as column2, .... etc,
You can use either this view or the Query in the bcp command. A little cumbersome, but works OK
Guarddata-
February 7, 2003 at 3:36 am
Is this a SQL2K issue. I have SQL7 SP4 and I never get trailing spaces with bcp for varchar columns.
Far away is close at hand in the images of elsewhere.
Anon.
February 7, 2003 at 9:05 am
Interesting question David. Rachel, I just assumed that the columns were defined as CHAR instead of VARCHAR. VARCHAR data will come out without trailing spaces.
Guarddata-
February 7, 2003 at 9:06 am
They are all varchar fields. Thanks.
February 7, 2003 at 9:18 am
Rachel r u still having problems. Can u post more info, SQL version, bcp command line parameters (without security info) etc. Anything would be of help.
Far away is close at hand in the images of elsewhere.
Anon.
February 10, 2003 at 9:17 am
Hi, you can create an SQL task that trims spaces from your varchar columns. The code below also pulls out any commas from the varchar field - this is useful since BCPing using a comma delimter does not do this for you.
-- Function to trim spaces and remove commas from varchar fields. This is useful when bcp'ing to
-- a comma delimited file
DECLARE cur_Column CURSOR FOR
SELECT sc.name
FROM syscolumns sc
WHERE sc.id = OBJECT_ID('MyTableName') order by colorder
DECLARE @colType int
OPEN cur_Column
FETCH NEXT FROM cur_Column into @CurrentColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @colType = sc.xtype
FROM syscolumns sc
WHERE sc.id = OBJECT_ID('MyTableName')
AND sc.name = @CurrentColumn
IF (@colType = 167)
BEGIN
-- Remove commas (varchar columns only : xtype = 167)
EXECUTE('UPDATE MyTableName SET ' + @CurrentColumn + ' = REPLACE(' + @CurrentColumn + ', '','', '''')')
-- Trim spaces
EXECUTE('UPDATE MyTableName SET ' + @CurrentColumn + ' = LTRIM(RTRIM(' + @CurrentColumn + '))')
END
FETCH NEXT FROM cur_Column into @CurrentColumn
END
CLOSE cur_Column
DEALLOCATE cur_Column
February 12, 2003 at 4:30 pm
I suppose, since bcp allows a query, all of this could be done as the query argument for the bcp command. (In case it is important to keep unchanged data in the table). It would be cumbersome with a lot of columns, but you could perform the trim as part of the query.
Guarddata-
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply