datHere's use case blogpost - Call for Use Cases!

[jqnatividad]

Happy New Year!

We just posted a blogpost sharing what we've used qsv for.

https://dathere.com/2024/01/qsv-use-cases/
https://www.linkedin.com/posts/joelnatividad_qsv-use-cases-activity-7149082354114842625-Facd

We'd heard from some folks how they use qsv (with one user even using it to wrangle 90,000 datasets at a time!) from all over the world, but have not really catalogued it.

These conversations and use cases have driven qsv's development and is why I ❤️ open source as it allows us to co-create qsv with ya'll!

As qsv pro (a desktop application that not only makes qsv more accessible with a user interface, it also enables expanded data-wrangling capabilities) enters limited preview, I'd like to continue that conversation.

If you're interested in trying out qsv pro and get actively involved in shaping its development, share your what you're currently using qsv for by creating a new Discussion in the "What I used qsv for" category.

[WMACCESS]

We're using qsv in our CSV data feed generation testing. From our WMACCESS database with static data and corporate events for millions of securities, we provide data feeds to our customers (banks, fund administrations etc) in various formats such as CSV, XML, Excel, or JSON.
The most used commands for our use cases are stats, flatten, and table and of course fmt to transform our ; separated output into , separated output.

[jqnatividad]

Thanks @WMACCESS for sharing!

It's gratifying to see that you've found qsv independently, have integrated it into your production pipeline, and above all, have instituted a program to recognize open source projects, of which I'm humbled, you've found qsv worthy.

https://github.com/WMACCESS

I'm sure we've all seen the XKCD comic about open source:

And the sad thing is, this is a well known problem with open source - the tragedy of the commons.

Being an open source contributor for more than two decades now across several projects - it's a systemic issue. Hopefully, your experiment in CFB recognizing and supporting OSS projects become more widespread.

Here's a very interesting recent paper on the topic - https://papers.ssrn.com/sol3/papers.cfm?abstract_id=4245266.

TLDR - it surmises that Governments have a unique role in nurturing the commons - much the same way it does for physical commons - roads, bridges and other physical infrastructure - it also has a vital role to play in ensuring the digital common infrastructure of our digital societies.

Thankfully, here in the US, there is also an experiment being conducted by the NSF - with its Pathways to Open Source Ecosystems program.

Fortunately, another OSS project I'm active in - CKAN has been selected as a Phase 1 grantee to study how ecosystem scale-up can be achieved so it becomes more sustainable. Check out https://civicdataecosystem.org to find out more.

Hopefully, we get the Phase II grant so we actually get to implement our Ecosystem scale-up plans.

[ondohotola]

I use qsv (and mlr alone and in combination) on the Mac to write convoluted bash functions. I then like csview, csvlens and of course typst and will replace as much csvq with qsv's sqlp as I can.

I have to download statements from multiple banks in multiple csv or xlsx formats and extract the info I need. All but one banks support ofx but the csv2ofx tool makes things so easy that I only use spreadsheet formats any longer.

My book keeping software sql-ledger has some form of an API so I generate that from the (generated) ofx to reconcile the bank accounts, while at the same time generating invoices for (as the payment narratives on the statements are recognizable per client and sql-ledger supports that).

sql-ledger can not export certain reports and the maintainer has passed away, so I pull stuff out of a replica of the backend.

My accountant wants the GL as spreadsheet so I'll pull that via csv into xlsx and then for each account into pdf so I can make sure I have all the founding documents for each account’s transactions.

I also upload daily exchange rates via a spreadsheet from the ECB into sql-ledger.

After pulling the required data out of the backend I generate VAT pdf printouts in a format as close as possible to the website we upload the data into (using typstypst). PAYE forms come out of a legacy ods spreadsheet.

In other words I try to put as much repetitive stuff into bash functions which make book keeping much less of a nuisance.

[aborruso]

Hi, I often do data cleaning and transformation operations, of structured text files.

The instrument I use most is Miller, both because I really like it and because I know it well.
Another one is duckdb.

I still haven't been able to get qsv fully into my pipelines, because every time I have a little obstacle, which is up to me, that I don't know it well and I don't know how to do the complex things that are possible to do.

But every time I can use it, it's a real gem. And @jqnatividad is really a very good professinist and a very caring and kind person.

Sometime I use it not for structured text files: for example these days, I needed to normalize the values of some variables, and it was enough for me to write echo "lorEm IPSuM" | qsv safenames to have "lorem_ipsum".

Thank you very much @jqnatividad

[jqnatividad]

