SQL Server Forums - bcp to text file issue

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

bcp to text file issue

New Topic

Reply to Topic

Printer Friendly

Author

Topic

HenryFulmer
Yak Posting Veteran

USA
98 Posts

Posted - 07/30/2012 : 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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts

Posted - 07/30/2012 : 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
Yak Posting Veteran

USA
98 Posts

Posted - 07/30/2012 : 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

Edited by - HenryFulmer on 07/30/2012 12:28:13

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts

Posted - 07/30/2012 : 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
Yak Posting Veteran

USA
98 Posts

Posted - 07/30/2012 : 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
Yak Posting Veteran

USA
98 Posts

Posted - 07/30/2012 : 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
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts

Posted - 07/30/2012 : 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.

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.08 seconds.