Unable to import a 5GB in .txt file in PowerPivot

Question

I’m using Excel 2010, and I'm trying to import a .TXT file of size 5GB

I tried importing the data in Power-pivot but it displayed a error stating limitation of 2GB

Can anyone help me to import this file excel?

Answer 1

Unfortunatley 32-bit Excel/PowerPivot can only import 2GB files, 64-bit Excel/PowerPivot can import 4GB files.

PowerPivot uses the ACE 14 OLE DB provider to import .txt .csv and .tab text files.
So it appears that text files are subject to the limitations of Access.

However if you use your 5GB file and import text file into a SQL Server table via "import data task"/SSIS package and subsequently load the SQL Server table into Powerpivot. Or you may have to split your large file into two txt files, perhaps one for each year or one for each quarter, not sure what the data look like.

MSDN Blog describing the issue you are having

Description of the Access Connectivity Engine 2010 hotfix package (Ace-x-none.msp): April 24, 2012 This hotfix warns you now when a large file successfully imports but cuts off the data over 4GB.

Answer 2

According to this Blogpost the Limitations are dependant on your Excel Version

• so The 32 Bit version can only address 2GB
• The 64 bit version is limited to 4Gb if its Excel 2010
• in Excel 2013 that limitation has been removed

so you will need x64 Excel 2013 (and enough RAM) to import a File of that Size into Excel