Solved

Need Help with Powershell Script

Posted on 2014-01-31
7
398 Views
Last Modified: 2014-02-01
Greeting Experts,
      I need some help with a PowerShell script designed open a excel file, delete list of rows (until it hit a condition), and save it using the same file name.  To give a you a better idea, 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. The propose of the script is to 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) so I don’t have to do it manually.
What I am looking to do is to expand on this script and insert two additional columns (one named “region” and the second named “date”) in front of the first column (i.e. “A1”). After it has completed removing the unnecessary rows the script currently does now…. Then put in the Region No# Folder its located in (i.e. Region1, Region2, or Region3), and put in current date in to the 2nd column in the following format (i.e. YYYY_mm_dd)


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:\Region Reports\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")

$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) 

Open in new window

ExampleSpreadsheet.xlsx
ExampleSpreadsheet2.xlsx
0
Comment
Question by:amstoots
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 39825032
How do you want the script to decide Region 1, 2, 3 etc?
0
 

Author Comment

by:amstoots
ID: 39825059
Based on the Network folder name the excel documents are located in ...

i.e..."\\IP Address\Network Share\Region1\"

i.e..."\\IP Address\Network Share\Region2\"

i.e..."\\IP Address\Network Share\Region3\"
0
 
LVL 18

Accepted Solution

by:
Raheman M. Abdul earned 500 total points
ID: 39825229
Revised now:

Try this (this time complete code, tested on my side, working)
#-----------------------------------------------------------------------

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:\Region Reports\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)

$xlShiftToRight = -4161
$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)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Closing Comment

by:amstoots
ID: 39826367
The script works perfect ... thank you for help !!!!!!
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39826411
It shouldn't. $xlShiftToRight = -4161  is set only after it has been already used. It should not work for the first loop go.
0
 

Author Comment

by:amstoots
ID: 39826729
What do you mean .... Qlemo.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39826978
Move
$xlShiftToRight = -4161
inside the loop before e.g.
$objExcel = new-object -comobject excel.application
to have it work reliably. As-is, when the loop is running the very first iteration,
[void] $objRange.Insert($xlShiftToRight)
is run twice without $xlShiftToRight having a value. Then the value is set, and in the next loop iteration all is fine.

But I have to correct myself. It still works, though the code is not correct, because the default for Range.Insert with a entire column selected is to move the existing columns to the right, which is the effect you need.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
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 …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

13 Experts available now in Live!

Get 1:1 Help Now