SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ravi@sql
ravi@sql
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 349
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 Smile
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63843 Visits: 18570
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

ravi@sql
ravi@sql
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 349
sql server 2008 and .xlsx file...
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63843 Visits: 18570
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

ravi@sql
ravi@sql
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 349
more than 11 lakh
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12576 Visits: 4077
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;-)
Attachments
excel.png (100 views, 62.00 KB)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63843 Visits: 18570
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

Greg Snidow
Greg Snidow
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4171 Visits: 2494
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.
arnipetursson
arnipetursson
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 1019
11 lakh is over the ~1 million limit of excel.
Looks like you are out of luck.
ravi@sql
ravi@sql
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 349
Hi thank you all for the support.

Finally i exported data using SSIS and it works fine .

Regards,
Ravindranath.s
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search