Viewing a very large CSV file?

Question

I have a very large 3.5 GB CSV file that I'd like to be able to read, sort through and filter for results based on various inputs. I'm pretty sure I can just import it to a MySQL database and go from there, but is there any program or online tool available that involves simply uploading the CSV and the rest is automatic?

I've actually worked with a couple of online services that will parse CSV but with a file that large, it's hard to say what will work. I doubt any free, hackjob CSV parser will let you upload a 3.5GB file. :) My guess is getting it into some sort of database might be your best bet. For what it's worth, I have read that tools like UltraEdit and Notepad++ can actually work with incredibly large files, but not sure if that helps. — trnelson, Nov 12 '13 at 18:08
You will probably benefit from reading the following: http://stackoverflow.com/questions/159521/text-editor-to-open-big-giant-huge-large-text-files It's about text files in general, but you may be able to get some good suggestions. Though, I have to say, given the "database" nature of csv files, going the MySQL way does not sound like a bad idea. — James, Nov 12 '13 at 18:36

score 8 · Answer 1 · answered Oct 05 '15 at 21:50

8

Yes, there is.

You can use OpenRefine (or Google Refine). OpenRefine is like a spreadsheet on steroids.

The file size that you can manipulate depend on your computer's memory.

answered Oct 05 '15 at 21:50

Estevão Lucas

4,440
34
37

score 5 · Answer 2 · answered Feb 22 '17 at 10:55

Since it is a CSV file.

Download http://openrefine.org/download.html
It is open source. Unzip openrefine.zip.
Run openrefine-2.7-rc.1\openrefine.exe.
It is a web app. So open http://127.0.0.1:3333/ in Chrome.
Upload the large csv file. In my case the file size was 3.61 GB and it got opened successfully.

https://snag.gy/02WECq.jpg

Neil McGuigan · Accepted Answer · 2016-03-10T07:36:33.330

You could try PostgreSQL 9.1+ and its file_fdw (File Foreign Data Wrapper) which would pretend that the CSV file is a table. If you replaced the CSV file with another CSV file of the same name, then you would see the new info immediately in the database.

You can improve performance by using a materialized view (PG 9.3+) which essentially creates a real database table from the CSV data. You could use pgAgent to refresh the materialized view on a schedule.

Another alternative would be to use the COPY statement:

/* the columns in this table are the same as the columns in your csv: */
create table if not exists my_csv (
  some_field text, ...
);

/* COPY appends, so truncate the table if loading fresh data again: */
truncate table my_csv;

/* 
you need to be a postgres superuser to use COPY 
use psql \copy if you can't be superuser 
put the csv file in /srv/vendor-name/
*/

copy 
  my_csv 
from 
  '/srv/vendor-name/my.csv'
with (
  format csv
);

score 2 · Answer 4 · answered Nov 19 '13 at 15:59

I had the same problem with a csv-file having over 3 Million lines. Could not open in OpenOffice Calc, Writer or Notepad++.

Then I used OpenOffice 4 base as a poor mans solution, which can link to csv. Short description (wording may not be correct as I use german OpenOffice).

Prepare: Your file needs .csv extension. First line should have field names. Put file as only file in a new directory to avoid confusion. Otherwise all files will be imported.
File - New - Database. The assistant should come up.
Connect to an existing database, format TEXT (your file needs to have .csv extension).
Next. Choose path to file (oddly not the file itself). Choose csv. Choose correct field delimiters.
Next and Finish.
Choose a name for your newly created db.

If everything is right you now see the table view with your newly created table.

You can also use gVim to view the file like in notepad, e.g. to add the first column descriptiom line.

You may create queries on this table. As the table has no indexes it is quite slow. Since OpenOffice does not make use of the hourglass it may seem the system has crashed.

Base is very limited and feels like early beta. Create new tables in that DB is not possible (thus no insert query to select from text file).

Export to csv is not possible. Reasonably sized query results can be (time consuming) copied and pasted to calc.

score 1 · Answer 5 · answered Nov 12 '13 at 18:59

1

Sure- there are quite a few Spreadsheet-like tools that support big data - IBM BigSheets being a major example.

For an online product with a free trial period, I'd recommend Datameer I've had relatively good success with them.

answered Nov 12 '13 at 18:59

RangerRick

51
4

score 1 · Answer 6 · answered Jul 25 '17 at 18:26

1

CSV Explorer is an online tool to read, sort, and filter CSVs with millions of rows. Upload the CSV and it will automatically import it and let you start working with the data.

https://www.CSVExplorer.com

answered Jul 25 '17 at 18:26

jastr

881
1
9
19

score 1 · Answer 7 · answered May 21 '21 at 09:24

You could use built-in excel's connection to do this .

Original Source : https://excel.officetuts.net/en/examples/open-large-csv

Steps :

Create a new excel file
Navigate to Data >> Get & Transform Data >> From File >> From Text/CSV and import the CSV file.
After a while, you are going to get a window with the file preview.
Click the little triangle next to the load button.
Select Load To…
Now, we need to create a connection and add data to the Data Model. This won’t load data to an Excel sheet because we have a limit for about a million rows there.
Click OK. This will take a while to load.
On the right side, you have the name of our file and the number of rows. If you save the file, you will notice that its size increased significantly.

Double-click this area to open Power Query Editor.
Now, if you scroll down, you will notice that the new rows are added on the go.
To change a value, right-click a cell and select Replace Values.
It’s going to replace all “builders” to “roofers” under the “profession” column.

score 0 · Answer 8 · answered Mar 08 '15 at 04:13

0

I had a file with ~100 million records, I used linux command line to view the files (just taking a look).

$ more myBigFile.CSV

or

$ nano myBigFile.CSV

it worked with a 6 GB file

answered Mar 08 '15 at 04:13

Mohammad Alqudah

23
3

score 0 · Answer 9 · answered Dec 16 '20 at 01:25

If it's a flat .CSV file and it does not involve a data pipeline, I'm not exactly sure about what you mean by "the rest is automatic".

For accessing larger .CSV files, the typical solutions are

Insert your .CSV file into a SQL database such as MySQL, PostgreSQL etc.

You'll need to design a table schema, find a server to host the database, and write server side code to maintain or change the database.

Processing you data using Python, or R.

Running Python and R on GBs of data will put a lot of stress to your local computer. It's also better for data exploration and analytics rather than table manipulation.

Find a data hub for your data. For example, Acho Studio.

A data hub is much easier but its costs may vary. It does come with a GUI that help you sort and filter through a table pretty easily.

score 0 · Answer 10 · answered Jul 05 '21 at 21:10

You can try Acho. It's an online tool and provides a free trial as well. I recommend it because its interface looks pretty great and intuitive. Also, it has all features that you mentioned, including sorting or filtering values. Basically, I use it to shrink the size of the dataset and export it to Python to do further analysis.

Viewing a very large CSV file?

10 Answers10

Linked