Automate xBase Integration Tasks from PowerShell

The CData Cmdlets for xBase are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to xBase.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to xBase, but also an SQL interface; this tutorial shows how to use both to retrieve xBase data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for xBase. To access xBase data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for xBase.

Once you have acquired the necessary connection properties, accessing xBase data in PowerShell can be enabled in three steps.

The DataSource property must be set to the name of the folder that contains the .dbf files. Specify the IncludeFiles property to work with xBase table files having extensions that differ from .dbf. Specify multiple extensions in a comma-separated list.

PowerShell

1. Install the module:

   Install-Module xBaseCmdlets

2. Connect:

   $xbase = Connect-xBase -DataSource "$DataSource"

3. Search for and retrieve data:

   $class = "ASSET" $invoices = Select-xBase -Connection $xbase -Table "Invoices" -Where "Class = `'$Class`'" $invoices

   You can also use the Invoke-xBase cmdlet to execute SQL commands:

   $invoices = Invoke-xBase -Connection $xbase -Query 'SELECT * FROM Invoices WHERE Class = @Class' -Params @{'@Class'='ASSET'}

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for xBase\lib\System.Data.CData.xBase.dll")

2. Connect to xBase:

   $conn= New-Object System.Data.CData.xBase.xBaseConnection("DataSource=MyDBFFilesFolder;") $conn.Open()

3. Instantiate the xBaseDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Company, Total from Invoices" $da= New-Object System.Data.CData.xBase.xBaseDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.company $_.total }