We are proud to share our inclusion in the 2024 Gartner Magic Quadrant for Data Integration Tools. We believe this recognition reflects the differentiated business outcomes CData delivers to our customers.
Get the Report →Automate xBase Integration Tasks from PowerShell
Are you in search of a quick and easy way to access xBase data from PowerShell? This article demonstrates how to utilize the xBase Cmdlets for tasks like connecting to xBase data, automating operations, downloading data, and more.
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
-
Install the module:
Install-Module xBaseCmdlets
-
Connect:
$xbase = Connect-xBase -DataSource "$DataSource"
-
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
-
Load the provider's assembly:
[Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for xBase\lib\System.Data.CData.xBase.dll")
-
Connect to xBase:
$conn= New-Object System.Data.CData.xBase.xBaseConnection("DataSource=MyDBFFilesFolder;") $conn.Open()
-
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 }