How to update dbf file with powershell?

PowerShell Help
1

Hi At first I apologize for my English.

I am asking for help in solving my problem.

I have a simple script that reads data from the users.dbf file (via Microsoft.ACE.OLEDB.12.0).

For the user X it has to fill in the id_sys field

How do I update the users.dbf file?

=====================================

$conn = new-object System.Data.OleDb.OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CZM_users;Extended Properties=dBase IV”)
$conn.open()

$cmd = new-object System.Data.OleDb.OleDbCommand(“select * from users”, $Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
$da.fill($dt)

#$dt | select nazwisko,id_sys #etc

foreach ($i in $dt)
{
if ($i.nazwisko -eq “Majewska Beata”)
{
$i.ID_SYS = “MB”
}
}

??? # How to update the users.dbf file

$conn.Close()

========================================

 

Thx Adam

2

Take a look at this:

Not sure if the query is working for you, but the “Data Source” should reference the file and it’s just a path in your posted code.

3

Thank you,

I will try tomorrow and write about my results.

thx

4

Hi

Thank you @Rob Simmers

Your tip was very good.

Good job

My working script

#===================================


#sql query, generated earlier ...
$strQuery =
       "Update users set ID_SYS = 'ETFF' where NAZWISKO = 'Kowska Agnieszka'",
       "Update users set ID_SYS = 'HMSQ' where NAZWISKO = 'Kulka Elżbieta'"

$objConn = new-object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CZM_users;Extended Properties=dBase IV")

$objConn.open()

foreach ($i in $strQuery)
        {
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($i.sq)
$sqlCommand.Connection = $objConn

$sqlCommand.ExecuteNonQuery() | Out-Null
        }

$objConn.Close()

#================================

 

Thx

Adam