/\/\o\/\/ PowerShelled
This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$PreviousItems = (" working with CSV files in MSH (part one) "," and another source for MSH scripts is born "," VBscript hosting in MSH, Inputbox in Monad part 2 "," Some Monad Com hacks "," It's busy at the Monad front "," Monad Remoting "," How MSH Works "," Hosting an MSH runspace from Monad "," MSH get-WmiMethodHelp function Update 3 "," out-dataGrid function for Monad "," ")
Wednesday, March 29, 2006
working with CSV files in MSH (part two)
In this second part of this serie, I will do some more combining with the Dataset generated in part 1 : working with CSV files in MSH (part one)
this will get you back to the status we here last time,
You need the helper functions from last post loaded for this !
MSH>. connect-csv c:\CsvExample
TABLE_NAME
----------
ADComputers#csv
AdActive#csv
AdActive2#csv
Inventaris#csv
SmsResources#csv
TABLE_NAME
----------
ADComputers#csv
AdActive#csv
AdActive2#csv
Inventaris#csv
SmsResources#csv
Note that, as mentioned in the former post, you can connect all sorts of DB's(or datasources) using this method.
I made this function for commandline usage, I don't realy mind overwriting the $conn object, hence breaking the connection.
You can leave out the last 2 lines of the script, generating the tables list, and replace it by :
return $conn
then you can do this :
$CsvConnCsvExample = connect-csv c:\CsvExample
if you then also change the get-DataTable function to take $conn as a parameter, by changing this line :
Param ($name )
into
Param ($name , $conn)
now $conn is not in the $global scope but we need to provide it :
$AdActive = get-DataTable AdActive#csv $CsvConnCsvExample
but this makes us flexible with more connections, but for commandline usage I prefer the way I did them in the last post, but if you use them as script functions, the changes mentioned could be more handy.
as I get the dataTables offline (and just use export-csv to write), I don't bother, so I go ahead using the functions as the where in last post).
Only I added one column to the SmsResources.csv file to make next examples more clear.
name,ResourceID,Obsolete
PC42,62301,1
PC73,62370,0
PC64,62360,1
PC76,62380,0
PC80,62381,0
PC64,62390,0
PC42,62301,1
PC73,62370,0
PC64,62360,1
PC76,62380,0
PC80,62381,0
PC64,62390,0
after those changes we do a complete reload:
# just past this into the CLI to get back to the status we here last time
# You need the helper functions from last post loaded for this !!
. connect-csv c:\CsvExample
$AdActive = get-DataTable AdActive#csv
$Inventaris = get-DataTable Inventaris#csv
$SmsResources = get-DataTable SmsResources#csv
$ds = new-object System.Data.dataSet
$ds.merge($AdActive)
$ds.merge($Inventaris)
$ds.merge($SmsResources)
$parent = $ds.tables.item("ADActive").columns.item('name')
$child = $ds.tables.item("SmsResources").columns.item('name')
$rel = new-object System.Data.DataRelation("Ad_SMS",$Parent,$child,$false)
# You need the helper functions from last post loaded for this !!
. connect-csv c:\CsvExample
$AdActive = get-DataTable AdActive#csv
$Inventaris = get-DataTable Inventaris#csv
$SmsResources = get-DataTable SmsResources#csv
$ds = new-object System.Data.dataSet
$ds.merge($AdActive)
$ds.merge($Inventaris)
$ds.merge($SmsResources)
$parent = $ds.tables.item("ADActive").columns.item('name')
$child = $ds.tables.item("SmsResources").columns.item('name')
$rel = new-object System.Data.DataRelation("Ad_SMS",$Parent,$child,$false)
now we can go one where we were, getting only the records from the other table is easy :
name,ResourceID,Obsolete
PC42,62301,1
PC73,62370,0
PC64,62360,1
PC76,62380,0
PC80,62381,0
PC64,62390,0
PC42,62301,1
PC73,62370,0
PC64,62360,1
PC76,62380,0
PC80,62381,0
PC64,62390,0
to combine them we can use this :
$ds.tables.item("AdActive") | select name,logon,
@{e={$_.getchildrows("Ad_sms").count};n='count'},
@{e={$_.getchildrows("Ad_SMS") | foreach {$_.ResourceID}};n='ResourceID'},
@{e={$_.getchildrows("Ad_SMS") | foreach {$_.Obsolete}};n='Obsolete'} | ft -auto
@{e={$_.getchildrows("Ad_sms").count};n='count'},
@{e={$_.getchildrows("Ad_SMS") | foreach {$_.ResourceID}};n='ResourceID'},
@{e={$_.getchildrows("Ad_SMS") | foreach {$_.Obsolete}};n='Obsolete'} | ft -auto
Note the adding of Format-table (FT) at the and of the line, leave it out and you see why I did this ;-)
Copied to the shell (handy with Quick edit on ;-)) you get this result:
MSH>$ds.tables.item("AdActive") | select name,logon,
>> @{e={$_.getchildrows("Ad_sms").count};n='count'},
>> @{e={$_.getchildrows("Ad_SMS") | foreach {$_.ResourceID}};n='ResourceID'},
>> @{e={$_.getchildrows("Ad_SMS") | foreach {$_.Obsolete}};n='Obsolete'} | ft
-auto
>>
name logon count ResourceID Obsolete
---- ----- ----- ---------- --------
PC64 3/13/2006 7:49:31 AM 2 {62360, 62390} {1, 0}
PC80 3/20/2006 7:49:31 AM 1 62381 0
PC76 3/20/2006 8:14:59 AM 1 62380 0
PC79 3/20/2006 7:49:31 AM 0 {} {}
>> @{e={$_.getchildrows("Ad_sms").count};n='count'},
>> @{e={$_.getchildrows("Ad_SMS") | foreach {$_.ResourceID}};n='ResourceID'},
>> @{e={$_.getchildrows("Ad_SMS") | foreach {$_.Obsolete}};n='Obsolete'} | ft
-auto
>>
name logon count ResourceID Obsolete
---- ----- ----- ---------- --------
PC64 3/13/2006 7:49:31 AM 2 {62360, 62390} {1, 0}
PC80 3/20/2006 7:49:31 AM 1 62381 0
PC76 3/20/2006 8:14:59 AM 1 62380 0
PC79 3/20/2006 7:49:31 AM 0 {} {}
Ok Cofee and Chess waiting got to run again,
$ds.tables.item("AdActive") | select name,logon,
@{e={$_.getchildrows("Ad_sms").count};n='count'},
@{e={$_.getchildrows("Ad_SMS") | foreach {$_.ResourceID}};n='ResourceID'},
@{e={$_.getchildrows("Ad_SMS") | foreach {$_.Obsolete}};n='Obsolete'} | where {$_.obsolete -contains 0} | ft -auto
# save to a new CSV and show again
$ds.tables.item("AdActive") | select name,logon,
@{e={$_.getchildrows("Ad_SMS") | where {$_.obsolete -eq 0} | foreach {$_.ResourceID}};n='ResourceID'} | export-csv -no c:\CsvExample\AdSmsActive.csv
get-DataTable AdSmsActive.csv
@{e={$_.getchildrows("Ad_sms").count};n='count'},
@{e={$_.getchildrows("Ad_SMS") | foreach {$_.ResourceID}};n='ResourceID'},
@{e={$_.getchildrows("Ad_SMS") | foreach {$_.Obsolete}};n='Obsolete'} | where {$_.obsolete -contains 0} | ft -auto
# save to a new CSV and show again
$ds.tables.item("AdActive") | select name,logon,
@{e={$_.getchildrows("Ad_SMS") | where {$_.obsolete -eq 0} | foreach {$_.ResourceID}};n='ResourceID'} | export-csv -no c:\CsvExample\AdSmsActive.csv
get-DataTable AdSmsActive.csv
those are witout expl.
gr /\/\o\/\/
Tags : Monad msh
