TSV Utilities: Command line tools for large, tabular data files

467 116 7 years ago

JimmyRuska• 7 years ago

I'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/dinedal/textql

https://github.com/n3mo/data-science

https://stedolan.github.io/jq/

https://gitlab.redox-os.org/redox-os/parallel

https://github.com/willghatch/racket-rash

Would you have any others you recommend?

j88439h844• 7 years ago

If you know a bit of Python, you can use it directly on streams in your shell.

https://github.com/python-mario/mario

agumonkey• 7 years ago

if one gets to port it to java, wario will make a great cousin

mechatroner• 7 years ago

RBQL: https://github.com/mechatroner/RBQL - SQL dialect with Python or JS expressions. I am the author, BTW

IanCal2• 7 years ago

Big fan of visidata 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• 7 years ago

I’ve been looking for exactly this!

austinjp• 7 years ago

Thanks 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!

mbostock• 7 years ago

I wrote one for working with newline-delimited JSON and JavaScript expressions: https://github.com/mbostock/ndjson-cli

Arkanosis• 7 years ago

http://xmlstar.sourceforge.net/

sstephenson• 7 years ago

jwalk parses JSON into a stream of TSV records: https://github.com/shellbound/jwalk/

uhhyeahdude• 7 years ago

CRUSH 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/CrushTutoria...

DonHopkins• 7 years 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/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.

bradknowles2• 7 years ago

Gron -- Make JSON Greppable https://github.com/TomNomNom/gron

mirashii• 7 years ago

https://github.com/dflemstr/rq

jq but supports avro, messagepack, protocol buffers, etc.

kasperset• 7 years ago

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

vips7L• 7 years ago

PowerShell.

trimbo• 7 years ago

Crush Tools

No idea how it compares to others but has been very handy.

https://github.com/google/crush-tools

akbo2• 7 years 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.

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

mhd• 7 years 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.

burntsushi2• 7 years ago

This is addressed in the xsv readme: 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.

cube22222• 7 years ago

Another one is the recent https://github.com/cube2222/octosql/ with a sql-based approach (I'm one of the authors)

etatoby• 7 years ago

I recommend "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• 7 years ago

My 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• 7 years ago

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 . There is also a small DSL to use with the "put" and "filter" verbs: 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• 7 years ago

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• 7 years ago

There'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

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://github.com/johnkerl/miller

https://github.com/eBay/tsv-utils-dlang

http://harelba.github.io/q/

https://github.com/BatchLabs/charlatan

https://github.com/dinedal/textql

https://github.com/BurntSushi/xsv

https://github.com/dbohdan/sqawk

https://stedolan.github.io/jq/

https://github.com/benbernard/RecordStream

j88439h844• 7 years ago

Another one, in Python:

https://github.com/python-mario/mario

cube22222• 7 years ago

Adding another one which I'm one of the authors of: 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.

bradknowles2• 7 years ago

With 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• 7 years ago

Worth 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...

paulryanrogers• 7 years ago

Nice! Or even just Sqlite

zmmmmm• 7 years ago

I 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• 7 years ago

For TSV, coreutils, awk, etc are nice to work with for sure but for CSV not so much.

ISL2• 7 years ago

At the risk of being flippant: sed 's/,/\t/g'

Something1234• 7 years ago

awk -F, ?

Until you start dealing with quotes.

UweSchmidt• 7 years ago

I agree. Mastering any tool of that sort is a lot of effort and I'd rather invest it in a tool that's reliable and always available.

If you have to explore data anyway, using basic unix tools, piping one into the other, always works. You end up with verbose, but readable code.

Put it into a script, make sure it works in a more general case and you might get close to what some of the more specialized tools can do.

pvg• 7 years ago

How do you beat something that's already pre-installed on every Unix system for the last 40 years?

One simple and useful way is by being much faster, which is the case for these tools. There are benchmarks linked in the README.

thewhitetulip3• 7 years ago

