How to export huge data from sql server into excel file

2
votes

I want to export huge data(about 5 millions rows with 2 columns) from sql server management studio into excel file, but the maximum size of excel file is around 1400000 rows

i tried import and export wizard but it does not work perfectly it returns 60000 records then the error occur:

enter image description here

when i choose the destination file, i should use one of excel versions if i choose 'Microsoft Excel 2007' the message appear(the 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.(System.Date)) so i chose 'Microsoft excel 97-2003'

i want to export data to excel or access.

any suggestion?

1
sql-serverexcelms-accessssmsexport-to-excel
What are you planning to do with the data? It is probably better to open Excel and import the data into a pivot table and summarize from there. This allows a lot more than a million rows of data. - jkpieterse
"I want to export 5M records to Excel, but Excel's maximum is 1M rows, what can I do?". Well, not a lot, if you exceed the maximum there is very little we can do for you. - HoneyBadger
Let's see, "I want to pour a full pint of milk into a half pint pot".... Yeah, nothing wrong with that! - Rachel Ambler
Access doesn't not have the 1M row limitation. Have you installed Access on the machine running the SSIS package? See social.msdn.microsoft.com/Forums/en-US/… - Dan Guzman
If you happen to have Excel 2010+ then you also might use the direction connection to SQL Server, with PowerPivot/PowerQuery. If so, Excel may exceed this limit. One caveat: Excel does then not store the data, it only loads it every time you open Excel. That also means that you need enough RAM available for this amount of data. - ksauter

1 Answers

3
votes

"i want to export data to excel or access."

Therefore import it to Access. 2007 has an overall 2GB Table size limitation, but no row limitation (that I can remember).

Excel ain't gonna work for you. 2003 (and other's when running in 2003 compatability mode) have a limitation of 65,535 rows, 2007+ 1,048,576 rows.