How to update dbf file with powershell?

[puad75 Participant Topics: 1 Replies: 2 Points: 20Rank: Member]

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

[Rob Simmers Participant Topics: 17 Replies: 1951 Points: 3,988Rank: Community Hero]

Take a look at this:

http://powershell-tips.blogspot.com/2012/07/reading-updating-and-inserting-data.html

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.

[puad75 Participant Topics: 1 Replies: 2 Points: 20Rank: Member]

Thank you,

I will try tomorrow and write about my results.

thx

[puad75 Participant Topics: 1 Replies: 2 Points: 20Rank: Member]

Hi

Thank you @Rob Simmers

Your tip was very good.

Good job

My working script

PowerShell

#=================================== #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() #================================

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

#===================================     #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

[Author]

Posts