NOTE: When you can, prefer ASV over CSV -- Text File formats – ASCII Delimited Text – Not CSV or TAB delimited text, especially when you aren’t writing the files by hand. (Caveat: "UTF-8 won’t trip this since it is ASCII compatible. That is, any ASCII byte in valid UTF-8 encoded bytes corresponds to its ASCII codepoint, regardless of context. (All continuation bytes are at least 0x80 and all leading bytes for codepoints greater than U+007F are at least 0xC0, which makes the 0x1E and 0x1Fseparators safe.") UTF-16 or UTF-32 on the other hand...)
Convert CSV to ASV: xsv fmt -t$'\x1f' one.csv > one.asv
Command line tools
- GitHub - dinedal/textql: Execute SQL against structured text like CSV or TSV
- GitHub - BurntSushi/xsv: A fast CSV command line toolkit written in Rust.; a fork called qsv has added more features to the original xsv.
- csvkit 1.0.3 — csvkit 1.0.3 documentation
- Miller -- "Miller is like awk, sed, cut, join, and sort for name-indexed data such as CSV, TSV, and tabular JSON. You get to work with your data using named fields, without needing to count positional column indices."
- interesting thread
- tab -- A modern text/number processing language for the shell.
MacOS
- Table Tool is a fast, open source, mac native application to open csv files. I have set this as my default CSV opener.
Web tools
csvbase, a simple web database, faq
- View, add, edit and delete rows from a browser
- Export to CSV, XLSX, Parquet, etc
- Each table has a single URL, RESTfully accessed by HTTP verb
- has an API with API key, that you get when you register
- works out of the box with R, pandas, duckdb, apache-spark
- paste cells directly from Microsoft Excel
- code is open source on github under AGPL license, written in python.
Apache Metamodel
Apache Metamodel provides a common interface for discovery, exploration of metadata and querying of different types of data sources. Uniform connector and query API for CSV & data formats, relational databases, noSQL databases.
With MetaModel you use a type-safe SQL-like API for querying any datastore:
DataContext dataContext = DataContextFactory.create[TypeOfDatastore](...);
DataSet dataSet = dataContext.query()
.from("libraries")
.select("name")
.where("language").eq("Java")
.and("enhances_data_access").eq(true)
.execute();
The MetaModel query API allows you to use the power of SQL, even on data formats such as CSV files, Excel spreadsheets, NoSQL databases and more.