/\/\o\/\/ PowerShelled

This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$AtomFeed = ("Atom.xml")
$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


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


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)


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


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


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 {6236062390} {10}
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


those are witout expl.

gr /\/\o\/\/

Tags :


Comments: Post a Comment

Links to this post:

Create a Link



<< Home

Archives

October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006  

$Links = ("PowerShell RC1 Docs"," PowerShell RC1 X86"," PowerShell RC1 X64"," Monad GettingStarted guide"," Monad Progamming Guide"," Monad SDK"," Monad videos on Channel 9"," MSH Community Workspace"," scripts.readify.net "," MonadSource"," www.reskit.net"," PowerShell Blog"," Under The Stairs"," computerperformance powershell Home"," proudlyserving"," MSH on wikipedia"," MSHWiki Channel 9"," Keith Hill's Blog"," Precision Computing"," PowerShell for fun"," MSH Memo (Japanese)"," monadblog")

find-blog -about "PowerShell","Monad" | out-Technorati.
find-blog -contains "","" | out-Technorati.
Google
 
Web mow001.blogspot.com

This page is powered by Blogger. Isn't yours?