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:
Providing results:
Force text formatting on the column by adding a preceeding = thus: