Solved

Need Help with Powershell Script

Posted on 2014-09-23
4
549 Views
Last Modified: 2014-09-23
Greeting Experts,
I need some help with a PowerShell script. The purpose of the script is to delete list a of rows, and two columns, and populate” File Name” in column A then “Creation Date” in column B (script below). What I want  to do is instead of putting the creation date in to column B insert the modified date in to the Date column… can someone give me a hand with this issue.  
Column A: File Name: Region1 <No need to change>
Column B Date: Creation Date < Need to change it to the documents modified date>


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 = $false 
$Lookupdir="U:\EPOReports\Region1\New folder\"    
$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) 

Open in new window

RawFile.xlsx
Final-Results.xlsx
0
Comment
Question by:amstoots
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 28

Expert Comment

by:Dan McFadden
ID: 40339234
The Last Modified info is referenced as:  LastWriteTime when viewed thru Get-ChildItem.

So just change this section of the script from this:

...   where { $_.Creationtime -gt $days_backdate }  ...

to

...  where { $_.LastWriteTime -gt $days_backdate }  ...

You can view all the file attributes by doing the following:
1. create an empty text file.  {example:  test.txt}
2. at PS console, run:  Get-ChildItem -Filter test.txt | select *

This will dump all the attributes for the test.txt file.  Next, add some text to the test.txt file and re-run the command in step #2.  You can see that the LastWriteTime has been updated.

Dan
0
 

Author Comment

by:amstoots
ID: 40339391
You may have miss understood the description of my problem.  What I am trying to do is instead of using the creation date of the document. I want to use the modified date of the document and have the script input that information in to column B (i.e. Date)  in the form of YYYY_mm_dd…


 Example
0
 
LVL 40

Accepted Solution

by:
footech earned 500 total points
ID: 40339677
If I'm understanding correctly, you would just need to change line 36 to:
$date = Get-Date $file.LastWriteTime -Format "yyyy_MM_dd"

Open in new window

0
 

Author Closing Comment

by:amstoots
ID: 40340633
footech - you hit it dead on... the string correction on Line 36 worked perfect..... thank you
0

Featured Post

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

627 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