Search code examples
sqlsql-servert-sqlreporting

SSMS - Export long data into excel file without truncate data

I have a script that I wanna to export in an xls file.

Due to one of the fields is a product description, it has commas, csv file is not a good option.

I tried using DTS Wizard, but the descrpition field is nvarchar(8000) and DTS fails when tried to convert to varchar(255) or longtext.

the query result has 100k+ rows.

I tried to send the result to a .rpt file but the description field is truncated as well.

I tried to send the result to a .csv file but the description field is truncated as well.

I tried to do an OPENROWSET command in TSQL, but OLE DB provider throws an error.

Is there a way to get an excel result with the fields, without truncate any data, and without parsing a lot the fields (e.g. delete commas, add ", changing table data structure..) ?

Solution 
  • declare @v1 varchar(2000);
set @v1 = replicate('ab',1000)
insert into i3 values (@v1)
select  * from i3

    save it as a csv file. (Right click on the results and save as CSV). When you open the csv file in excel I get the entire value.

    You can also use BCP to export data from SQL Server to an CSV.

    You can also use SSIS

    You can use (in SSMS) by selecting the database you are exporting from in the Object Explorer window and right clicking choosing Tasks --> Export Data and follow the tabs.