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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Am not able to export 10 lakh records to...

14 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

ravi@sql

Posted Wednesday, January 09, 2013 11:07 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:57 AM
Points: 88, Visits: 183

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

SQLRNNR

Posted Wednesday, January 09, 2013 11:20 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

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 2008

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

ravi@sql

Posted Wednesday, January 09, 2013 11:25 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:57 AM
Points: 88, Visits: 183

sql server 2008 and .xlsx file...

Post #1405189

SQLRNNR

Posted Wednesday, January 09, 2013 11:33 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

Exact number of records?

Is the spreadsheet in compatibility mode by chance?

Post #1405197

ravi@sql

Posted Wednesday, January 09, 2013 11:59 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:57 AM
Points: 88, Visits: 183

more than 11 lakh

Post #1405221

Bhuvnesh

Posted Thursday, January 10, 2013 1:19 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post Attachments

excel.png (15 views, 62.50 KB)

Post #1405245

SQLRNNR

Posted Thursday, January 10, 2013 8:40 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

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.

Post #1405504

Greg Snidow

Posted Thursday, January 10, 2013 9:03 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 1,561, Visits: 2,316

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