Solved

Need Help with Powershell Script

Posted on 2014-01-31
7
400 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 19

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 19

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Closing Comment

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

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 69

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
upadting roles in SQL/oracle 8 21
Powershell XML in variable 4 20
Shutdown Inactive Server- Script 4 18
text file manipulation 2 17
Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
This article will help you understand what HashTables are and how to use them in PowerShell.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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