Posting here for continuity @plambert's post on #1300.

Hmm. How best to describe it?

I have a data source for time-series data that doesn't have an API. So I pull it via a ruby/selenium script. Every Monday I run the script, and it downloads the data as CSV files. I keep the files in their original form, untouched, so that I can start from scratch at any time—sometimes column names change, or other things occur, so I need to be able to easily reprocess it all. While the CSV files have the first column as "date", the second row starts with "Total" and is the totals for the whole file. Not very useful! Also the column titles are horrible, and need to be cleaned up a lot. Especially because they and the values don't actually match other data sources from the same vendor. For example, this scraped data might call a product "Product One" and one REST API might call it "ProdOne" and another "prod_1". So I have to normalize both column names and values.

The majority of the CSV files are small, a few kb. Some can be several MB. Common manipulations I have to do are take a part of the filename, and add it as a column. So adding a 'foo' column to data-bar-2024-01-10.csv which has the value 'bar' from the filename, because there's a dimension in the data that is per report, and not inside the report. I concatenate them with a custom script because data is repeated, and I need to replace records with the record from the most recently downloaded datafiles. For example, 2024-01-10,foo,bar,baz,1.0 might be in a datafile downloaded today, and then next week a datafile has 2024-01-10,foo,bar,baz,1.1. I need just one record, with the 1.1 value, because the datasets overlap. I pull 90 days' data every week, since the dataset can change after the fact. Some data arrives late, other data changes because of other factors. I need the most-recently downloaded value for every row.

I do all of this with a combination of qsv and custom scripts, and the final reports are produced in Excel.

What sort of edge cases do I run into? Well, if I put daily data into an Excel table, it's limited to a little over a million rows (1024^2 I think). So I have to cut off the report to include as many days of data that fits in that row limit. However, I can't just cut anywhere--I have to have the complete set of data for each day. Currently, I get the unique set of days, then iterate in reverse, extracting each to a file in a temp directory and counting its size. When I cross the limit, I delete the most recent file, then concatenate and delete the temp files.

My final dataset is put into a sqlite database. It's not super fast, but it's very flexible. Although I think I have either encountered some bugs, or exceeded my own skills. So I again use a custom script to generate delta info.

For example, if I have 2024-01-01,foo,bar,baz,1.0 and 2024-01-02,foo,bar,baz,1.1 I want to add a column to every row with the difference from the previous day's value for the same dimensions. So in the second case, it'd become 2024-01-02,foo,bar,baz,1.1,0.1 showing that there was a 0.1 increase from the previous day's value. I use a custom script to read all the data into memory into a big data structure, then iterate through the dates with the trailing and current date, iterate on every record for the current date, do a lookup of both values, and add the delta to the current date. If the trailing date's record doesn't exist, I record the delta as 0. It's appropriate for my use case.

Things I can't do in qsv, though I am not saying I should be able to do them, because I have no idea how many other people might want to do them...

1. Take a CSV lookup file with value,canonical_value and change every value in a particular column that is found in the lookup's value column, with the canonical_value. For example, Pepsi,cola and Coke,cola in the lookup would allow every instance of exactly Pepsi or Coke in the selected column of the datafile be normalized to "cola". Case sensitivity would not matter for me, but maybe for others. In addition to the lookups, being able to apply a regex and replacement string from the lookup file rather than a separate invocation of qsv replace would be helpful.
2. Strip a trailing newline. When pasting a tsv into Excel, if the last line ends in a newline, then Excel creates a blank row. I have to strip that pesky newline, even though it really should be there. I've written a csv2tsv script that does this, while also converting to tsv. But an option to qsv fmt to not emit a newline after the final line, and clear documentation that this is how you make pasting a tsv into Excel easy, would be very helpful. (as an aside: perl -nE 'chomp; print "\n" if $. > 1; print' does a good job of stripping a final newline... it'd be way faster in qsv of course...)
3. Simple aggregations. If I have headers date,keyname,lockname,doorname,cost and want to spit out a new csv with date,keyname,lockname,cost that aggregates(sums) the cost... basically like a pivot table in Excel, if you're familiar. Or a sql statement with 'sum(cost)' and 'group by'.
4. Support for SQLite and maybe other SQL databases. It's trivial to load a CSV file into a sqlite file, but it's less trivial to load several into different tables in the same database, and being able to just pipe a series of qsv manipulations into something like qsv sql --sqlite ./data.db --replace-table dataset and have it do the "heavy lifting" would be great.
5. Many of the things that a pivot table can/could do, would be useful in qsv. Especially because there are a lot of features of Excel pivot tables only available in the Windows version, so they're not useful for those using MacOS. PowerQuery, for example. I'd love to be able to say qsv pivot -s date,keyname,lockname -sum cost file.csv > newfie.csv and have it do the aggregation I mentioned above. But even better would be saying qsv pivot -s date,keyname,lockname --eval "100 * sum(cost) / sum(cost - delta)" --as cost datafile.csv > newfile.csv though of course, the key would be documentation.
6. Documentation. What does qsv apply do? Why is it a handful of different things, none of which are obvious from the name? I am not criticizing--it's obvious why and how the tool arrived to where it is now, in terms of organization and design. But the learning curve is very high. There needs to be a lot more documentation from the perspective of "how do I do X..." and the commands could be reorganized to be easier to learn. In addition, it'd be good to take common use cases that are possible with one command, and make a specific command to do them as well. qsv tsv file.csv should convert file to tsv format. Sure, qsv fmt can do it... but someone who needs a tsv file will be looking for tsv.

