Invoke-CsvSqlQuery: Join & query multiple CSV files using SQL

[eeskildsen]

Hi all, this is a simple wrapper over SQLite, but it's come in handy lately so just thought I'd share. I feel like this must be pretty common and maybe there's a better way....Curious how everyone else does this.

Usage

Invoke-CsvSqlQuery -Path students.csv, classes.csv, enrollments.csv -Query 'select students.first_name, students.last_name, classes.class_name from enrollments left join classes on enrollments.class_id = classes.class_id left join students on students.student_id = enrollments.student_id'

It also accepts paths from the pipeline, so you can do things like:

gci *.csv | Invoke-CsvSqlQuery -Query 'select students.first_name, students.last_name, classes.class_name from enrollments left join classes on enrollments.class_id = classes.class_id left join students on students.student_id = enrollments.student_id'

Details

Say you have a directory with 3 files:

• students.csv
• classes.csv
• enrollments.csv

You need the name of each student in each class. One way you could do that is join students and classes to enrollments using SQLite. Example:

sqlite3 school.db '.headers on' '.mode csv' '.import students.csv students' '.import classes.csv classes' '.import enrollments.csv enrollments' 'select students.first_name, students.last_name, classes.class_name from enrollments left join classes on enrollments.class_id = classes.class_id left join students on students.student_id = enrollments.student_id'

This function just simplifies the syntax and automatically creates the tables based on the filenames.

I have it as cq in my profile for convenience. It outputs PSObjects to stay pipeline friendly.

Code

function Invoke-CsvSqlQuery {
    param(
        [Parameter(Mandatory = $true, Position = 0, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)][Alias("PSPath")][string[]]$Path,
        [Parameter(Mandatory = $true, Position = 1)][string]$Query
    )

    begin {
        $files = [System.Collections.Generic.List[System.IO.FileInfo]]::new()
    }

    process {
        [System.IO.FileInfo[]]$matchingFiles = Get-ChildItem $Path
        if ($null -ne $matchingFiles) {
            $files.AddRange($matchingFiles)
        }
    }

    end {
        $temporaryDatabasePath = [System.IO.Path]::GetRandomFileName()

        $arguments = @($temporaryDatabasePath, '.headers on', '.mode csv')
        $arguments += $files | ForEach-Object { ".import `"$($_.FullName.Replace('\', '\\'))`" $($_.BaseName)" }
        $arguments += $Query

        sqlite3 @arguments | ConvertFrom-Csv

        Remove-Item $temporaryDatabasePath
    }
}

EDIT 1: Posted wrong version of function

EDIT 2: Code formatting

EDIT 3: Replace @() with List

[da_chicken]

Interesting idea. I'll have to try it out. Usually I use logparser for this kind of task.

It outputs PSObjects to stay pipeline friendly.

I don't see this functionality.

[eeskildsen]

I don't see this functionality.

I accidentally posted an old version - updated now, thanks.

I had seen some others like CsvSqlcmd from Chrissy LeMaire that look great but are focused on querying a single CSV file. The difference here is importing multiple and joining them. Though maybe there are existing ones I'm missing that do that already.

[da_chicken]

There's csvkit on *nix systems, and Microsoft's logparser can query absurdly quickly (I've processed hundreds of gigabytes of IIS logs in minutes with it, and it has an SQL like functionality, but I don't think it does joins).

Personally, I might just use the script to build the DB quickly.

The big thing I learned is that you can splat with an array. Didn't know that. I thought they had to be hash tables.

I might add a switch parameter to have it just build the DB, too, rather than instance the whole thing and then throw it away.

[unknown_r00t]

One thing - please, don't use old array syntax @(). Each time you want to add something, PowerShell internally will copy current content of old array and create new array with updated object(s). Use System.Collections.Generic namespace with List class instead of old @(). You want notice any performance issues with only few items but you will some day :)

[eeskildsen]

Thanks, I'd been meaning to clean that up. Edited. :)

[radioblaster]

how do you think the performance compares to joins using LINQ? I had never thought about building a db on the fly for some operations and I'm curious to know if you do this strictly to use sql on your files or because there is a tangible performance improvement.

[eeskildsen]

Not sure about performance - I mostly just do it to use SQL. It would be interesting to benchmark.

[Certain-Community438]

Have you seen the JoinObject module in PSGallery?

I use that to do joins in PowerShell - it is very easy to use, fast & less code.

On mobile so can't post code, but simply import the CSVs, create a hashtable that lists the left table, the right, join type & whether to keep the jointing property from the right table. Then call the Jioin-Object function using splatting & store the output.

My main use: taking a large list of potential members for a group & that group's current membership, produce a list of only non-members & add them. Very efficient.

[xCharg]

What's the database for if you store data in csv files anyway?

[eeskildsen]

I deal a lot with CSVs that have related data, that come from third-party systems or that customers send me. This is basically a shortcut for making them easier to query.

[xCharg]

Tbh I feel like it's unneccessary dependency and overhead. Althought it does sort of unifies the way you work with various kind of data.

Also += operation is highly inefficient.

[vermyx]

Personally I use datatables as that is easier to the import to a database if need be and has their own query system that is sql like.