PowerShell import-csv to database

Question

I want to insert csv data into access database via powershell. Im using below code to insert data. it executed successfully but the issue is that it insert blank rows in database. Im not sure where Im making mistake. Any Idea?

    $datafile='my_file.xls'
$dbfile='C:CSVtoAccessTestDatabase.accdb'
$connectionString="Provider=Microsoft.Ace.OLEDB.12.0; Data Source=$dbfile"
$conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
$conn.Open()
$cmd = $Conn.CreateCommand()
Import-Csv $dataFile | 
     ForEach{
         $cmd.CommandText = "INSERT INTO CSVDATA2(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26) VALUES ('$($_.col1)', '$($_.col2)', '$($_.col3)', '$($_.col4)', '$($_.col5)', '$($_.col6)', '$($_.col7)', '$($_.col8)', '$($_.col9)', '$($_.col10)', '$($_.col11)', '$($_.col12)', '$($_.col13)', '$($_.col14)', '$($_.col15)', '$($_.col16)', '$($_.col17)', '$($_.col18)', '$($_.col19)', '$($_.col20)', '$($_.col21)', '$($_.col22)', '$($_.col23)', '$($_.col24)', '$($_.col25)', '$($_.col26)')"
         $cmd.ExecuteNonQuery()
     }
$conn.Close()

Any Idea where Im making mistake.

Regards,

Danish

Answer 1

The problem is caused by Powershell's handling of single ' and double " quotes. As per the documentation,

When you enclose a string in double quotation marks (a double-quoted string), variable names that are preceded by a dollar sign ($) are replaced with the variable's value before the string is passed to the command for processing.

When you enclose a string in single-quotation marks (a single-quoted string), the string is passed to the command exactly as you type it. No substitution is performed.

Thus, this statement

"INSERT INTO CSVDATA2(F1,F2,F3...) VALUES ('$($_.col1)', '$($_.col2)', '$($_.col3)'...)"

will not expand any of the $($_.coln) into a values.

Use Powershells -f string formating, like so

$cmd.CommandText = "insert into foo(bar, zof) values ('{0}', '{1}')" -f $_.col1, $_.col2