Other random info, that probably isn't helpful:

When I say "custom script", it's probably in bash, perl, or crystal (https://crystal-lang.org/). The first is great for just stringing commands together. The second is great for quick scripts, though my script to calculate the deltas is in perl. The latter is great for speed and memory usage, since it's a statically typed, compiled language. I've been tempted on occasion to write my own variant of qsv in crystal, just for fun. But it quickly stops being fun--to get anywhere near a useful subset of qsv's features would take a huge amount of time, as you obviously know. :)

Anyway, that's my stream-of-consciousness dump. I hope it's helpful in some way.

[jqnatividad]

Thanks for sharing this detailed feedback @plambert.

To your points:

1. Take a CSV lookup file with value,canonical_value and change every value in a particular column that is found in the lookup's value column, with the canonical_value. For example, Pepsi,cola and Coke,cola in the lookup would allow every instance of exactly Pepsi or Coke in the selected column of the datafile be normalized to "cola". Case sensitivity would not matter for me, but maybe for others. In addition to the lookups, being able to apply a regex and replacement string from the lookup file rather than a separate invocation of qsv replace would be helpful.

Give the luau command and it lookup tables functionality a try. For an extended example, see https://github.com/dathere/qsv-lookup-tables. In the next few weeks, we'll have a whole library of high-value datasets (HVD) you can do lookup against (starting primarily with US-centric HVDs e.g. US Census, Bureau of Labor Statistics, etc.)

2. Strip a trailing newline. When pasting a tsv into Excel, if the last line ends in a newline, then Excel creates a blank row. I have to strip that pesky newline, even though it really should be there. I've written a csv2tsv script that does this, while also converting to tsv. But an option to qsv fmt to not emit a newline after the final line, and clear documentation that this is how you make pasting a tsv into Excel easy, would be very helpful. (as an aside: perl -nE 'chomp; print "\n" if $. > 1; print' does a good job of stripping a final newline... it'd be way faster in qsv of course...)

I just added a --no-final-newline option to the fmt command as well as a T (for tab character) special value for the --out-delimiter option.

It should be able to replace your csv2tsv script with qsv fmt data.csv --out-delimiter T --no-final-newline output.tsv 😄

3. Simple aggregations. If I have headers date,keyname,lockname,doorname,cost and want to spit out a new csv with date,keyname,lockname,cost that aggregates(sums) the cost... basically like a pivot table in Excel, if you're familiar. Or a sql statement with 'sum(cost)' and 'group by'.

The pivot command is already in the backlog - #799.

In the meantime, have you used the sqlp command? It can do quite complex and very performant SQL queries - complete with joins, aggregations, sorting, grouping and more!

4. Support for SQLite and maybe other SQL databases. It's trivial to load a CSV file into a sqlite file, but it's less trivial to load several into different tables in the same database, and being able to just pipe a series of qsv manipulations into something like qsv sql --sqlite ./data.db --replace-table dataset and have it do the "heavy lifting" would be great.

Again, take a gander at sqlp. It can really do "heavy lifting" type OLAP type queries without having to load CSVs into a database.

And should you really need to pump data into a database, the to command allows you to pump data (even an entire directory of CSV files in one go) into SQLite and PostgreSQL.

