Am not able to export 10 lakh records to excel sheet.

  • Hi all,

    Am not able to export more than 10 lakh records to excel sheet(2007 xlsx). I'll get sucess message but all data will not get copy to excel sheet .

    I have tried through import wizard in sql server and also directly copy and paste to excel sheet.

    Apart from doing through ssis package i need help to perform this task.

    Regards,

    Ravi 🙂

  • What version of SQL Server? What is the file extension of excel you are using (xls limits to ~65000 records where xlsx limits to ~1,000,000).

    If you have more than 1 million records, then the import to excel will not work properly.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • sql server 2008 and .xlsx file...

  • Exact number of records?

    Is the spreadsheet in compatibility mode by chance?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • more than 11 lakh

  • why so much of data on excel .it would be difficult to analyze it there (difficult to slice and dice) or even to scroll.

    from excel too , you can do this . see ther attachment

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • with no errors and not being in compat mode in excel - it sounds like you may have bad data that is getting discarded or you are exceeding the limits in excel 2007.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • ravi@sql (1/9/2013)I'll get sucess message but all data will not get copy to excel sheet .

    Ravi, when you say "all data will not get copy", do you mean the rows that do get copied are *exactly* as they should be, but that some rows are missing? Or, are some of the rows that do get copied messed up? Did you, by any chance, import the data from some kind of text file? I had this issue one time when I imported some data from a text file as all varchar columns, which worked fine, but then I could not export it, keeping the columns as varchar. Then, I tried to re-import and convert to target datatype via the wizard. I found there were some non-printing characters in the data that I could not see. You could try (and it will be a pain), exporting the entire table to a text file, then re-importing with conversion. Set the wizard to fail on error, then look at the first record that causes a conversion failure. Maybe this is not an issue, but if you have any lf, cr, etc characters in your table, it could cause an issue.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • 11 lakh is over the ~1 million limit of excel.

    Looks like you are out of luck.

  • Hi thank you all for the support.

    Finally i exported data using SSIS and it works fine .

    Regards,

    Ravindranath.s

  • That is good to hear.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • ravi@sql (1/10/2013)


    Hi thank you all for the support.

    Finally i exported data using SSIS and it works fine .

    Regards,

    Ravindranath.s

    can u please explain how ?

    because for excel 2007 ,row limit is 10,00,000 and you mentioned that there are 11,00,000 + records in table.

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • No actually i exported 3 lakh records with around 150 columns dude , i splitted the records based on source using query . for that only it was taking around 1.5 hour to export.:crying:

  • ravi@sql (1/10/2013)


    No actually i exported 3 lakh records with around 150 columns dude , i splitted the records based on source using query . for that only it was taking around 1.5 hour to export.:crying:

    it will/should as data transfer rate will be dependent on certain factor like cpu, memory and IO.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • HI Ravi,

    Any idea. Need to export around 1 million.

    Thanks,

    Rajesh

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply