by rambog at 2013-03-22 07:51:50
I have a template in which I need to populate with server information. I am able to access the table but, for some reason, a couple of the columns only have one letter while a couple of others have the information intact. It appears that the columns where only partial information is contained is somehow wrapping the next letter onto the next row. I have mucked with the word warpping of the dotx file to now avail. I am looking at how to properly fit the server names into all the columns of my table. Any suggestions are welcome.by ArtB0514 at 2013-03-22 12:59:25
Here is the pertinent section of code:
#Now, let’s generate the surveys
#first, specify location under My Documents
$MyDocuments=[Environment]::GetFolderPath("MyDocuments")
#$DebugPreference="Continue"
#$ErrorActionPreference="Continue"
#The information will strictly arise from the csv file and not vmware
#gather the unique occurrances of mnemonics from csv file
$list=Import-csv serverreport.csv
$mnemonics=$list|select mnemonic|get-unique -AsString|foreach {$.Mnemonic}
Write-Debug "$mnemonics=$mnemonics"<br>#Step thru each Mnemonic in the CSV file and create a separate word doc<br>foreach ($app in $mnemonics) {<br>$word=new-object -ComObject "Word.Application"<br>#here is the template file to be used<br>$doc=$word.documents.Add("$MyDocuments\ServerInfo\ServerInventory.dotx")<br>Write-Debug "$app=$app"
#assemble all the VMs based upon their roles
$testserverlist=$list|where {$.Mnemonic -eq $app -and $.Role -eq ‘Test’}|select Servername|foreach {$.Servername}
Write-Debug "$testserverlist=$testserverlist"<br>Write-Debug "Number of test servers=$testserverlist.count"<br>$qaserverlist=$list|where {$_.Mnemonic -eq $app -and $_.Role -eq 'QA'}|select Servername|foreach {$_.Servername}<br>Write-Debug "$qaserverlist=$qaserverlist"
Write-Debug "Number of QA servers=$qaserverlist.count"
$prodserverlist=$list|where {$.Mnemonic -eq $app -and $.Role -eq ‘Production’}|select Servername|foreach {$.Servername}
Write-Debug "$prodserverlist=$prodserverlist"<br>Write-Debug "Number of Prod servers=$prodserverlist.count"<br>$drserverlist=$list|where {$_.Mnemonic -eq $app -and $_.Role -eq 'Disaster Recovery'}|select Servername|foreach {$_.Servername}<br>Write-Debug "$drserverlist=$drserverlist"
Write-Debug "Number of DR servers=$drserverlist.count"
#need to get the amount of rows needed in table from higer value of test,qa,prod or dr servers(used to expand the table, if necessary)
if ($testserverlist.count -ge $qaserverlist.count) {
$numberofrows=$testserverlist.count}
else {$numberofrows=$qaserverlist.count}
if ($numberofrows -lt $prodserverlist.count) {
$numberofrows=$prodserverlist.count}
if ($numberofrows -lt $drserverlist.count) {
$numberofrows=$prodserverlist.count}
$AppTeam=$doc.Bookmarks.Item("Text128").Range
$AppTeam.Text="$app"
$ATLperson=$doc.Bookmarks.Item("AppTechLead").Range
$ATLperson.Text=$List|where {$.Mnemonic -eq $app}|select ATL|get-unique -AsString|foreach {$.ATL}
#Insert the server information into table
$servertable=$word.ActiveDocument.Tables.item(1)
#The server entries begin at the 5th row of the table and the server name entries go from column 2 through 5 on each row
$tablerowposition=5
for ($j=1; $j -le $numberofrows; $j++) {
$servertable.Cell($tablerowposition,2).Range.Text=$testserverlist[$j-1]
$servertable.Cell($tablerowposition,3).Range.Text=$qaserverlist[$j-1]
$servertable.Cell($tablerowposition,4).Range.Text=$prodserverlist[$j-1]
$servertable.Cell($tablerowposition,5).Range.Text=$drserverlist[$j-1]
$tablerowposition=$tablerowposition + 1
#add rows if needed to accomodate larger server environments
if ($tablerowposition -ge 9) {
$servertable.rows.add()}
} #end creation of table for servers
#Finish the document and close
$doc.SaveAs([ref]"$MyDocuments\Serverinfo$app-test.docx")
$doc.Close()
$Word.quit()
} #end foreach Mnemonic
Here is what my output looks like (in the existing table within word):
Test Environment QA Environment Production Environment DR Environment
s server004 server001 s
e server005 server002 e
When it should look like the following:
Test Environment QA Environment Production Environment DR Environment
server00a server004 server001 server003
server005 server002
Your code is pretty hard to read as is. Can you use the PowerShell or Code buttons to help?by rambog at 2013-03-27 13:23:03
Are you sure that $TestServerList contains the correct information? Try using this code segment to verify:$testserverlist=@($list|where {$.Mnemonic -eq $app -and $_.Role -eq 'Test'}|select -ExpandProperty Servername)
"Number of test servers=$($testserverlist.Count)"
The @() forces the results to be an array, even if 0 or 1 objects are returned. Neither $Null nor a simple string have a Count property, so they will each return $null instead of 0 or 1.
To get a property of an object in a string, you need to tell PowerShell to evaluate it by enclosing the expression in $().
You solved my problem. In solving it, however, I discovered that servername would spill over into another section of the table in *.dotx. In other words, it appears to all be one table and I need the rows to be added after the first "section" (see section "A1" in attached *.jpg) and leave the other portion of the table alone.
Unfotunately, the place where I am working has blocked all photo sharing sites so I cannot show you what the table in the template looks like before and after the script.