5. Many of the things that a pivot table can/could do, would be useful in qsv. Especially because there are a lot of features of Excel pivot tables only available in the Windows version, so they're not useful for those using MacOS. PowerQuery, for example. I'd love to be able to say qsv pivot -s date,keyname,lockname -sum cost file.csv > newfie.csv and have it do the aggregation I mentioned above. But even better would be saying qsv pivot -s date,keyname,lockname --eval "100 * sum(cost) / sum(cost - delta)" --as cost datafile.csv > newfile.csv though of course, the key would be documentation.

Would definitely keep these great suggestions in mind when implementing the pivot command!

6. Documentation. What does qsv apply do? Why is it a handful of different things, none of which are obvious from the name? I am not criticizing--it's obvious why and how the tool arrived to where it is now, in terms of organization and design. But the learning curve is very high. There needs to be a lot more documentation from the perspective of "how do I do X..." and the commands could be reorganized to be easier to learn. In addition, it'd be good to take common use cases that are possible with one command, and make a specific command to do them as well. qsv tsv file.csv should convert file to tsv format. Sure, qsv fmt can do it... but someone who needs a tsv file will be looking for tsv.

Couldn't agree more. Documentation needs more attention. I was hoping there would be more contributions to the Wiki, but it's been a bit sparse.

That's why we're building qsv pro and its associated premium resources (high value datasets for lookups, knowledgebase, recipes, etc.), a big subset of which will be available to the OSS version.

The KB, in particular, should help as we'll be using some AI (isn't everybody?) for it to generate a qsv recipe based on a natural language query.

[vi]

My primary tools to process csv files are xsv, q-text-as-data and Google Sheets where output of those tools typically end up.

qsv is for cases where xsv not enough: for example, I see some qsv apply calcconv in command history. Sometimes I experiment with qsv sqlp as alternative for q that starts faster, but very often some SQL things that work with q (i.e. SQLite) are missing from Polars.

I often use csvtool transpose (even though qsv also has transpose) and csvlens (interactive tool).

I also often use something like datamash --header-in perc:3 1 perc:25 1 perc:50 1 perc:75 1 perc:97 1 - this and more statistical tools (such as data normalisation / whitening) could be a nice addition to qsv.

Speaking of Google Sheets, I usually just paste CSV into a cell and let it split up the resulting mega-column into a table. "qsv" could have a simple text/html table formatter to make it more straightforward, to insert tables from qsv as if they were copied from the web. Ideally with a platform-specific clipboard integration to make qsv copy the table to clipboard with a proper content type instead of just plain text.

Another common tool I use is clipivot, which is already said to be in the qsv's backlog.

[jqnatividad]

Thanks for your feedback @vi and your contribution too! - https://github.com/jqnatividad/qsv/commits?author=vi

And clipboard support is a great idea! I just added it to the backlog - #1561

As for sqlp, Polars also has a torrid development pace, and its SQL support is starting to really catch up - and its speed - both in its query execution performance despite not having to setup anything beforehand (no need to load data into a database with the right schema and index it before running a query)

[Prid13]

I only used it for a single project where I needed to process CSV data in a desktop app, and haven't used it since as this is not my primary domain (I'm a frontend guy). But like the other comments say, I was blown away by such an extensive, fully-maintained, and cared-for love project being freely available to do all sorts of crazy operations on CSV files, and I am ever grateful for both the author and everyone else involved for actively working on this and publicizing it. It's such a great relief to have such an incredible tool available, and if I ever would need to do CSV manipulation again, I don't need to go hunting for another tool, as the best one already exists 😇

God bless every single one of you making this possible and for actively mainting this -- hope your lives are good and filled with blessings, I pray for a way out of every difficulty in life, and I only wish you the best of successes ⭐

[jqnatividad]

Thanks @Prid13 for your kind words!

Fortunately, as a renamed fork of xsv, qsv benefits from the xsv's solid foundations, enabling us to build all the additional commands in a modular manner.

Also, what started out as a side-project for a pilot we did during the depths of the pandemic, has now become a part of our daily workflow in our data engineering work, and we've integrated it into several other projects.

Apart from that, I'm also lucky that it's become my hobby as well 😄

Hopefully, you get a chance to use qsv once again sooner than later especially now that we'll be releasing a "front-end" for qsv with qsv pro.

[Prid13]

I'll be keeping an eye out for QSV Pro 😇

Wanted to add that initially finding qsv was a real struggle. I had to scour through Github and Google and go through tons of repos and tools before I found this gem. And I even remember struggling to re-find this once I had seen it once but lost the tab -- and for some reason it was almost impossible to find it again as I confused the original xsv repo with this one as well >,<

Hoping this gets more visible with the frontend releasing soon, or maybe the SEO has become better since I last used this? :)

[jqnatividad]

