First of all, it's important to know a few basic size limitations of the most common programs for opening CSV files. Read more about the limitations in this article
, where you can find out the size of the file that the program you're using is capable of opening.
If you don't know in advance how many rows are in your CSV, you can try and see if it will work with your preferred software.
Whatever software you use you will be limited to what your computer can handle, i.e the file size may not exceed the memory (RAM) limit of your computer, and you will still have trouble opening a large file.
There are several online tools that work with large files. These include CSV Opener, Delimitware, and others. Some of these tools offer free or trial versions, but they are basically paid options for working with large files.
An additional option for those who want to try some "tricks" with CSV is to split the file into more manageable pieces.