Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Count mismatch when resultset copied from sql to excel

Unanswered Count mismatch when resultset copied from sql to excel

  • Friday, September 14, 2012 6:52 AM
     
     
    Sign In to Vote
    0

    Hi all,

                   I came across a strange thing just now. The thing is when i copy my result set generated by a query using the "Copy with Headers"  and then pasted into a new excel sheet, i see there is count difference. I selected on primary column and it shows me 50 less than actual number. how can this be possible ?

    Note: There are no duplications, all are unique values.

    Please help me in solving this issue as i need to send an excel sheet to my manager.

    Any help woul dbe really appreciated

    Regards

    Chaithanya M

    Regards Chaithu.. If it is usefull,Mark this as Answer.

     

All Replies

  • Friday, September 14, 2012 7:06 AM
     
     
    Sign In to Vote
    0

    I am damn sure that the count should be the same.

    To verify that just check what count(*) of your select query returns and then check the count of the no. of rows in the excel sheet.

    Murali Krishnan

     
  • Friday, September 14, 2012 7:11 AM
     
     
    Sign In to Vote
    0
    Hi Chaithanya, Are you copying a huge data? If am not wrong, I guess this is a known issue. Let me try to pull few of ocurences if possible.

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

     
  • Friday, September 14, 2012 7:12 AM
     
     
    Sign In to Vote
    0
    Excel I guess would not count the rows that are blank. Check if you have any blank values in the data you pasted to excel. Thanks!
     
  • Friday, September 14, 2012 7:14 AM
     
     
    Sign In to Vote
    0

    Hi Murali,

                         i did couple of exercises to trace out this, but couldnt. One exercise that i did is , i just saved the resultset  using "Save Result As" option in the output window. when i go back and looked into the excel sheet , i still see the count difference. i  wonder how ?

    Regards

    Chaithanya M

    Regards Chaithu.. If it is usefull,Mark this as Answer.

     
  • Friday, September 14, 2012 7:16 AM
     
     
    Sign In to Vote
    0

    Hi deepak,

                            there are on blank values as the column values which i am trying to copy has a primary key constraint defined on it .

    Regards

    Chaithnaya M

    Regards Chaithu.. If it is usefull,Mark this as Answer.

     
  • Friday, September 14, 2012 7:18 AM
     
     
    Sign In to Vote
    0

    Hi deepak,

                            there are on blank values as the column values which i am trying to copy has a primary key constraint defined on it .

    Regards

    Chaithnaya M

    Regards Chaithu.. If it is usefull,Mark this as Answer.


    Which excel (office) version you are using. I guess you have limitation of number of rows that can be used per sheet in Excel. Check if the rows being copied crosses the allowed limit. Thanks!
     
  • Friday, September 14, 2012 7:19 AM
     
     
    Sign In to Vote
    0

    Have a look at the below link:

    http://www.sqlmag.com/forums/aft/96701

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

     
  • Friday, September 14, 2012 7:19 AM
     
     
    Sign In to Vote
    0

    Hi Latheesh,

                                 I am just copying 7550 rows. I see the count as 7413. I dont know for what reason.Interesting thing is the first and lastvalue are same.

    Regards

    Chaithanya M

    Regards Chaithu.. If it is usefull,Mark this as Answer.

     
  • Friday, September 14, 2012 7:22 AM
     
     
    Sign In to Vote
    0

    i am using excel 2010. and row count is just 7550

    Regards Chaithu.. If it is usefull,Mark this as Answer.

     
  • Friday, September 14, 2012 7:23 AM
     
     
    Sign In to Vote
    0

    Chaithanya, Can you try Export Data wizard as a workaround and see the results are matching?

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

     
  • Friday, September 14, 2012 7:37 AM
     
      Has Code
    Sign In to Vote
    0

    Hi Chaithanya,

    Please check, if any column data contains character ". If it is present then replace it with '" and then copy data from result set to excel with or without header.

    The below result set copy only 4421 rows to excel

    WITH numbers AS 
( 
  SELECT 0 AS Number 
  UNION ALL 
  SELECT Number + 1 
  FROM Numbers 
  WHERE Number + 1 <= 7550
) 
SELECT Number, CHAR(Number) AS Character FROM numbers 
OPTION (MAXRECURSION 7550);

    while below copies 7551 rows (all data)

    WITH numbers AS 
( 
  SELECT 0 AS Number 
  UNION ALL 
  SELECT Number + 1 
  FROM Numbers 
  WHERE Number + 1 <= 7550
) 
SELECT Number, replace(CHAR(Number),'"','""') AS Character FROM numbers 
OPTION (MAXRECURSION 7550);

    Thanks, Sachin Surve



    • Edited by S_Surve Friday, September 14, 2012 7:38 AM
    • Edited by S_Surve Friday, September 14, 2012 7:39 AM
    •  
     
  • Friday, September 14, 2012 12:11 PM
     
     
    Sign In to Vote
    0

    Hi surve,

                      I did the excercise, that doesnt seems to really true . Could you please re-check the same

    Regards

    Chaithanya M

    Regards Chaithu.. If it is usefull,Mark this as Answer.