Finding a Character in Excel using [Regex]


I have a list of over 500 new cmdlets for Powershell and SharePoint. I wanted to put this list in an Excel spreadsheet for my own nefarious purposes. When I did that the  Cmdlet name somehow got a number added to the end of it. Like  so:

Backup-SPFarm12

Backup-SPSite13

Clear-SPLogLevel14

Clear-SPMetadataWebServicePartitionData15

I was wondering if there is a way, in Powershell to remove these numbers from the name. I tried a few things, with no luck.

Then I did some research online and found that I was over thinking the problem. I just need to split on the first number. But how?

 $Rng1 = $ws1.Cells.Item($i, 2).value()
    $a = $Rng1
    $b = [regex]::split($a,("\d"))  # (“\d”) is a number, so it splits on the first number in the string
    $b[0] # This is everything to the left of the first number
    $ws1.Cells.Item($i, 2).value() = $b[0] # This overwrites the contents of the cell with everything BUT the number

Here is the whole script:

 function Release-Ref ($ref) {
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
} 

$XLNormal = -4143
$xlPasteValues = -4163          # Values only, not formulas
$xlCellTypeLastCell = 11        # to find last used cell
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2
$xlYes = 1
$xlCellTypeLastCell = 11 

#Create an instance of the Excel.Application
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.open("C:\SharePoint Server 2010 PowerShell cmdlets.xlsx")
$ws = $wb.Worksheets.Item(1)
$ws1 = $wb.worksheets | where {$_.name -eq "sheet1"}
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row + 1
For($i = 8; $i -lt $row; $i++){
   Write-Progress -Activity "Searching $row #'s..." `
     -PercentComplete ($i/$row*100) -CurrentOperation `
     "$i of $Row to go" -Status "Please wait."     
   $Rng1 = $ws1.Cells.Item($i, 2).value()
    $a = $Rng1
    $b = [regex]::split($a,("\d"))
    $b[0]
    $ws1.Cells.Item($i, 2).value() = $b[0]
} 
[void]$ws1.cells.entireColumn.Autofit() 
$wb.Save()
Release-Ref $ws
Release-Ref $wb
$xl.Quit()
Release-Ref $xl
Advertisements

How do I check and Update a web.config file (XML)

I am working on a script to check for a particular node in a web.config file for the SharePoint Team.
The idea is to check if the node exist. If so, check to see if the request limits are set to “maxAllowedContentLength”,”83886080″
If so, all is well. If not set “maxAllowedContentLength”,”83886080″.
If the node does not exist, add it.
I am also logging my results and traping any errors. You may want to add or delete directories in the $xdir array. The -recurse function is an all or nothing sort of thing. Excluding directories is not supported. Hopefully in the next release!

BTW: if you copy this, you will need to remove the back ticks (`) from the begining of lines 73-79. I had to add them so WordPress would show the (Greater than, Less than) properly.

###### Start Posh Script ########

function write-log([string]$info){
if($loginitialized -eq $false){
#$FileHeader > $logfile
$script:loginitialized = $True
}
$info >> $logfile
}
#---------Logfile Info----------#
$script:UseInfo = $($(get-date -format MM/dd/yyyy-HH:mm:ss) + '`t' + $env:username + '`t')

Trap [Exception] {
write-log $('$UseInfo`t$_. - Line:(' + $($_.InvocationInfo.ScriptLineNUmber)+':'+$($_.InvocationInfo.OffsetInLine)+ ') ' + $($_.InvocationInfo.Line))
continue
}

$xdir = @('_app_bin','_vti_pvt','App_Browsers','App_GlobalResources','aspnet_client','bin','wpcatalog','wpresources')
$LogPath = Test-Path 'E:\LogFiles\ps\UpdateWebConfig' -pathType container
If ($LogPath) {
$script:logfile = 'E:\LogFiles\ps\UpdateWebConfig\WebConfig Update - $(get-date -format MMddyy-HHmmss).log'
$script:loginitialized = $false
} Else { New-Item 'E:\LogFiles\ps\UpdateWebConfig' -type directory
$script:logfile = 'E:\LogFiles\ps\UpdateWebConfig\WebConfig Update - $(get-date -format MMddyy-HHmmss).log'
$script:loginitialized = $false
}
$d = Get-Date -f MMddyy-HHmmss
Write-log '***Application Information***'
Write-log 'Filename: UpdateWebConfig.ps1'
Write-Log 'Created by: $env:username'
Write-Log 'Last Modified: $d'
Write-Log '***Application Information***'
Write-log

$ErrorActionPreference = 'SilentlyContinue'
$rl = '83886080'
#$wc = Get-ChildItem -Recurse C:\Scripts\xml -include *.config
$wc = Get-ChildItem -Recurse E:\inetpub\wwwroot\wss\VirtualDirectories -include web.config |
where {$xdir -notcontains (Split-Path $_.DirectoryName -Leaf -EA SilentlyContinue)}

$XML = New-Object xml

foreach ($item in $wc){
$wtc = gc $item | Select-String 'requestLimits' -quiet
If ($wtc) {
$XML.load($Item)
$wcToChange = $XML.configuration.'system.webServer'.security.requestFiltering.requestLimits.GetAttribute('maxAllowedContentLength')
If ($wcToChange -eq $rl) {
Write-Host '$item is OK' -ForegroundColor Green
write-log '$item is OK'
write-log
}
Else {
Write-Host '$item is NOT OK maxAllowedContentLength = $wcToChange' -ForegroundColor Red
write-log '$item is NOT OK maxAllowedContentLength = $wcToChange'
$XML.Save('$Item.bak-$d')
write-log 'Backup File: `t$Item.bak-$d'
$XML.configuration.'system.webServer'.security.requestFiltering.requestLimits.SetAttribute('maxAllowedContentLength','83886080')
$wtc = $XML.configuration.'system.webServer'.security.requestFiltering.requestLimits.GetAttribute('maxAllowedContentLength')
$XML.Save([Console]::Out)
$XML.Save($Item)
write-log '$item is NOW OK maxAllowedContentLength = $rl'
write-log }
} Else {
$XML.load($Item)
Write-Host '$item is NOT OK' -ForegroundColor Red
Write-Host 'Need to Add RequestFiltering' -ForegroundColor Red
write-log 'Need to Add RequestFiltering to web.config file'
$XML.Save('$Item.bak-$d')
write-log 'Backup File: `t$Item.bak-$d'
# new system.webServer node and child nodes
[Xml]$sweb = @'
`<system.webServer>
`<security>
`<requestFiltering>
`<requestLimits maxAllowedContentLength='83886080'/>
`</requestFiltering>
`</security>
`</system.webServer>
'@

# import and set the new system.webServer node
$newNode = $XML.ImportNode($sweb.'system.webServer', $true)
# append new system.webServer node
[Void]$XML.configuration.AppendChild($newNode)
# check (this displays the results on the screen)
$XML.Save($Item)
write-log $item
write-log `"requestLimits maxAllowedContentLength='83886080'"
write-log 'added to web.config file'
write-log
}
}

############# End POSH Script ###############