I totally agree. awk, sed, grep are amazing and it astonishes me how many people aren't fluently able to write scripts in it. I used to be in that camp few months earlier and I used to write everything in Python, but one day, I determined to learn shell scripting and I loved it.

awk::Python what Java::Python. What we can do in 10 lines of Python can be done in one line of awk and it doesn't have to be unreadable!

Main pain in the neck is finding a good tutorial. As usual, I started documenting what I learnt in an end to end guide here, https://github.com/thewhitetulip/awk-anti-textbook

perl4ever5• 7 years ago

Is there anything you can do with awk or sed that isn't built in to perl? I know I never used awk after I learned the latter, and sed very, very rarely.

j88439h844• 7 years ago

I totally agree about the unnecessary verbosity.

For a more concise Python on the command line, I like Mario: https://github.com/python-mario/mario

cf• 7 years ago

The original awk book from the creators is in my opinion really good. https://archive.org/download/pdfy-MgN0H1joIoDVoIC7/The_AWK_P...

oehtXRwMkIs2• 7 years ago

I 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• 7 years ago

must be visidata

oehtXRwMkIs2• 7 years ago

I think that's the one, thanks so much.

londons_explore• 7 years ago

I feel like this might explain atrocious unicode handling and special character escaping in most eBay properties... Looking at you gumtree!

pfarnsworth• 7 years ago

eBay 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• 7 years ago

You might want to check out Apache Drill. It allows you to access structured files as database tables through JDBC/ODBC.

riboflavin• 7 years ago

Dremio 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-csv...

cbsmith6• 7 years ago

It 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• 7 years ago

While 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• 7 years ago

But 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• 7 years ago

That'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• 7 years ago

using csv and tools like grep and join is very fast for large datasets

lgas• 7 years ago

Using the reserved separator characters wouldn't change that.

perl4ever5• 7 years ago

I always used cut, sort, and uniq a lot too.

dev_dull3• 7 years ago

We’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.

cbsmith6• 7 years ago

Like a lot of things, #1 is only partly true, particularly if you have embedded tabs.

The number of times I've had to deal with parsing errors because of embedded carriage returns, commas, tabs, etc., sometimes costing millions of dollars is just... upsetting.

It's like JSON... we say everything can parse it, but really what we've got is some approximation that will come back to haunt us... and once you've done all the work to make sure everything is precise and correct, you'd really have saved time if you'd just used the tools we already had in the first place.

cbsmith6• 7 years ago

2. More accurately, people can screw up TSV files from any editor. Have you not seen embedded spaces used instead of tabs and totally mess everyone up?

cbsmith6• 7 years ago

3. If you don't know how to use your editor, maybe you should learn. If your editor can't insert even all the characters in basic ASCII, it's not an editor.

Annatar3• 7 years ago

Both JSON and YAML are very difficult to construct parsers for.

To make matters worse, both formats are full of pitfalls:

http://seriot.ch/parsing_json.php https://arp242.net/yaml-config.html

using either of these, in my opinion, is extremely misguided.

tinus_hn• 7 years ago

Just use these formats if you want to be stuck in the last century when internationalization was that odd thing you could easily afford to ignore. Otherwise, use a real format.

xashor2• 7 years ago

We took the wrong path quite some time ago. Most of the POSIX utils like sort, cut, grep only support \n (or sometimes \0) as row separator.

jlgaddis2• 7 years ago

Are you aware of $IFS?

perl4ever5• 7 years ago

For some reason, in some circles, it seems to be semi standard to use þ (0xFE aka thorn) as the delimiter and a paragraph symbol (0x14 aka DC4 aka ^T) as the separator. The latter is not to be confused with 0xB6.

Anyway, these character are presumably not going to occur in ordinary text.

PeterisP• 7 years ago

All of "upper ascii half" can occur in ordinary text in "pre-Unicode" encodings.

