it blows my mind that you can use sqlite with csv as input and then query it, it sounds so logical and useful yet I never came by it.
we have lots of reporting in CSV, can't wait to start using it to run queries quickly
we have lots of reporting in CSV, can't wait to start using it to run queries quickly
$> csvData = @"
Name,Department,Salary
John Doe,IT,60000
Jane Smith,Finance,75000
Alice Johnson,HR,65000
Bob Anderson,IT,71000
"@;
$> csvData
| ConvertFrom-Csv
| Select Name, Salary
| Sort Salary -Descending
Name Salary
---- ------
Jane Smith 75000
Bob Anderson 71000
Alice Johnson 65000
John Doe 60000
$> csvData
| ConvertFrom-Csv
| Select Name, Salary
| Sort Salary -Descending
| ConvertTo-Csv
"Name","Salary"
"Jane Smith","75000"
"Bob Anderson","71000"
"Alice Johnson","65000"
"John Doe","60000" /tmp/> "Name,Department,Salary
::: John Doe,IT,60000
::: Jane Smith,Finance,75000
::: Alice Johnson,HR,65000
::: Bob Anderson,IT,71000" |
::: from csv |
::: select Name Salary |
::: sort-by -r Salary
╭───┬───────────────┬────────╮
│ # │ Name │ Salary │
├───┼───────────────┼────────┤
│ 0 │ Jane Smith │ 75000 │
│ 1 │ Bob Anderson │ 71000 │
│ 2 │ Alice Johnson │ 65000 │
│ 3 │ John Doe │ 60000 │
╰───┴───────────────┴────────╯They're a powerful pairing
Here I'll:
- Send the csv data from stdin (using echo and referred to in the command by -)
- Refer to the data in the query by stdin. You may also use the _t_N syntax (first table is _t_1, then _t_2, etc.), or the file name itself before the .csv extension if we were using files.
- Pipe the output to the table command for formatting.
- Also, the shape of the result is printed to stderr (the (4, 2) below).
$ echo 'Name,Department,Salary
John Doe,IT,60000
Jane Smith,Finance,75000
Alice Johnson,HR,65000
Bob Anderson,IT,71000' |
qsv sqlp - 'SELECT Name, Salary FROM stdin ORDER BY Salary DESC' |
qsv table
(4, 2)
Name Salary
Jane Smith 75000
Bob Anderson 71000
Alice Johnson 65000
John Doe 60000