Importing data from Access

[Azhar]

Can you please tell me how to import data from Access if data is around 2 million rows or even more?

 

[Hui]

Azhar


Firstly, Welcome to the Chandoo.org Forums


On a single worksheet, Excel can only hold a header row plus 1048575 rows of data

so your out of luck


The solution will depend on what you want to use the data for.


Depending on what you want to do can you import different subsets to diferent sheets, staying under the 1048575 row limit per sheet. eg: Year 1 on Sheet1, Year 2 on Sheet2 etc


You could look at using a Pivot Table, which allows processing of these quantities of data, but presenting it to Excel as a summary format with some drill down facility


You could also look at using Power Pivot, which allows processing of these quantities of data, but presenting it to Excel as a summary format with drill down facilities

 

[dan_l]

As Hui said: you can't exceed the row cap. Your best bet is to build a query and plug it directly into a pivot table. Or, you can build whatever summaries into the query....


To be honest: I use access really more or less because it lets me get around the Excel row cap.