1

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
0

1 Answer 1

2

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

2
  • that shows the result, but it also adds the = sign to the cell in my csv file. Is there a way to not have the equal sign exported? Nov 11, 2016 at 12:52
  • The = sign is read by Excel and interpreted as the following value being text. You are probably running into an issue with Excel 2007. The article I linked to has information on a workaround for that.
    – Nic
    Nov 11, 2016 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.