January 9, 2013 at 11:07 pm
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 🙂
January 9, 2013 at 11:20 pm
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
January 9, 2013 at 11:25 pm
sql server 2008 and .xlsx file...
January 9, 2013 at 11:33 pm
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
January 9, 2013 at 11:59 pm
more than 11 lakh
January 10, 2013 at 1:19 am
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;-)
January 10, 2013 at 8:40 am
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
January 10, 2013 at 9:03 am
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.
January 10, 2013 at 9:57 am
11 lakh is over the ~1 million limit of excel.
Looks like you are out of luck.
January 10, 2013 at 9:30 pm
Hi thank you all for the support.
Finally i exported data using SSIS and it works fine .
Regards,
Ravindranath.s
January 10, 2013 at 10:19 pm
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
January 10, 2013 at 11:08 pm
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.
-----------------------------------------------------------------------------
संकेत कोकणे
January 10, 2013 at 11:15 pm
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:
January 10, 2013 at 11:29 pm
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;-)
September 23, 2015 at 6:27 am
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