There is a dedicated website now at qsv.dathere.com, and when you search for "csv" on crates.io, qsv ranks second. Also, the Hacker News mention last month more than doubled the number of GitHub stars, and has helped with the tool's visibility.

When qsv pro is released, we'll do a concerted campaign to get the word out @Prid13 . We're hopeful that its UI should make the tool more accessible to a wider audience, not just folks who're comfortable with a command-line interface.

Thanks again!

[Prid13]

Woah! I was expecting a super simple, bare-bones website, but that totally blew me away -- bravo 👏 Absolutely love how everything is presented so graphically user-friendly. I did notice some minor tweaks the website could improve from, like a bit of padding on certain elements in the front page. Anywhere it's possible to contribute to the website? I'm a frontend guy, so this could be fun to help out with :)

[derekmahar]

Until now I hadn't shared my qsv use cases because I don't consider myself a qsv power user and don't yet use qsv in any of my scripts that process CSV files. (I still mostly use a mix of csvq, Miller, SQLite and more recently, csvtk and DuckDB.) Two years ago, I had intended to use xsv or qsv to replace my own basic CSV concatenation functions until I discovered to my astonishment that xsv and qsv both performed more poorly at CSV concatenation than other CSV processing tools like csvtk and Miller. While I appreciate that qsv has many other impressive and useful features, concatenation was the first task for which I had considered adopting it and it didn't perform nearly as well as other tools that I had compared. On the bright side, #1293 solves this concatenation problem for large numbers of CSV input files provided you specify as an argument to qsv cat either a directory that contains the CSV input files or a text file having extension .infile-list that contains the names of those files.

Though I don't yet use qsv in my scripts, I have followed the project closely for over two years, have suggested several improvements, and it is one of only two projects (DuckDB being the second) that I build daily. Now that #1293 is implemented, however, I may start using qsv at least for some of my CSV concatenation tasks.

[jqnatividad]

Thanks to your extensive benchmarking last month, which led me to create the cat --infile option - cat's performance improved exponentially and it's now competitive with the other CSV tools you mentioned, performing in the millisecond range.

#1293 (comment)

Your extensive input/feedback in the SQL discussion (#828), and your insight on luau (#788) has led me to really expand it as a DSL, and was the genesis of its BEGIN, MAIN, END block extensions.

So you're a power user in my book @derekmahar 😄

It's feedback like yours that pushes me to keep improving qsv!

[derekmahar]

@jqnatividad, thank you for granting me qsv "power-user" status! (I feel a bit like you've made me a qsv knight!)

Not to nit-pick, but qsv cat doesn't have an --infile option! You instead implemented an .infile-list argument text file type that qsv expects to contain a list of input CSV files. You also added a feature where users may specify a directory that contains a list of input CSV files. This means that a user need not even create the input file list of a directory that contains all of the CSV files. A directory is essentially just a list of files, so specifying an input text file would be redundant.

Usage:
    qsv cat rows    [options] [<input>...]
    qsv cat rowskey [options] [<input>...]
    qsv cat columns [options] [<input>...]
    qsv cat --help

cat arguments:
    <input>...              The CSV file(s) to read. Use '-' for standard input.
                            If input is a directory, all files in the directory will
                            be read as input.
                            If the input is a file with a '.infile-list' extension,
                            the file will be read as a list of input files.
                            If the input are snappy-compressed files(s), it will be
                            decompressed automatically.

[jqnatividad]

See! You actually know qsv better than me Sir @derekmahar ! 😉

[derekmahar]

Only that one small feature.

[terefang]

hello

the use-case is to convert and standardize data with timestamps (mostly timeseries) coming from various products.

the most common types of timestamps that i see are:

• Java/Javascript int64 milliseconds since 1970
• Unix epoch-seconds since 1970
• timezone-less strings in the format "YYYY-MM-DD hh:mm:ss"
• ISO 8601 style dates
• C/UNIX strftime dates in C or en_US locale

and converting them all into the ISO8601 UTC variant format "YYYY-MM-DD'T'hh:mm:ss'Z'"

[jqnatividad]

@terefang I'm glad you find qsv useful in normalizing timestamps.

We deal with a lot of data quality issues in our data pipelines, and surprisingly, even timestamps from machine-generated data feeds would sometimes inexplicably change date formats.

It's the main motivation behind the apply datefmt subcommand, and its support of 19 major date formats and sub variants of each, normalizing them to ISO8601 format by default - which conveniently, PostgeSQL recognizes as timestamps when bulk COPYing CSVs.