Looking for a Script

Greeting Experts,
      I am in need of a script to delete a set of rows in group of excel spread sheets until hits a condition (i.e. such as a keyword). What do I mean….  I receive several attachments once a week from our Antiviruses Management server with the states of daily infections. The excel files come in two parts. One is the list of malware, Trojans, and viruses names (along with no# of detections of each threat) and 2 is a Drill down detail list of each event.
What I am looking to do is dynamically strip out the list of Threats until I get the First cell of the Drill down list in to cell “A1” ( i.e. Event Category highlighted in yellow). None of the excel files have a fixed number of rows for both parts (Meaning the Number of Threat Events could be longer or shorts from one day to the next.  
I am looking for a script to scan theses excel files and strip out the unnecessary data and leave the drill down detailed information starting at Cell “A1” with the keyword of “EventCategory”   . I have attached example spreadsheet that give an example of the files I receive weekly..
ExampleSpreadsheet.xlsx
MikeSecurityAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Raheman M. AbdulConnect With a Mentor Senior Infrastructure Support Analyst & Systems DeveloperCommented:
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
$objExcel.Visible = $false

$Lookupdir="C:\temp"    
$days_backdate=$(Get-Date).AddDays(-7)    
 
$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")

$objExcel.ActiveWorkbook.SaveAs("c:\temp\myfile4.xlsx")
$objExcel.Workbooks.Close()
$objExcel.Quit()
}

Release-Ref($objRange)
Release-Ref($objWorksheet)
Release-Ref($objWorkbook)
Release-Ref($objExcel)
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Try this:

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
$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Open("C:\temp\ExampleSpreadsheet.xlsx")
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$i = 1
Do {
    If ($objWorksheet.Cells.Item($i, 1).Value() -ne "Event Category"  )
      {
            $objRange = $objWorksheet.Cells.Item($i, 1).EntireRow
            $objRange.Delete()
            $i-=1
      }
      $i += 1
}
While ($objWorksheet.Cells.Item($i,1).Value() -ne "Event Category" )

$objExcel.ActiveWorkbook.SaveAs("c:\temp\myfile.xlsx")
$objExcel.Workbooks.Close()
$objExcel.Quit()

Release-Ref($objRange)
Release-Ref($objWorksheet)
Release-Ref($objWorkbook)
Release-Ref($objExcel)
0
 
MikeSecurityAuthor Commented:
The Script works perfect .... Is there a way to have it look at the past 7 days and do the same thing on each spreadsheet (in silent mode)... ?>
0
NEW Internet Security Report Now Available!

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out this quarters report on the threats that shook the industry in Q4 2017.

 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Optimized code: (faster) and silent mode

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
$objExcel.Visible = $false
$objWorkbook = $objExcel.Workbooks.Open("C:\temp\ExampleSpreadsheet.xlsx")
$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")

$objExcel.ActiveWorkbook.SaveAs("c:\temp\myfile4.xlsx")
$objExcel.Workbooks.Close()
$objExcel.Quit()

Release-Ref($objRange)
Release-Ref($objWorksheet)
Release-Ref($objWorkbook)
Release-Ref($objExcel)
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
you mean look the .xlsx files created 7 days ago?
0
 
MikeSecurityAuthor Commented:
yes... Need to have it look at example.csv files for the past seven days... based on the creation date (Located  in the properties) of each document ...
0
 
MikeSecurityAuthor Commented:
Is there a way to  save the file as it current file name instead of using "MyFile4.xlsx" file name.. Just remove the unnecessary rows and then save it w/o moving it to new excel document.


$objExcel.ActiveWorkbook.SaveAs("c:\temp\myfile4.xlsx")
$objExcel.Workbooks.Close()
$objExcel.Quit() 

Open in new window

0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
$objExcel.displayalerts=$false
$objExcel.activeworkbook.saveas($file.fullname)
0
 
MikeSecurityAuthor Commented:
That did the trick .. thank you....  Raheman Mohammed Abdul
0
 
MikeSecurityAuthor Commented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.