Applying filters to headers in a huge CSV file

Question

I am working with a CSV file that is approximately 500 MB.

When I open this file in Excel, the wrong data is displayed under the column headers. I suspect this may be due to the large size of the CSV file involved. Opening with the EM editor neither allows me to align the column data nor to remove entire columns from the CSV file.

What is the best way to use this CSV to align the data in columns and to delete columns that are not of interest?

Answer 1

The csvkit python library is great for transforming big csv datasets in the style of a unix command line tool (like sed). It has many small utilities that do one thing well each so you can compose them in helpful ways. In your case, csvcut can extract certain columns from a csv.

From their docs:

Extract columns named “TOTAL” and “State Name” (in that order): 

$ csvcut -c TOTAL,"State Name" examples/realdata/FY09_EDU_Recipients_by_State.csv

Good luck!

Answer 2

Try first managing a smaller version of this file first. And this is where the classic techniques never die.

Run cmd.exe which will be available on Windows

Navigate to the directory containing the file (you can do this by typing "pushd" and then right-clicking and pasting the directory).

Use the head command to create a version of the file that will contain a specified number of lines from the original file. (example: head -n1000 original.txt > header.txt)

In this example the -n1000 specifies the number of lines taken from the original file, which in this case is 1000 because I specified 1000; likewise, if you want only 100 lines, change it to -n100.

The original.txt would be the name of your original file which be the name and then the extension which is .csv.

The ">" symbol specifies to send the information to the new file, which in this example I've named header.txt, which will write the new file in the same directory.

This should make it MUCH easier to handle with and explore this .csv.

Answer 3

I'd suggest you look at TextQ (disclaimer - I'm its developer). It can import a big CSV file and allows you to manage its schema/structure:

• parse date and numbers in various formats;
• rename or hide columns;
• index columns for faster queries;

You can filter rows via a UI Query Builder.

For advanced users, TextQ supports SQL - select, join, group by, etc. The UI Query Builder can convert to SQL with a single button click.

You can export any query result (filtered rows) to a CSV file, which can be imported in MS Excel or others.

You can get it from the Mac App Store or Microsoft Store (coming soon).

Answer 4

It's quite a common operation, file size doesn't really matter. I do it with 20GB CSV files sometimes.

I use undatum cmd tool created initially for JSON lines processing but it works with CSV files too.

Example of the command to get a list of headers fields

undatum headers budgetreg_domains.csv

It will provide a list of field names from the file first line

Example of the command to extract only 2 fields:

undatum select -f okopfCode,okoguCode budgetreg_domains.csv 

It will output CSV file to std output with just 2 fields okopfCode and okoguCode from this file.

Disclaimer: I am author of undatum tool, feel free to submit any issue or idea how to improve it.