/\/\o\/\/ PowerShelled
This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$PreviousItems = (" PowerShell : Getting Subdirectories using WMI part 3 "," PowerShell : Getting Subdirectories using WMI part 2 "," PowerShell : Getting Subdirectories using WMI "," PowerShell basic parameter checking of a function "," PowerShell Celsius Fahrenheit converting "," PowerShell : How Can I Rename Files Using File Nam... "," PowerShell make a drive of an UNC path "," Some Powershell News "," PowerShell Import and Export a DirectoryTree and S... "," PowerShell How Can I Query a Text File and Retriev... "," ")
Friday, May 26, 2006
PowerShell out-DataGrid update and more Dataset utilities
As I renamed the out-PropertyGrid function entry,as I wrongly titled it as out-DataGrid
as Abhiskek did an out-DataGrid function here : Abhishek's PowerShell Blog
and I already had one for Monad this did lead to some confusion so I renamed it but the link has also changed, this is the new one powershell-out-propertygrid-msh-view.html
as I did not convert my out-DataGrid yet (changing MSH to PS in 2 places)
I will also provide my version here, the diffence is that I as a Datagrid and Abhiskek did use a array.
my version is a bit bigger, but I like the datatable as Greg did notice in the comments the sort in the Datagrid will not work on an array, but does an the datagrid and also that I can combine the data from all kind of sources :(see working with CSV files in MSH (part one) and some of the links or seach for system.data on my blog to find more info.)
and as you can see here : MSH GUI Viewer for Dataset Example in some screenshots, so you can walk the relations you did make (only the datagridpart not the converting part for the rest the same as for the old focus hack)
I also have a script using only the convert to dateTable, that I will also post ,
and the helpers to get a CSV file or a Excel sheet into a dataTable ( some already posted )
so now you have the toolset to get together changed for PowerShell , and you can just past in the whole bunch and get working with CSV file Excel files DataTables and Combining them, and last but not least viewing them in a GUI.
a last remark note that the functions are made for commandline usage,
so you dot-source the connect funtions (otherwise csv files or excel sheets are listed but go out of scope after running the function and con not be used) :
then the functions just assume and take the connection object ($con or $xlsCon) as i explained in former posts.
so you work like this :
# takes directory name as parameter CSV and TXT files in directory are tables.
. connect-csv c:\Csvfiles
# takes Excel filename as parameter Sheets are datatables
. connect-Excel c:\ExcelFiles\file.xls
# after connecting you can just use the get-* functions
# get CSV file
$file = get-DataTable file#csv
# get XlsSheet (defaults to sheet1$ )
$file2 = get-xlsSheet
# make new dataset
$ds = new-object System.Data.dataSet
# merge the files
$ds.merge($file)
$ds.Merge($file2)
# show in GUI (no parameter just assumes there is a $ds object ;-) )
show-DataSet
# out-dataTable will convert command output to a dataTable ( you can merge again in the $ds)
$dt = gps | out-dataTable
. connect-csv c:\Csvfiles
# takes Excel filename as parameter Sheets are datatables
. connect-Excel c:\ExcelFiles\file.xls
# after connecting you can just use the get-* functions
# get CSV file
$file = get-DataTable file#csv
# get XlsSheet (defaults to sheet1$ )
$file2 = get-xlsSheet
# make new dataset
$ds = new-object System.Data.dataSet
# merge the files
$ds.merge($file)
$ds.Merge($file2)
# show in GUI (no parameter just assumes there is a $ds object ;-) )
show-DataSet
# out-dataTable will convert command output to a dataTable ( you can merge again in the $ds)
$dt = gps | out-dataTable
and here are the updates and utility scripts :
(note most parts are formerly posted on my blog and origional entrys contain more information about the workings, so if things are not clear try the links)
# Function out-datagrid
# shows piplineinput in a GUI using a datagrid
# /\/\o\/\/ 2006
# http:mow001.blogspot.com
Function out-dataGrid {
# Make DataTable from Input
$dt = new-Object Data.datatable
$First = $true
foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() | foreach {
If ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
ElseIf ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
Else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}
# show Datatable in Form
$form = new-object Windows.Forms.form
$form.Size = new-object System.Drawing.Size @(1000,600)
$DG = new-object windows.forms.DataGrid
$DG.CaptionText = "Number of Rows $($DT.rows.Count)"
$DG.AllowSorting = $True
$DG.DataSource = $DT.psObject.baseobject
$DG.Dock = [System.Windows.Forms.DockStyle]::Fill
$form.text = "$($myinvocation.line)"
$form.Controls.Add($DG)
$Form.Add_Shown({$form.Activate()})
$form.showdialog()
}
##########################################################
# Load CSV and Excel ADO helper functions
# connect to Excel File
function connect-Excel {
Param ($Path = "")
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$path;Extended Properties=Excel 8.0;"
$XlsConn = new-object System.Data.OleDb.OleDbConnection($connString)
$Xlsconn.open()
$Tables = $xlsConn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
$Tables |% {$_.TABLE_NAME}
}
# get a sheet from excel into a datatable
function get-xlsSheet {
Param ($name = "sheet1$")
$cmd = new-object System.Data.OleDb.OleDbCommand("Select * from [$name]",$xlsConn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt.TableName = $name
return $dt
}
# connect to a directory with CSV files :
function connect-csv {
Param ($Path = ".")
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=Delimited'"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$Tables = $Conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
$Tables |% {$_.TABLE_NAME}
}
# get CSV / txt file into a dataTable
function get-DataTable {
Param ($name)
$cmd = new-object System.Data.OleDb.OleDbCommand("Select * from [$name]",$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt.TableName = $name.split("#")[0]
return $dt
}
# show the $ds dataSet in a GUI
Function show-DataSet {
$form = new-object Windows.Forms.form
$form.Size = new-object System.Drawing.Size @(1000,600)
$DG = new-object windows.forms.DataGrid
$DG.CaptionText = "Number of Rows $($Ds.rows.Count)"
$DG.AllowSorting = $True
$DG.DataSource = $Ds.PSobject.baseobject
$DG.Dock = [System.Windows.Forms.DockStyle]::Fill
$form.text = "$($myinvocation.line)"
$form.Controls.Add($DG)
$Form.Add_Shown({$form.Activate()})
$form.showdialog()
}
# convert commandoutput to DataTable :
Function out-dataTable {
# Make DataTable from Input
$dt = new-object Data.datatable
$First = $true
foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() |% {
If ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
ElseIf ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
Else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}
return $dt
}
# shows piplineinput in a GUI using a datagrid
# /\/\o\/\/ 2006
# http:mow001.blogspot.com
Function out-dataGrid {
# Make DataTable from Input
$dt = new-Object Data.datatable
$First = $true
foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() | foreach {
If ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
ElseIf ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
Else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}
# show Datatable in Form
$form = new-object Windows.Forms.form
$form.Size = new-object System.Drawing.Size @(1000,600)
$DG = new-object windows.forms.DataGrid
$DG.CaptionText = "Number of Rows $($DT.rows.Count)"
$DG.AllowSorting = $True
$DG.DataSource = $DT.psObject.baseobject
$DG.Dock = [System.Windows.Forms.DockStyle]::Fill
$form.text = "$($myinvocation.line)"
$form.Controls.Add($DG)
$Form.Add_Shown({$form.Activate()})
$form.showdialog()
}
##########################################################
# Load CSV and Excel ADO helper functions
# connect to Excel File
function connect-Excel {
Param ($Path = "")
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$path;Extended Properties=Excel 8.0;"
$XlsConn = new-object System.Data.OleDb.OleDbConnection($connString)
$Xlsconn.open()
$Tables = $xlsConn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
$Tables |% {$_.TABLE_NAME}
}
# get a sheet from excel into a datatable
function get-xlsSheet {
Param ($name = "sheet1$")
$cmd = new-object System.Data.OleDb.OleDbCommand("Select * from [$name]",$xlsConn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt.TableName = $name
return $dt
}
# connect to a directory with CSV files :
function connect-csv {
Param ($Path = ".")
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=Delimited'"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$Tables = $Conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
$Tables |% {$_.TABLE_NAME}
}
# get CSV / txt file into a dataTable
function get-DataTable {
Param ($name)
$cmd = new-object System.Data.OleDb.OleDbCommand("Select * from [$name]",$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt.TableName = $name.split("#")[0]
return $dt
}
# show the $ds dataSet in a GUI
Function show-DataSet {
$form = new-object Windows.Forms.form
$form.Size = new-object System.Drawing.Size @(1000,600)
$DG = new-object windows.forms.DataGrid
$DG.CaptionText = "Number of Rows $($Ds.rows.Count)"
$DG.AllowSorting = $True
$DG.DataSource = $Ds.PSobject.baseobject
$DG.Dock = [System.Windows.Forms.DockStyle]::Fill
$form.text = "$($myinvocation.line)"
$form.Controls.Add($DG)
$Form.Add_Shown({$form.Activate()})
$form.showdialog()
}
# convert commandoutput to DataTable :
Function out-dataTable {
# Make DataTable from Input
$dt = new-object Data.datatable
$First = $true
foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() |% {
If ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
ElseIf ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
Else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}
return $dt
}
As most where mentioned before and not all had to be changed I still reposted them all as I hope this combination of scripts helps to see the complete picture of combining data in PowerShell using ADO (the samples are not complete as you can add Access and SQL also to the toolkit as you can see in other entries, but that a leave up to you to make the post not even bigger)
but I mean that you can just past all the code into your PowerShell console, pick a directory that contains CSV files ans an Excel sheet and follow the examples above and in the other posts and get experimenting.
also be sure the check the select samples in the CSV series to create new combined datasets and working with export-csv to safe them.
and follow the post about how to make relations and then use Show-DataSet again and see how you can walk the relations.
I use this a lot comparing information from different sources.
let me know what you think of this way to work with data in Powershell
Enjoy,
Greetings /\/\o\/\/
Tags : Monad msh PowerShell
