# CSV

- Page: https://shared.btbytes.com/p/csv
- Date: 2018-04-07
- Tags: tools, csv, query

NOTE: When you can, prefer ASV over CSV -- [Text File formats – ASCII Delimited Text – Not CSV or TAB delimited text](https://ronaldduncan.wordpress.com/2009/10/31/text-file-formats-ascii-delimited-text-not-csv-or-tab-delimited-text/), especially  when you aren’t writing the files by hand. ([Caveat](https://lobste.rs/s/w2in7w/ascii_delimited_text_not_csv_tab#c_v4vede): "UTF-8 won’t trip this since it is ASCII [compatible](http://www.unicode.org/charts/PDF/U0000.pdf). 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 `0x1F`separators 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](https://github.com/dinedal/textql)
- [GitHub - BurntSushi/xsv: A fast CSV command line toolkit written in Rust.](https://github.com/BurntSushi/xsv); a fork called [qsv](https://qsv.dathere.com) has added more features to the original xsv.
- [csvkit 1.0.3 — csvkit 1.0.3 documentation](https://csvkit.readthedocs.io/en/1.0.3/)
- [Miller](http://johnkerl.org/miller/doc/) -- "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](https://news.ycombinator.com/item?id=16781294)
- [tab](https://bitbucket.org/tkatchev/tab/src/master/) -- A modern text/number processing language for the shell.


## MacOS

* [Table Tool](https://github.com/jakob/TableTool) 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](https://csvbase.com/about), [faq](https://csvbase.com/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](https://shared.btbytes.com/p/pandas), [duckdb](https://shared.btbytes.com/p/duckdb), apache-spark
- paste cells directly from Microsoft Excel
- code is [open source on github](https://github.com/calpaterson/csvbase) under AGPL license, written in [python](https://shared.btbytes.com/p/python).


## Apache Metamodel

[Apache Metamodel](https://metamodel.apache.org/) 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:

```java
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.