0xFE is a good example - you may get a customer or employee from Iceland with that character in their name (e.g. https://en.wikipedia.org/wiki/Haf%C3%BE%C3%B3r_J%C3%BAl%C3%A...), or data in cyrillic cp1251 or koi8-r enconding where 0xFE also represents characters that you'll encounter in surnames, etc.

dagenix• 7 years ago

But, there is nothing to stop your values from containing these characters. So, you still have to escape your input. And once you've done that, you might as well just use csv / tsv.

cbsmith6• 7 years ago

Actually, the whole point of those characters is that they cannot be used in values.

jstimpfle• 7 years ago

How do you do (for example) column names or types with ASCII only?

How do you extend ASCII?

kps• 7 years ago

ESC is the ASCII character for code extension.

ISO 2022 aka ECMA-35¹ standardizes how to use it in general, but the only thing that really caught on is a subset of the terminal control extensions (ISO 6429 aka ECMA-48² aka ‘ANSI’). In the hypothetical alternate universe where people use ASCII-based structured data, ISO 2022 would have standard sequences for such metadata.

If you were rolling your own today, you'd probably wrap the metadata in Application Program Command (ESC _ … ESC \) or Start Of String (ESC X … ESC \) or one of the private-use sequences. ¹ https://www.ecma-international.org/publications/standards/Ec...

² https://www.ecma-international.org/publications/standards/Ec...

jondegenhardt3• 7 years ago

Hi all, Primary tsv-utils author here. Thanks for checking out the tools! I hope some of you find them beneficial.

tyingq• 7 years ago

Appreciate it, sincerely. Any chance this moves Paypal to offer more real-time reports vs submit-and-wait?

Tharkun• 7 years ago

Thanks 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• 7 years ago

Thanks! These are awesome. The rationale provided is excellent and really explains the design decisions made. Kudos!

yfiapo• 7 years ago

Cool 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/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.

jondegenhardt3• 7 years ago

Thanks for the feedback. I agree, the performance table would benefit from better formatting.

gibba999• 7 years ago

Seems like a waste to do CSV->TSV without going all the way:

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• 7 years ago

Interesting. Anyone have any experience of using tsvx in the wild?

benatkin• 7 years ago

tsvx 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• 7 years ago

Hm 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

I hope to find time to prototype it in 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)

woofie112• 7 years ago

I think it depends on how YAML is used. Requiring full YAML on an input is cumbersome on users (e.g. if any downstream user MUST parse ANY YAML). On the other hand, a simple format which happens to be YAML-compatible means anyone with a YAML parser can parse it without reinventing wheels. That seems a good thing.

An example of that is JSON. Any YAML parser will parse JSON.

TSVX merely seems to guarantee that the output is a simplified (but compatible) form of YAML. All this means is that any YAML parser will read it, and you don't need to write your own. But it's just a colon-delimited dictionary with a few constraints.

enriquto• 7 years ago

It wouldn't seem that tsvx is "going all the way", it's a completely different thing that dirties your numbers with silly metadata.

woofie112• 7 years ago

TSVs aren't just numbers. A TSV might be numbers for the first terabyte, and then include a string, just for the heck of it.

I'd call that "dirtying numbers," rather than an removable header which allows you to programmatically validate that your numbers have not, in fact, been dirtied.

j88439h844• 7 years ago

Mario 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

QuadrupleA• 7 years ago

Wouldn'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?

perl4ever5• 7 years ago

People 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

pletnes• 7 years ago

If 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• 7 years ago

I'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• 7 years ago

http://www.lueftenegger.at/produkte/csvsplitter-gratis-progr...

Annatar3• 7 years ago

With 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

jmsmistral• 7 years ago

Looks cool! The presentation of the benchmark data could be improved by charting the values, however.

maxekman• 7 years ago

Check out Nushell if you missed it a few days ago on HN: https://github.com/nushell/nushell

Mauricio_• 7 years ago

Dear author: thank you for making multiple small programs instead of a single command with 400 flags.

JoeAltmaier• 7 years ago

Agreed!

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.

jondegenhardt3• 7 years ago

Thank 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• 7 years ago

`keep-header` is a superb gem of do-one-thing composability.

TSV Utilities: Command line tools for large, tabular data files

Related Stories