How do I automate saving query result to .csv file in PI SQL Commander Lite?

I have a Product Batch Event Frame configured and running in PI AF. Also, I am able to successfully query for the results in PI SQL Commander Lite, but I need to automatically run the Query in PI SQL Commander Lite and save the result of the queries in .csv format at 11:59:59 PM on daily basis.

Parents

0 Community User over 1 year ago

Hi Kelvino ,

Here's a simple test code you can start from.

#Change 4 params
$AFServerHost="piafsrv1.dev.osisoft.int"
$AFDBName = "Configuration"
$query = "SELECT * FROM [System].[Connection].[ProductVersion]"
$outputLocation = [Environment]::GetFolderPath("Desktop") + "\"+ "out.csv"


# Build connection string and query
$connectionString = "Provider=PIOLEDBENT;Data Source=" + $AFServerHost + ";Initial Catalog=" + $AFDBName + ";Integrated Security=SSPI;"
$connectionString


# Test OLE DB Connection
$connection = $null
$connection = New-Object System.Data.OleDb.OleDbConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText=$query
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet)
$connection.Close()
$table = $dataset.Tables[0]


# Output Result
$table | Export-Csv -LiteralPath $outputLocation
#$table | Format-Table

Reply

0 Community User over 1 year ago

Hi Kelvino ,

Here's a simple test code you can start from.

#Change 4 params
$AFServerHost="piafsrv1.dev.osisoft.int"
$AFDBName = "Configuration"
$query = "SELECT * FROM [System].[Connection].[ProductVersion]"
$outputLocation = [Environment]::GetFolderPath("Desktop") + "\"+ "out.csv"


# Build connection string and query
$connectionString = "Provider=PIOLEDBENT;Data Source=" + $AFServerHost + ";Initial Catalog=" + $AFDBName + ";Integrated Security=SSPI;"
$connectionString


# Test OLE DB Connection
$connection = $null
$connection = New-Object System.Data.OleDb.OleDbConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText=$query
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet)
$connection.Close()
$table = $dataset.Tables[0]


# Output Result
$table | Export-Csv -LiteralPath $outputLocation
#$table | Format-Table

Children

No Data