12 ms·
TSV Utilities: Command line tools for large, tabular data files
- JimmyRuska 7y agoI've been noticing more swiss army-knife-like cli tools in the last few years. It would be cool if there were some that could support avro/parquet/orc formats. This one is notable because it's written in D lang by a mega corp. Some useful cli data wrangling tools -- https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv https://github.com/dinedal/textql https://github.com/dinedal/textql https://github.com/n3mo/data-science https://github.com/n3mo/data-science https://stedolan.github.io/jq/ https://stedolan.github.io/jq/ https://gitlab.redox-os.org/redox-os/parallel https://gitlab.redox-os.org/redox-os/parallel https://github.com/willghatch/racket-rash https://github.com/willghatch/racket-rash Would you have any others you recommend?
- j88439h84 7y agoIf you know a bit of Python, you can use it directly on streams in your shell. https://github.com/python-mario/mario https://github.com/python-mario/mario
- agumonkey 7y agoif one gets to port it to java, wario will make a great cousin
- mechatroner 7y agoRBQL: https://github.com/mechatroner/RBQL https://github.com/mechatroner/RBQL - SQL dialect with Python or JS expressions. I am the author, BTW
- IanCal 7y agoBig fan of visidata https://visidata.org/ https://visidata.org/ - one of those tools that does a lot but easily provides useful things with little complexity. For example open, select column, shift-f gives a histogram. Enter on one of those rows filters the data to just them. Can load a variety of data formats + anything pandas can (so parquet works). Edit - you can do a lot with it, quickly creating columns with python expressions, plots, joining tables, etc. but it's great for things like "pipe in kubectl output - get numbers of each job status, wait why are 10 failed, filter to those..." which easily works with any fixed width output.
- DSingularity 7y agoI’ve been looking for exactly this!
- austinjp 7y agoThanks for this. Looks perfect for my needs. I've got to analyse a hideous generated CSV file with thousands of columns. I was planning to load it into SQLite3 but it has too many columns for import. I was also unpleasantly surprised to find that the column names were too long for PostegresQL too. Python + pandas can handle it, but a tool for quick interactive exploration is just what I need. Hopefully visidata will provide!
- IanCal 7y agoIf you still hit size issues, you might be able to throw things through xsv first (list columns, select just a few columns and pipe out, get stats on contents of columns, samples, etc). It's one of my other goto things https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
- mbostock 7y agoI wrote one for working with newline-delimited JSON and JavaScript expressions: https://github.com/mbostock/ndjson-cli https://github.com/mbostock/ndjson-cli
- Arkanosis 7y agohttp://xmlstar.sourceforge.net/ http://xmlstar.sourceforge.net/
- sstephenson 7y agojwalk parses JSON into a stream of TSV records: https://github.com/shellbound/jwalk/ https://github.com/shellbound/jwalk/
- uhhyeahdude 7y agoCRUSH looks interesting, but you’ll have to poke around to find out what the tool(s) can do: https://github.com/google/crush-tools/blob/wiki/CrushTutorial.md https://github.com/google/crush-tools/blob/wiki/CrushTutoria...
- DonHopkins 7y ago>parses large documents slowly, but steadily, in memory space proportional to the key depth of the document If parsing JSON with shell scripts and awk is your idea of the most ideal way to "slowly, but steadily" get the job done. https://github.com/shellbound/jwalk/blob/master/lib/jwalk/commands/parse.awk#L137 https://github.com/shellbound/jwalk/blob/master/lib/jwalk/co... I know that everything looks like a nail if your only tool is a hammer, and it's fun to nail together square wheels out of plywood, but there are actually other tools out there with built-in, compiled, optimized, documented, tested, well maintained, fully compliant JSON parsers.
- bradknowles 7y agoGron -- Make JSON Greppable https://github.com/TomNomNom/gron https://github.com/TomNomNom/gron
- mirashii 7y agohttps://github.com/dflemstr/rq https://github.com/dflemstr/rq jq but supports avro, messagepack, protocol buffers, etc.
- kasperset 7y agohttps://www.gnu.org/software/datamash/ https://www.gnu.org/software/datamash/
- vips7L 7y agoPowerShell.
- trimbo 7y agoCrush Tools No idea how it compares to others but has been very handy. https://github.com/google/crush-tools https://github.com/google/crush-tools
- akbo 7y agoThese tools probably do a good job at processing CSV/TSV/DSV (haven't tried them). However, I would love if we could just stop using delimiter-separated value files alltogether. Why? Because the file format is an underspecified, unsafe mess. When you get this kind of a file you have to manually specify its schema when reading it because the file doesn't contain it. Also, due to its underspecification, there are many unsafe implementations that produce broken files that cannot be read without manual fixing in a text editor. Let's just start using safe, well-specified file formats like AVRO, Parquet or ORC. As a data scientist, I have had lots of issues because the data I got for a project was a CSV/TSV/DSV file. I recently spat out a rant on this topic, so if you want more details, check out https://haveagooddata.net/posts/why-you-dont-want-to-use-csv-files/ https://haveagooddata.net/posts/why-you-dont-want-to-use-csv...
- mhd 7y ago> These tools probably do a good job at processing CSV/TSV/DSV (haven't tried them). However, I would love if we could just stop using delimiter-separated value files alltogether. I hear ya. I have no doubt that "we" could, as in IT professionals. Maybe even the "surrounding" science fields that provide data could make an effort. But you're out of luck when it comes to almost any other field that serves you data, in my experience. Any org that you can't even tell details about the CSV you want (what's the "C"? UTF8? Quoting?) will have no chance of providing you with something more complex. It's partly our fault: The tools we provide them with suck. Excel's CSV handling is atrocious. Salesforce and similar tools seem to spit out barely consistent data dumps. Sometimes I feel like 80% of the industry is dealing with sanitizing input.
- burntsushi 7y agoThis is addressed in the xsv readme: https://github.com/BurntSushi/xsv#motivation https://github.com/BurntSushi/xsv#motivation We can't stop, because it's a de facto standard format for exchange with spreadsheet programs. So long as that's ubiquitous, we might as well write tools to make processing them easier. Also, I'm not sure why you called CSV unsafe. It's certainly the case that it's severely under-specified, but I don't think there's anything unsafe about it.
- cube2222 7y agoAnother one is the recent https://github.com/cube2222/octosql/ https://github.com/cube2222/octosql/ with a sql-based approach (I'm one of the authors)
- etatoby 7y agoI recommend "gron" https://github.com/tomnomnom/gron https://github.com/tomnomnom/gron > gron transforms JSON into discrete assignments to make it easier to grep for what you want and see the absolute 'path' to it. It eases the exploration of APIs that return large blobs of JSON but have terrible documentation. ▶ gron "https://api.github.com/repos/tomnomnom/gron/commits?per_page=1" | fgrep "commit.author" json[0].commit.author = {}; json[0].commit.author.date = "2016-07-02T10:51:21Z"; json[0].commit.author.email = "mail@tomnomnom.com"; json[0].commit.author.name = "Tom Hudson";
- unhammer 7y agoMy go-to tools: * vd (visidata) * xsv (often piping into vd) * jq (though I often forget the syntax =P) * gron (nice for finding the json-path to use in the jq script) * xmlstarlet (think jq for xml) * awk (or mawk when I need even more speed) * GNU parallel (or just xargs for simple stuff)
- mooss 7y agohttps://github.com/johnkerl/miller https://github.com/johnkerl/miller Miller (mlr) is now my goto tool for csv/tsv manipulation. I've used it from time to time during the past year and it's been a joy to use. I must admit that I did not try any other tool for the purpose so I don't know how it compares. It has a nice verb system, verbs can be combined with "then" statements : https://johnkerl.org/miller/doc/reference-verbs.html https://johnkerl.org/miller/doc/reference-verbs.html . There is also a small DSL to use with the "put" and "filter" verbs: https://johnkerl.org/miller/doc/reference-dsl.html https://johnkerl.org/miller/doc/reference-dsl.html . Another verb I find very useful is "join", which does pretty much what you would expect it to do.
- petre 7y agoMiller https://github.com/johnkerl/miller https://github.com/johnkerl/miller I'd normally pass but this is written in D so I'm looking forward to try it out.
- dima55 7y agoThere're lots of tools in this space that are similar in a very general way, but have widely different design choices. My tookit is https://github.com/dkogan/vnlog https://github.com/dkogan/vnlog It's tsv-utils-like, but is strictly a wrapper around existing tools. So filtering and transformations are interpreted literally as awk (or perl) expressions. And the various cmdline options match the standard tool options because they ARE the standard tools. So you get a very friendly learning curve, but something like tsv-utils is probably faster and probably more powerful. And it looks like tsv-utils references fields by number instead of by name. Many of the others (mine included) use the field names, which makes a MAJOR usability improvement. Other tools in no particular order: https://csvkit.readthedocs.io/ https://csvkit.readthedocs.io/ https://github.com/johnkerl/miller https://github.com/johnkerl/miller https://github.com/eBay/tsv-utils-dlang https://github.com/eBay/tsv-utils-dlang http://harelba.github.io/q/ http://harelba.github.io/q/ https://github.com/BatchLabs/charlatan https://github.com/BatchLabs/charlatan https://github.com/dinedal/textql https://github.com/dinedal/textql https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv https://github.com/dbohdan/sqawk https://github.com/dbohdan/sqawk https://stedolan.github.io/jq/ https://stedolan.github.io/jq/ https://github.com/benbernard/RecordStream https://github.com/benbernard/RecordStream
- j88439h84 7y agoAnother one, in Python: https://github.com/python-mario/mario https://github.com/python-mario/mario
- cube2222 7y agoAdding another one which I'm one of the authors of: https://github.com/cube2222/octosql/ https://github.com/cube2222/octosql/ It tries to provide ergonomic data munching of various formats, but using a sql interface, which most will probably feel immediately at home with.
- bradknowles 7y agoWith respect, who actually feels at home with SQL? It's about the most alien and obtuse language I've ever had the misfortune of encountering, and in that category I rate it worse than COBOL, FORTRAN, Assembly, C, Prolog, Sendmail re-write rules, BASH, and every other language I've ever encountered and had to use, but which I cannot recall at the moment.
- zX41ZdbW 7y agoWorth to note clickhouse-local: full featured ClickHouse SQL engine for files in CSV/TSV/JSONLines, whatever... https://www.altinity.com/blog/2019/6/11/clickhouse-local-the-power-of-clickhouse-sql-in-a-single-command https://www.altinity.com/blog/2019/6/11/clickhouse-local-the...
- paulryanrogers 7y agoNice! Or even just Sqlite
- zmmmmm 7y agoI always get excited by these types of tools and then end up going back to awk. How do you beat something that's already pre-installed on every Unix system for the last 40 years? Having said that ... these do look like they have some useful extras, in particular, around the annoying part of retaining / manipulating header rows ...
- Scarbutt 7y agoFor TSV, coreutils, awk, etc are nice to work with for sure but for CSV not so much.
- ISL 7y agoAt the risk of being flippant: sed 's/,/\t/g'
- bbernoulli 7y agoAnd hope you don't have any commas into your data?
- ISL 7y agoIn my line of work, that's an excellent assumption.
- misterdoubt 7y agoMust be nice to start work with clean data. Allow me to assure you that there are many, many others who don't have the luxury.
- Something1234 7y agoawk -F, ? Until you start dealing with quotes.
- boring_twenties 7y ago
- oehtXRwMkIs 7y agoI remember there was a TUI app that could handle CSV, HDF, etc. I can't find if for the life of me though. I remember the author even made their own TUI library for it. I've been looking for it for a while now, if anyone knows please let me know.
- flas9sd 7y agomust be visidata
- oehtXRwMkIs 7y agoI think that's the one, thanks so much.
- londons_explore 7y agoI feel like this might explain atrocious unicode handling and special character escaping in most eBay properties... Looking at you gumtree!
- pfarnsworth 7y agoeBay has an api project that appears to have been half-completed and then abandoned. Trying to understand exactly how to use their APIs is extremely confusing because they don't have anything definitive and basically a frankenstein-like API so I've given up. I frankly wouldn't trust anything coming from eBay at this point, they appear to have extremely poor developer support and no investment in making their APIs better to use.
- ragerino 7y agoYou might want to check out Apache Drill. It allows you to access structured files as database tables through JDBC/ODBC.
- riboflavin 7y agoDremio replaces Drill for a lot of that too, some of the original team has worked on it. Here's a tutorial from some folks who have used it: http://www.helicalinsight.com/technical-guide/connecting-csvexcel-via-dremio/ http://www.helicalinsight.com/technical-guide/connecting-csv...
- zahreeley 7y agoWhat is tsv? Didn’t find definition
- cbsmith 7y agoIt makes me cry how much time has been invested in formats like CSV's, TSV's, etc. ASCII (and UTF-8) has characters reserved for column, row, and even group separation. Just use them and save a lot of pain.
- jefftk 7y agoWhile that might have been a good idea if we'd started a while ago, at this point CSV/TSV are so much more established that using commas or tabs doesn't actually save pain.
- zachrose 7y agoBut wouldn’t you have to account for the possibility that those separators exist in the content your working with, putting you back at square one in terms of escaping?
- kec 7y agoThat's entirely the point: 0x1C and 0x1E should never actually appear in "normal" text unless someone has explicitly put them there, which is not necessarily true of , \t or \n.
- mdellavo 7y agousing csv and tools like grep and join is very fast for large datasets
- dev_dull 7y agoWe’ve discussed this at length and I’m squarely in the DO NOT camp. The drawbacks of using non-readable meta characters exceeds the benefits: 1. TSV can be read and imported by almost everything. 2. People can add and adjust TSV files from any editor. 3. What’s the way to insert meta characters again In VIM? And now nano? Argh I’ll just try and copy and paste it. Ugh that doesn’t work. Just use CSV/TSV folks. Anything more complicated and reach for a better serialization format (json, yaml) and not a better delimiter.
- jondegenhardt 7y agoHi all, Primary tsv-utils author here. Thanks for checking out the tools! I hope some of you find them beneficial.
- tyingq 7y agoAppreciate it, sincerely. Any chance this moves Paypal to offer more real-time reports vs submit-and-wait?
- Tharkun 7y agoThanks for these. I've rolled my own implementation of many of these over the last 20 odd year, most of them living in ugly shell scripts embedded as aliases in my env. This will make my life easier!
- inimino 7y agoThanks! These are awesome. The rationale provided is excellent and really explains the design decisions made. Kudos!
- yfiapo 7y agoCool that there are some specialty tools in this space and will use if I hit that performance challenge. Minor nit, I found the performance table (https://github.com/eBay/tsv-utils/blob/master/docs/Performance.md https://github.com/eBay/tsv-utils/blob/master/docs/Performan...) confusing at first and second glance. Alternating colors indicate.. different OSes? That doesn't seem to be the important message to convey as you are trying to show the speed of your tool and not the OS. Recommend to use the coloration to provide differentiation between tools instead of OS.
- jondegenhardt 7y agoThanks for the feedback. I agree, the performance table would benefit from better formatting.
- gibba999 7y agoSeems like a waste to do CSV->TSV without going all the way: http://www.tsvx.org/ http://www.tsvx.org/ The problem with TSVs and CSVs is that you might get an odd datatype 1TB into a file. For example, what you expect to be an integer value is somehow a string. TSVx extends TSV to add standard formats for things like headers which allows for strict typing. You can do things like export a database table and import losslessly. You can even export from MySQL and import into PostgreSQL most of the times without pain. The strict typing also avoids a lot of potential security issues. And in an environment where you control both ends (so you don't need to worry about security of where the file came from), it leads to much nicer APIs: you can refer to things by names rather than column numbers. It's more readable, and if the order or number of columns changes, nothing breaks.
- osrec 7y agoInteresting. Anyone have any experience of using tsvx in the wild?
- benatkin 7y agotsvx apparently isn't tsv. so it's not a good example of going all the way with tsv. it has a header that isn't tab separated. technically all text files are variable length tsvs. But a proper tsv file has the same number of columns per row.
- chubot 7y agoHm interesting, I hadn't seen tsvx. Where is it being used? Are you part of its development? FWIW I have my own TSV enhancement that is more modest: https://github.com/oilshell/oil/wiki/TSV2-Proposal https://github.com/oilshell/oil/wiki/TSV2-Proposal I hope to find time to prototype it in https://www.oilshell.org/ https://www.oilshell.org/ first. Although it's very simple -- about as simple as JSON -- I think it needs some real world use first to prove it. I think adding metadata is orthogonal to cleaning up the problems with TSV, e.g. that you can't represent strings with tabs (or newlines) in fields. Also I wouldn't want a TSV enhancement to depend on YAML, as it's quite a big and confusing (e.g. https://news.ycombinator.com/item?id=17359376 https://news.ycombinator.com/item?id=17359376)
- j88439h84 7y agoMario is a tool for manipulating data in your shell with Python functions. It supports csv, json, toml, yaml, xml. https://github.com/python-mario/mario https://github.com/python-mario/mario
- QuadrupleA 7y agoWouldn't a relational database be better and faster-querying and more space efficient in most cases, e.g. sqlite? Wonder what use cases would favor large collections of text / TSV files?
- perl4ever 7y agoPeople use delimited text files to transfer data from one database to another. I'm thinking of lawyers in particular. https://en.wikipedia.org/wiki/Load_file https://en.wikipedia.org/wiki/Load_file
- pletnes 7y agoIf you’re getting TSV files from an external source and are interested in just some aggregate results, you’ll get better performance by computing the smaller results and storing these than if you first import, then process, then delete the data in a SQL db. Also you don’t have to store the data twice.
- visarga 7y agoI've had something like this for 10 years, self made. I use cut, grep, sort, uniq from the system, but added a tool to compute counts per field, do value processing like awk (using a one line Perl script because I didn't want to learn awk), randomise order, split files by percentage, join multiple files by column and pretty format the data. Sometimes I make a chain of them 3-4 lines long, at which point I switch to a script.
- mlueft 7y agohttp://www.lueftenegger.at/produkte/csvsplitter-gratis-programm-zum-splitten-von-csv-dateien/ http://www.lueftenegger.at/produkte/csvsplitter-gratis-progr...
- Annatar 7y agoWith AWKA, in my tests I was able to get an instant 100% speed boost without modifying any of my AWK code. So using AWKA would likely beat the tools presented here, without needing to invest the time to learn a new tool, which for me at least is of paramount importance. https://github.com/noyesno/awka https://github.com/noyesno/awka
- jmsmistral 7y agoLooks cool! The presentation of the benchmark data could be improved by charting the values, however.
- maxekman 7y agoCheck out Nushell if you missed it a few days ago on HN: https://github.com/nushell/nushell https://github.com/nushell/nushell Related HN thread: https://news.ycombinator.com/item?id=20783006 https://news.ycombinator.com/item?id=20783006
- Mauricio_ 7y agoDear author: thank you for making multiple small programs instead of a single command with 400 flags.
- JoeAltmaier 7y agoAgreed! My pet peeve: open source packages that have no separation of the actual IP from the embodiment in an app or environment. Is it so hard to create a library? And put the real feature in there, instead of wrapped up in some run-on main module? Kudos to this writer for doing it well.
- jondegenhardt 7y agoThank you both! I too like the Unix philosophy of small tools that do a specific job. In a package like this, people may find they only care about one or two tools. They can ignore the rest and mix and match with other tools as they see fit.
- cben 7y ago`keep-header` is a superb gem of do-one-thing composability.