Search code examples
sql-serverexcelssmsexport-to-excel

SSMS :: Copy with Headers and paste to Excel results in more rows in destination

Peculiar issue: I have a table of 140.588 rows and 246,313 MB which looks like this:

CREATE TABLE [dbo].[DMA_assessment](
    [InstanceName] [varchar](128) NULL,
    [DatabaseName] [varchar](128) NULL,
    [SizeMB] [varchar](30) NULL,
    [ImpactedObjectName] [varchar](128) NULL,
    [ImpactDetail] [varchar](max) NULL,
    [AssessmentName] [varchar](128) NULL,
    [AssessmentNumber] [int] NULL,
    [SourceCompatibilityLevel] [varchar](15) NULL,
    [TargetCompatibilityLevel] [varchar](15) NULL,
    [TargetSQLServerEdition] [varchar](15) NULL,
    [Category] [varchar](50) NULL,
    [Severity] [varchar](15) NULL,
    [ChangeCategory] [varchar](30) NULL,
    [Title] [varchar](500) NULL,
    [Impact] [varchar](max) NULL,
    [Recommendation] [varchar](max) NULL,
    [MoreInfo] [varchar](max) NULL,
    [ObjectType] [varchar](40) NULL,
    [DBOwnerKey] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Click to enlarge.

enter image description here

This table was created by the DMA Tool and I want to use the output to visualize the results on Power BI. Querying a table so big is out of question. I need to export it in .csv or .xlsx file

If I do the notorious right click and "copy with Headers" and then I paste it on an Excel file the result has 141186 rows (-1 because the first rows are the columns names)

(Click again to see the details)

enter image description here

So here we are:

141186 - 140558 = 598

Where do they come from those 598 rows?

I tried multiple times, the result is still the same.

Solution

  • I guess your varchar(max) causes the problem. The size of a text field in Excel is limited. Following an example:

    DECLARE @x VARCHAR(MAX) = '***************************************************************************';
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)
SELECT @x=CONCAT(@x,@x)

SELECT @x AS Test, 'Test' AS Test2

    Copy the result to an excel file and you will get two rows, where the second column (Test2) is only featured in the second row.