asked on

Adjust Powershell script to go based on date stamp and not file name

I would like to adjust this script so that instead of looking for the .xls file which was previous to the one that was copied - based on the name of the file which should be the previous day - it should go by date stamp.

In other words if the copied file was 11,13,2020.xls, this would try to move the file 11,12,2020.xls to the PathTarget.

However what is 11,12,2020.xls does not exist? It would have to look for 11,11,2020.xls file to move. So I am thinking that perhaps this can be changed to sending the file previous to 11,13.2020.xls based on the date stamp.

Is this change possible?

$pathSource = "C:\Users\HainKurt\Documents\EE\"
$pathTarget = "C:\Users\HainKurt\Documents\EE\data\"

$Line1 = (Get-Content -path (-join ($pathSource,"INIFile.txt"))  -First 1)
#CopiedFile_Name=11,13,2020.xls

$FileName = $line1 -replace "CopiedFile_Name=", ""
#11,13,2020.xls

$DatePart = $FileName -replace ".xls",""
#11,13,2020

$FileDate = [datetime]::parseexact($DatePart, 'mm,dd,yyyy', $null).AddDays(-1)
$PreFileName = $FileDate.ToString("mm,dd,yyyy") + ".xls"

echo "Current File  : $FileName"
echo "Previous File : $PreFileName"

Move-Item -Path (-join ($pathSource, $PreFileName)) -Destination $pathTarget

Open in new window

HainKurt

not sure but this may work, not tested

$pathSource = "C:\Users\HainKurt\Documents\EE\"
$pathTarget = "C:\Users\HainKurt\Documents\EE\data\"

$Line1 = (Get-Content -path (-join ($pathSource,"INIFile.txt"))  -First 1)
#CopiedFile_Name=11,13,2020.xls

$FileName = $line1 -replace "CopiedFile_Name=", ""
#11,13,2020.xls

$DatePart = $FileName -replace ".xls",""
#11,13,2020

$FileDate = [datetime]::parseexact($DatePart, 'mm,dd,yyyy', $null).AddDays(-1)

$TargetFile = Get-ChildItem -Path $pathSource | Where-Object {$_.LastWriteTime -lt $FileDate} | Select-Object -First 1
Move-Item $TargetFile -Destination $pathTarget

Open in new window

HainKurt

here, tested

$pathSource = "C:\Users\HainKurt\Documents\EE\"
$pathTarget = "C:\Users\HainKurt\Documents\EE\data\"

$Line1 = (Get-Content -path (-join ($pathSource,"INIFile.txt"))  -First 1)
#CopiedFile_Name=11,13,2020.xls

$FileName = $line1 -replace "CopiedFile_Name=", ""
#11,13,2020.xls

$DatePart = $FileName -replace ".xls",""
#11,13,2020

$FileDate = [datetime]::parseexact($DatePart, 'mm,dd,yyyy', $null)
echo $FileDate 

$TargetFile = Get-ChildItem -Path $pathSource -filter *.xlsx -name| Where-Object {$_.LastWriteTime -lt $FileDate} | sort lastwritetime | Select-Object -First 1 

Move-Item (-join ($pathSource,$TargetFile)) -Destination $pathTarget

Open in new window

E=mc2

ASKER

Thanks HainKurt, however even your tested script is not working.
After much testing, for example even the the .ini quotes a file on November 15th, it then copies a file dated back to the 5th.. when it should copy a file just date stamped prior to this file, which would have been on the 14th..

HainKurt

attach your ini and the code used so I can look at it...
above code works fine on my machine...

HainKurt

you see line 15, I used xslx but you should change it to xls I guess :)

E=mc2

ASKER

Yes I caught the error with .xslx and I had already changed it...
I will provide the .ini file.

ASKER

and code you use?

I guess I did totally different thing :)
plus there was a issue with format

check this, adjust paths...
I am checking 30 days back in loop, you can increase or decrease this if you want...

$pathSource = "C:\Users\HainKurt\Documents\EE\xls\"
$pathTarget = "C:\Users\HainKurt\Documents\EE\data\"

$Line1 = (Get-Content -path (-join ($pathSource,"INIFile.ini"))  -First 1)
#CopiedFile_Name=11,13,2020.xls

$FileName = $line1 -replace "CopiedFile_Name=", ""
echo (-join ("File Name: ",$FileName))
#11,13,2020.xls

$DatePart = $FileName -replace ".xls",""
#11,13,2020

$FileDate = [datetime]::parseexact($DatePart, 'MM,dd,yyyy', $null)
echo (-join ("File Date: ",$FileDate))

For ($i=0; $i -le 30; $i++) {
  $FileDate = $FileDate.AddDays(-1)
  $FileName = $FileDate.ToString('MM,dd,yyyy')+".xls"
  echo (-join ("File to check : ",(-join ($pathSource,$FileName))))
  if (Test-Path (-join ($pathSource,$FileName))){
    echo (-join ("File found : ",(-join ($pathSource,$FileName))))
    Move-Item (-join ($pathSource,$FileName)) -Destination $pathTarget
    break
  }
}

Open in new window

E=mc2

ASKER

Thanks so much HainKurt, almost there :)
Now if there are single digit days, for example 11,9,2020.xls it won't move that file over.
I thought this was based on date stamp of the file, and not of the file name??
If it is based on date stamp, it's not working once it starts to his the single digit days..

HainKurt

hmms...
should we check both?

ie

11,9,2020.xls
11,09,2020.xls

or is it always no 0 padded?

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial