Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Am not able to export 10 lakh records to excel sheet. Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 11:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 26, 2014 6:02 AM
Points: 93, Visits: 195

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 :)
Post #1405185
Posted Wednesday, January 9, 2013 11:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 21,749, Visits: 15,444
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1405188
Posted Wednesday, January 9, 2013 11:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 26, 2014 6:02 AM
Points: 93, Visits: 195
sql server 2008 and .xlsx file...
Post #1405189
Posted Wednesday, January 9, 2013 11:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 21,749, Visits: 15,444
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1405197
Posted Wednesday, January 9, 2013 11:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 26, 2014 6:02 AM
Points: 93, Visits: 195
more than 11 lakh
Post #1405221
Posted Thursday, January 10, 2013 1:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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


  Post Attachments 
excel.png (16 views, 62.50 KB)
Post #1405245
Posted Thursday, January 10, 2013 8:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 21,749, Visits: 15,444
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1405504
Posted Thursday, January 10, 2013 9:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,566, Visits: 2,392
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.
Post #1405513
Posted Thursday, January 10, 2013 9:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2014 11:45 AM
Points: 275, Visits: 863
11 lakh is over the ~1 million limit of excel.
Looks like you are out of luck.
Post #1405534
Posted Thursday, January 10, 2013 9:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 26, 2014 6:02 AM
Points: 93, Visits: 195

Hi thank you all for the support.

Finally i exported data using SSIS and it works fine .

Regards,
Ravindranath.s
Post #1405751
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse