Solved

Looking for a Script

Posted on 2014-01-30
10
260 Views
Last Modified: 2014-01-30
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
0
Comment
Question by:amstoots
  • 5
  • 5
10 Comments
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 39822079
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
 

Author Comment

by:amstoots
ID: 39822124
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
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 39822207
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
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 39822221
you mean look the .xlsx files created 7 days ago?
0
 

Author Comment

by:amstoots
ID: 39822237
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Accepted Solution

by:
Raheman M. Abdul earned 500 total points
ID: 39822310
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
 

Author Comment

by:amstoots
ID: 39822410
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
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 39822489
$objExcel.displayalerts=$false
$objExcel.activeworkbook.saveas($file.fullname)
0
 

Author Comment

by:amstoots
ID: 39822831
That did the trick .. thank you....  Raheman Mohammed Abdul
0
 

Author Closing Comment

by:amstoots
ID: 39822832
:)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now