Tools for big CSV files manipulations

Question

I'm working with a huge CSV dataset and I'm now make some pre-processing on it. The pre-processing consists of removing lines with "not a numbers", change columns positions or remove lines. Is there any tools package or library in any programming language that performs this kind of pre-processing?

Many thanks

Richard

Answer 1

You could try datamash

https://www.gnu.org/software/datamash/manual/datamash.html

Also importing your records into mysql.

importing into sqlite.

sqlite> .mode csv
sqlite> .import test.csv foo

if the first line of your csv file contains the column names, then you can omit then you don't have to create the table first.

Not a number SELECT * FROM myTable WHERE col1 NOT REGEXP '[0-9]+';

You can and together as complex as where statement as you need.

select *

You can use to select column names in any order, and omit or move columns at will.

select a,b,z,t,w,u from table;

Notice they are not in any order, and columns could be missing.

You can physically move columns around, but because of the way SQL works it is completely unnecessary.

ALTER TABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department;

Answer 2

The ideal tool for this is a python tool called Pandas.

• Free, Gratis & Open Source
• Cross Platform
• Can handle huge data sets
• Intelligent label-based slicing, fancy indexing, and subsetting of large data sets

Answer 3

You should check out TextQ (disclaimer - I'm its developer). It can import a big CSV file and manage its schema/structure:

• rename/hide columns;
• parse dates and numbers;
• index columns to speed up search;

You can filter rows with a UI Query Builder.

For more advanced users, TextQ supports SQL. You can select, join, group by, etc. The UI Query Builder can convert to SQL to get you started.

You can export any query result to a news CSV file, which can be imported in MS Excel or other tools.

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

Answer 4

If you prefer a good PHP library, try Spout: a PHP library "to read and write CSV or Excel files in a fast and scalable way" (supports CSV, XLSX and ODS file formats). It's "capable of processing very large files while keeping the memory usage really low".

Answer 5

you can use Acho Studio for this.

1. remove lines by using the "filter" function
2. change the column position by using the "move column" feature
3. then you can download the csv files for further analytics