2

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?

YowE3K
  • 23,852
  • 7
  • 26
  • 40
askm
  • 195
  • 1
  • 7
  • 19
  • 1
    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 May 30 '17 at 11:25
  • 2
    "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 May 30 '17 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 May 30 '17 at 11:30
  • Access doesn't not have the 1M row limitation. Have you installed Access on the machine running the SSIS package? See https://social.msdn.microsoft.com/Forums/en-US/f11b2df9-fd0a-4528-987f-f95dfdccee0a/microsoftaceoledb120-provider-is-not-registered-on-the-local-machine-error – Dan Guzman May 30 '17 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 May 30 '17 at 13:00
  • @jkpieterse I tried Pivot Table, but it does not work. when i click on the desired column, the next message appear"A field in your source data has more unique items than can be used in a PivotTable. Microsoft Excel may not be able to create the report, or may create the report without the data from this field." – askm May 31 '17 at 11:23
  • Sounds like you have a file in xls format. Trys this in a file with xlsx fileformat. – jkpieterse May 31 '17 at 11:29

1 Answers1

4

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

Rachel Ambler
  • 1,440
  • 12
  • 23