loading into An access database

Hi, I need to load data into An Access 2000(!) database to support a legacy application, what I have basically works but any record with an apostrophe in fails to load in.

The code is.

$dataSource = "C:tempstaff.mdb" $dataFile = "C:tempstaff.csv" $dsn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource;" ## create connection object and open the database $objConn = New-Object System.Data.OleDb.OleDbConnection $dsn $objConn.Open() $cmd2 = $objConn.CreateCommand() $cmd2.CommandText="delete from staff" $cmd2.ExecuteNonQuery() $Users = Import-Csv -Delimiter "," -Path "C:tempstaff.csv" foreach ($User in $Users) { $Hostkey = $User.hostkey $Name = $User.name $Department = $User.department $Email = $User.email $Title = $User.title $cmd = $objConn.CreateCommand() $cmd.CommandText="INSERT INTO staff VALUES('$Hostkey','$Name','$Department','$Email','$Title')" $cmd.ExecuteNonQuery() } $objConn.Close() $objConn 

And the error I get is

Exception calling "ExecuteNonQuery" with "0" argument(s): "Syntax error (missing operator) in query expression ''o'keefe','dept','o'keefe@email.com''." At C:tempstaff.ps1:29 char:1 + $cmd.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : OleDbException 

All my fields in the database are shorttext and en example records I am loading in is

hostkey,name,department,email,title 1234567,o'keefe,dept,o'keefe@email.com,8370F 

Anyone see where I am going wrong?


submitted by /u/uniitdude
[link] [comments]

Leave a Reply