Solved

Need Help with Powershell Script

Posted on 2014-09-23
4
527 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
  • 2
4 Comments
 
LVL 27

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

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

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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