How can I open this colossal .csv file?

I have an enormous 32.8GB .csv file containing patient vital data. I need to open it and sift through the entries, but am unable to.

The file is too large to open in Excel or Access. I've tried using applications that split .csv files into smaller pieces, but I don't think I have access to a machine with enough memory to handle the operation. Is there any way to handle this ridiculously big file?

6 Answers

Relevance
  • 4 weeks ago
    Favorite Answer

    For something that large, you'd probably want something that read line by line instead of trying to open it all at once. It'd be trivial to do in Linux, but I've never tried working with such a large dataset in Windows. 

    If you're using Windows 10, enabling the Windows Subsystem for Linux is relatively easy. If you know what structure it uses, it'd then be relatively easy to sort into smaller files. For example

    egrep "^A" LargeCSVFle.csv >> A.csv

    would copy only lines starting with the letter A. Presumably, this may still be several gigabytes in size. If you find it's too large to open with your typical tools after that, you could narrow it further with something like

    egrep "^A[a-n]" A.csv >> AaAn.csv

    Sorry I can't offer a more native Windows solution. 

    • Dennis4 weeks agoReport

      ore you could try this. https://www.microsoft.com/en-us/p/xls-viewer-free/9nblggh6hbf5?activetab=pivot:overviewtab

    • Login to reply the answers
  • 4 weeks ago

    On a Unix/Linux system you can use the split command. That can work with any size input file.

    • Login to reply the answers
  • 4 weeks ago

    You can transfer it to a flash drive that has enough space to accept it, then you will be able to open it

    • It's saved on a 64 GB flashdrive. The problem isn't storage, it's memory and limits of the applications.

    • Login to reply the answers
  • 4 weeks ago

    export your data in a format that is 

    1. hippa compliant (if you are in the USA) (exporting in a non secured CSV is in violation of HIPPA)

    2. in small enough chunks to be able to be opened in excel

    • The problem is how do I get it into small enough chunks?

    • Login to reply the answers
  • How do you think about the answers? You can sign in to vote the answer.
  • 4 weeks ago

    Instead of using Excel to open the file, have you tried IMPORTING the file to Excel? 

    • Yes, and after waiting 3 hours for it to import it still only showed 1,048,576 out of ≈ 400,000,000 rows

    • Login to reply the answers
  • Anonymous
    4 weeks ago

    Are you using the 32-bit or 64-bit versions of Excel? If 64-bit, you should be able to open it.

    • ...Show all comments
    • P
      Lv 7
      4 weeks agoReport

      The 32 bit version of Excel installs by default on BOTH 64 bit computers and 32 bit computers, so it is something you need to specifically double check within Excel.  

    • Login to reply the answers
Still have questions? Get your answers by asking now.