6

I am trying to open a CSV file in Excel 2013 but nothing really happens. Excel is just opening without anything visible (just the gray background as you have just closed a workbook).

The CSV file is 2.7GB. I have no idea how many rows that could be, the columns are 30.

I have seen similar problems with quite smaller files which still doesn't open. No solutions so far...

Thanks :)

Pepys
  • 981
  • 7
  • 16
  • 34
  • 1
    2.7GB is a very large file for Excel. What are you expecting us to tell you? Does [this article](http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_Overview) answer your question? – Roger Rowland Nov 26 '13 at 08:58
  • It is... Well how the hell to open such files? :) Could there be some other editors to open it? I tried Notepad++ and it says unsurprisingly: "File is too large". – Pepys Nov 26 '13 at 09:00
  • What do you expect to do *if* it is opened? Maybe you need to consider command line tools or databases? – zx8754 Nov 26 '13 at 09:02
  • 3
    [These are the limits for Excel 2013](http://office.microsoft.com/en-gb/excel-help/excel-specifications-and-limits-HA103980614.aspx) I think maybe you have to split your file and load it in portions. If you're 32-bit, it's definitely above the limits. If it helps, [Google found me this](http://www.swiftgear.com/ltfviewer/features.html). – Roger Rowland Nov 26 '13 at 09:03
  • 2
    @zx8754 I will load it to a database which actually works fine but I wanted to edit some characters that are not needed. I guess will do that with SQL task... – Pepys Nov 26 '13 at 09:12
  • @RogerRowland +1 for the LTFViewer! It opened it :) FYI There are 10,109,450 rows. Great. If you answer the question so I can mark it as resolved? – Pepys Nov 26 '13 at 09:16
  • @Pepys Excel 2013 with Power Pivot can *load and properly process* **much** larger data sets but the saved *Excel* file has to be up to 2 GB. That's because is uses column compression and the same analytic engine as SQL Server. XLSX is itself a compressed format so a 2GB Excel file can hold a lot more than 2 GB of data, but that depends on how well the data compresses. – Panagiotis Kanavos Nov 27 '14 at 13:19

3 Answers3

3

For info, these are the limits for Excel 2013, so it looks like you will need to split your input file and examine it in sections - that's especially true if you are on 32-bit because Excel expects to have only 500 - 700 Mb free for data in a 2GB address space.

If it helps, Google found the Large Text File Viewer, which may be a useful alternative.

Roger Rowland
  • 25,885
  • 11
  • 72
  • 113
  • 2
    That's not correct. 64-bit Excel 2013 with Power Pivot can *import* and *process* as much data as the computer's memory can handle (4,8,16GB or more) because it uses column compression and the same analytic engine as SQL Server. By process I mean join, filter and aggregates multiple sources in memory – Panagiotis Kanavos Nov 27 '14 at 13:19
1

I would like to add some points that might help you now and for the future.

You answered in comment that you want to load the csv file to a database. I would recommend you if it's possible to use an SSIS package. You could do this without actually open the file. Inside the package you can also edit your characters and transform your data before you insert it in the database. Of course you could do this after the insertion in your SQL queries.

Pirvu Georgian
  • 657
  • 1
  • 12
  • 37
1

total commander has a feature for splitting files. try to use it

Benjamin Gutman
  • 11
  • 3