none
What is the maximum file size for CSV that Excel can open ? (Excel 2013 64bit)

    Question

  • Hello,

    Before anyone jumps in, I am not talking about the maximum worksheet size of 1048576 rows by 16384 columns.

    I have  client whom has a 1.5 Gb CSV file, 1.9, 2.6, 5, 17 and 89 Gb file (Huge).

    If I open the 1.5 Gb, the file opens (After waiting 5 minutes) and then a warning pops up that only the first 1048576 rows have loaded. That is fair enough.

    If I try and open any of the others, Excel comes up to a blank worksheet. No errors. It just seems to ignore the file I tried to open. This happens from within Excel (File - open) or from double clicking the file in explorer.

    Excel goes to this blank page almost imeadiatly. It does not even try to open the file.

    If I try with Ms Access, I get a size warning and it refuses to load the file. (At least I get a warning)

    I would have expected Excel to load at least the first 1048576 rows  (If that is what there are in the file), and give an error.

    The computer is more than capable (Xeon processors, 16 Gb ram, SSD hard disks top of the line HP Z820 power workstation).

    With the 1.5 Gb file loaded to 1048576 rows, it uses 15% ram/pagefile. CPU's hit about 5%.

    I have confirmed it is Win 7 64bit, Excel 64bit. I am fairly confident we are over the file size but without an error message, I don't know what to tell my client whom is looking to me for answers.

    I have already discussed that the 89gb file in Excel is unreasonable and they are looking at a stat's package but I need an answer on these smaller files.

    Anyone got any ides ?


    Michael Jenkin (Mickyj) www.mickyj.com (Community website) - SBS MVP (2004 - 2008) *5 times Microsoft MVP award winner *Previously MacWorld Australia contributer *Previously APAC Vice Chairman Culminis (Pro IT User group support system)* APAC chairman GITCA *Director Business Technology Partners, Microsoft Small Business Specialist, SMB150 2012 Member

    Tuesday, July 29, 2014 9:46 AM

Answers

All replies

  • Have you tried:

    1) Using VBA to read the file line by line, parsing it out as it is read in?

    2) Power Pivot Add-in

    http://office.microsoft.com/en-us/excel-help/power-pivot-add-in-HA101811050.aspx

    (the article was written for 2010 by applies to 2013) 

    • Load even the largest data sets from virtually any source including databases, data feeds, Reporting Services, and text files
    Tuesday, July 29, 2014 2:07 PM
  • Hi,

    The 1,048,576 rows & 16,384 columns is the workbook size limitation in Excel 2013. Thus, I recommend we try the Mr. Bernie's suggestions to import the large CSV file.

    1. Use VBA to read the file line by line and split/examine the import file in sections. If you have further question about the VBA, please post your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    2. Use Excel 2013 add-ins. Power Pivot and Power Query. For more detailed information, please see the below articles: 

    http://social.technet.microsoft.com/Forums/en-US/9243a533-4575-4fd6-b93a-4b95d21d9b10/table-with-more-than-1-048-576-rows-in-power-query-excel-2013?fo

    http://www.microsofttrends.com/2014/02/09/how-much-data-can-powerpivot-really-manage-how-about-122-million-records/

    Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

    Thanks

    George Zhao
    Forum Support
    ________________________________________
    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please click "tnfsl@microsoft.com"

    Wednesday, July 30, 2014 2:10 AM
    Moderator
  • I think your option number 2 is a good idea.

    I assume that a CSV about 2 Gb or larger will simply not load up into Excel 2013 and the fact I get no error message and a new sheet appears is simply an Excel design fault. I beleive it should come up with an error, but does not.

    That is why I would like to know what the max size csv file (or any file) can be, that is opened with Excel.

    If I have a figure then I can turn to the client, without having to mess about and prove it, and tell them that is is a fact that the file is too large.

    Is there a 2 Gb limit in Excel 2013 64bit   ?


    Michael Jenkin (Mickyj) www.mickyj.com (Community website) - SBS MVP (2004 - 2008) *5 times Microsoft MVP award winner *Previously MacWorld Australia contributer *Previously APAC Vice Chairman Culminis (Pro IT User group support system)* APAC chairman GITCA *Director Business Technology Partners, Microsoft Small Business Specialist, SMB150 2012 Member

    Wednesday, July 30, 2014 4:26 AM
  • yes, I know it is a limit. The problem is, as I can't load the file, I don't know I am hitting that limit. As I get no errors and Excel almost imeadiatly shows me an empty blank worksheet, I expect that there is meant to be a popup warning "file too big" that I am not getting.


    Michael Jenkin (Mickyj) www.mickyj.com (Community website) - SBS MVP (2004 - 2008) *5 times Microsoft MVP award winner *Previously MacWorld Australia contributer *Previously APAC Vice Chairman Culminis (Pro IT User group support system)* APAC chairman GITCA *Director Business Technology Partners, Microsoft Small Business Specialist, SMB150 2012 Member

    Wednesday, July 30, 2014 4:27 AM
  • Hi,

    As I known, the PowerPivot is Supported Data Sources that it uses the ACE 14 OLE DB provider for Microsoft Access to import .txt .csv and .tab text files. So it appears that CSV files are subject to the limitations of Access (Limited 2GB).

    http://office.microsoft.com/en-us/excel-help/get-data-using-the-power-pivot-add-in-HA102836921.aspx

    We'd better split the data source file.

    Thanks

    George Zhao
    Forum Support
    ________________________________________
    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please click "tnfsl@microsoft.com"

    Thursday, July 31, 2014 1:14 AM
    Moderator
  • Hi,

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

    Thanks

    George Zhao
    Forum Support
    ________________________________________
    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please click "tnfsl@microsoft.com"

    Monday, August 4, 2014 1:10 AM
    Moderator
  • Hi, 
    I'm marking the reply as answer as there has been no update for a couple of days.
    If you come back to find it doesn't work for you, please reply to us and unmark the answer.

    Thanks

    George Zhao
    Forum Support
    ________________________________________
    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please click "tnfsl@microsoft.com"

    Friday, August 8, 2014 1:46 AM
    Moderator