How to export huge data from sql server into excel file

Question

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:

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?

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
– jkpieterse, Commented May 30, 2017 at 11:25
"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
– HoneyBadger, Commented May 30, 2017 at 11:26
Let's see, "I want to pour a full pint of milk into a half pint pot".... Yeah, nothing wrong with that! — Rachel Ambler
– Rachel Ambler, Commented May 30, 2017 at 11:30
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
– Dan Guzman, Commented May 30, 2017 at 11:37
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
– ksauter, Commented May 30, 2017 at 13:00

Rachel Ambler · Accepted Answer · 2017-05-30 11:33:53Z

6

"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.

answered May 30, 2017 at 11:33

Rachel Ambler

1,61413 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to export huge data from sql server into excel file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related