Sql-server – BCP To Show Full Text Values

bcpsql serversql-server-2008-r2t-sql

I have a long varchar field that I am exporting to a csv with BCP. The export goes as it should, however in the actual cell itself the csv file shows X.XXXXXX+13 instead of XXXXXXXXXXXXXX

Am I missing a Cast() or BCP switch that could force the csv file to show XXXXXXXXXXXXXX? Or since it is BCP this is the output you get?

Declare @sql nvarchar(max), @filename varchar(max)
Set @filename = 'C:\Test.csv'
Create Table #test (field1 varchar(100))
Insert Into #test (field1) Values
('XXXXXXXXXXXXXX')
Select @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM #Test''" queryout "'
SET @sql = @sql + @filename + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql

Best Answer

This works fine for me:

CREATE TABLE test ( field1 VARCHAR(100) );
INSERT  INTO test
        ( field1 )
VALUES  ( 'XXXXXXXXXXXXXX' );

DECLARE @sql VARCHAR(4000) ,
    @filename VARCHAR(4000);
SET @filename = 'C:\temp\Test.csv';
SELECT  @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM Test" queryout "';
SET @sql = @sql + @filename + '" -c -t, -T -S ' + @@SERVERNAME;
PRINT @sql;
EXEC master..xp_cmdshell @sql;

Providing results:

field1
"XXXXXXXXXXXXXX"

Force text formatting on the column by adding a preceeding = thus:

CREATE TABLE test ( field1 VARCHAR(100) );
INSERT  INTO test
        ( field1 )
VALUES  ( 'XXXXXXXXXXXXXX' );

DECLARE @sql VARCHAR(4000) ,
    @filename VARCHAR(4000);
SET @filename = 'C:\temp\Test.csv';
SELECT  @sql = 'bcp "SELECT ''field1'' UNION ALL SELECT ''=''+CHAR(34)+CAST([field1] AS VARCHAR(MAX))+CHAR(34) FROM Test" queryout "';
SET @sql = @sql + @filename + '" -c -t, -T -S ' + @@SERVERNAME;
PRINT @sql;
EXEC master..xp_cmdshell @sql;

Excel data