Add CSV support

[asmith3006]

It would be great to be able to open a CSV file and either query it or use in a BulkCopy command for moving large amounts of data.

[mithrandyr]

@asmith3006 -- I've previously looked into options for sql access to csv files and wasn't really satisfied with what I found. I had considered implementing a generic OLEDB or ODBC connection (and then you could do something with csv, depending on the OLEDB/ODBC libraries you have installed), but I didn't get there.

What you can do is something like this:

#assume we have a file 'people.csv' with the following structure: FirstName, LastName, EmailAddress

# InMemory SQLite database....
Open-SQLiteConnection

#create table to hold data
Invoke-SqlUpdate -Query "create table people(first text, last text, email text)"

#read csv and insert; note that I don't have to specify -parameters, parameters are automatically created based upon the inputobject
Import-Csv -Path .\people.csv | Invoke-SqlUpdate -Query "INSERT INTO people(first, last, email) values(@FirstName, @LastName, @EmailAddress)"

#Now, the above will do the inserts one at a time, which will be slow for large volumes.. so lets update the logic to use transactions
Start-SqlTransaction
Import-Csv -Path .\people.csv | Invoke-SqlUpdate -Query "INSERT INTO people(first, last, email) values(@FirstName, @LastName, @EmailAddress)"
Complete-SqlTransaction

now if you have too much data, you might want batching -- so in this case, you would read in the csv file in chunks. This will depend on your structure.. but suppose you had a million line csv file -- then your code might be something like
extract the headers from the first line of the file
split file into chunks, adding the headers as the first line to each chunk
use import-csv on each chunk, including wrapping in transaction

OR

extract headers, read chunks of the file, using convertto-csv with Headers and pass that into invoke-sqlupdate (wrapping in sqltransaction)

Effectively, what this doesn't give you opening the csv file as a connection... however, you can get good insert performance by using batching and transactions.

If you want to research an effective csv reader library that exposes the csv file through a System.Data implementation (https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbconnection?view=netstandard-2.0) - I could look at incorporating. Otherwise, I could look at incorporating OleDB or ODBC. Or if you want to provide a pull request that implements this, your are welcome to.

If you wait for me, its likely to be months -- I'm currently working on my dissertation & working fulltime. Thanks for your interest though!