Mike
asked on
Need to modify a Powershell Script
Greeting Experts,
I was wondering if would be possible to add on to existing PowerShell script used to modify excel spreadsheet. Currently the script works in two parts……First it deletes list of Summary Information until it reach the cell (in column A) with the following keyword “Event Category”. Once that is done the Script then inserts two columns and then adds the Name of the folder the file located at (i.e. Region 1, Region2, or Region3) in column A. Then plugs the date in to column B in the following format “yyyy_MM_dd” and saves the file.
What I would like to do is change the script from deleting the Summary information with “Number of Threat Events” and “Threat Names” is add the Threat Name to each of the Rows in order.. What do I mean?? Since the Number of “NO# Event Threats Events” Corresponds to number of rows under Each Cell marked “Event Category” (1 row for each no# threat Names that show up ) I want to add the “Threat Name” to each row it corresponds for each event. So there is one event “Threat Name1” under Threat name. That would go next to first row/record under the first “Event Category along with the Region no# and Date ( i.e. Threat Name would be the 3rd column called “Threat Name”)
There is one event “Threat Name2” for the 2nd “Event Category”… There is 3 events for “Threat Name3” for the 3rd Event Category”…. And so one…..
So basically in a nut shell what I want to do is instead of deleting both “Number of Threat Events” and “Threat Name”. I want to add the “Threat Name” as a 3rd column and add to the number of instances under each row that falls under Each “Event Category” ….. ..
ModifiedExclefile.csv
FinalExclefile.csv
I was wondering if would be possible to add on to existing PowerShell script used to modify excel spreadsheet. Currently the script works in two parts……First it deletes list of Summary Information until it reach the cell (in column A) with the following keyword “Event Category”. Once that is done the Script then inserts two columns and then adds the Name of the folder the file located at (i.e. Region 1, Region2, or Region3) in column A. Then plugs the date in to column B in the following format “yyyy_MM_dd” and saves the file.
What I would like to do is change the script from deleting the Summary information with “Number of Threat Events” and “Threat Names” is add the Threat Name to each of the Rows in order.. What do I mean?? Since the Number of “NO# Event Threats Events” Corresponds to number of rows under Each Cell marked “Event Category” (1 row for each no# threat Names that show up ) I want to add the “Threat Name” to each row it corresponds for each event. So there is one event “Threat Name1” under Threat name. That would go next to first row/record under the first “Event Category along with the Region no# and Date ( i.e. Threat Name would be the 3rd column called “Threat Name”)
There is one event “Threat Name2” for the 2nd “Event Category”… There is 3 events for “Threat Name3” for the 3rd Event Category”…. And so one…..
So basically in a nut shell what I want to do is instead of deleting both “Number of Threat Events” and “Threat Name”. I want to add the “Threat Name” as a 3rd column and add to the number of instances under each row that falls under Each “Event Category” ….. ..
function Release-Ref ($ref)
{
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
$objExcel = new-object -comobject excel.application
$xlShiftToRight = -4161
$objExcel.Visible = $true
$Lookupdir="\\ServerName\Region1\"
$days_backdate=$(Get-Date).AddDays(-1)
$files = Get-ChildItem $Lookupdir -Recurse | where-object {!($_.psiscontainer)} | where { $_.Creationtime -gt $days_backdate }
foreach ($file in $files)
{
$objWorkbook = $objExcel.Workbooks.Open($file.Fullname)
$objWorksheet = $objWorkbook.Worksheets.Item(1)
$i = 1
$cell=$objWorksheet.Cells.Item($i, 1)
Do {
If ( $cell.value() -ne "Event Category" )
{
$objRange = $cell.EntireRow
$objRange.Delete()
$i-=1
}
$i += 1
$cell=$objWorksheet.Cells.Item($i, 1)
}
While ($cell.value() -ne "Event Category")
$region=$file.FullName.Split("\")[-2]
$date = Get-Date -Format "yyyy_MM_dd"
$objRange = $objExcel.Range("A1").EntireColumn
[void] $objRange.Insert($xlShiftToRight)
$objRange = $objExcel.Range("A1").EntireColumn
[void] $objRange.Insert($xlShiftToRight)
$objWorksheet.Cells.Item(1,1) = "Region"
$objWorksheet.Cells.Item(1,2) = "Date"
$cell=$objWorksheet.Cells.Item($i+1, 3)
While ($cell.value() -ne $null)
{
$objWorksheet.Cells.Item($i+1,1)=$Region;
$objWorksheet.Cells.Item($i+1,2)=$Date;
$i += 1
$cell=$objWorksheet.Cells.Item($i+1, 3)
}
$objExcel.displayalerts=$false
$objExcel.activeworkbook.saveas($file.fullname)
$objExcel.Workbooks.Close()
$objExcel.Quit()
}
Release-Ref($objRange)
Release-Ref($objWorksheet)
Release-Ref($objWorkbook)
Release-Ref($objExcel)
0rginalExclefile.csvModifiedExclefile.csv
FinalExclefile.csv
ASKER
Thank you for the script you created…. It works just like I need it to…. But just for couple of thing… Is there a way to adjust the script to add the name of the Folder the excel file is located (i.e. Region1, Region2, or Region3). I have 3 different network shares called “Region1, Region2, and Region3” and based on the file name Vender Server pushes those reports out to each of those Folder Locations… Also Instead of dropping the data all in to one excel folders. Is it possible to have it save in each one of the excel files it opens….. ???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The Script works great, thanks for your help...
Please try the following.
I have have done it it a slightly different way to produce a csv file. this can then be loaded in to excel if requited.
I also removed the extra headers, i am not sure if you required them
Open in new window