bcp to text file issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

bcp to text file issue

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-07-30 : 11:20:53

I have a simple stored procedure that I call with a bcp command to create a tab delimited text file form the result set.


SELECT 
  a.PrID
, a.PrDescription
, a.Category
, a.Measurement
, b.Price
FROM Products a
INNER JOIN Prices  ON a.PrID = b.prID

--------


Exec Master..xp_Cmdshell 'bcp "EXEC DBNAME..spCreate_Product_File" queryout "C:\temp\export_test.txt" -T -c'

In general it works just fine, however,I have a few data sets where several extra tabs are inserted into the result file. There are no tabs in the datafields itself so I'm not sure where the extra tabs in the file are coming from.

Any ideas?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-30 : 11:33:19

Null/blank columns?

Are you sure they aren't comming from the SP?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-07-30 : 12:10:54

quote:
Originally posted by nigelrivett

Null/blank columns?
Are you sure they aren't comming from the SP?

I'm pretty sure that they don't come from the sp.
All the SP does is return the values for the 5 columns in the select statement. On several records there are 3 extra tabs added before the category column value. I thought that maybe there were tabs in the data field itself but even with ltrim(rtrim()) on that column the additional tabs are being added.

Here's a sample record with the false tabs:


05400091	SHOEBOX UA LM/BIM310 00/4Z				FOOTWEAR 	KT	101.09

This is how it should look like:


05400091	SHOEBOX UA LM/BIM310 00/4Z	FOOTWEAR 	KT	101.09

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-30 : 12:16:57

I would have a look at the binary values at the end of category column and the begining of the next column. Could be that they are something that trim doesn't get rid of but gets converted to a tab - you did trim both the columns?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-07-30 : 12:38:05

The only thing I can find at the end of the Description column is three spaces that apparently are not getting removed with LTRIM/RTRIM.
I don't understand how that would be converted into tabs, though. Is there a command to remove any leading/trailing spaces?

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-07-30 : 12:51:22

I found a function that removes all kinds of trailing characters and that did the trick:


CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '%[' + @trimchars + ']'
SET @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
RETURN @str
END

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-30 : 13:30:13

Check to see what the values are. You might want to stop them getting in to the table.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources