I have below share and folders. For every month there will be a folder and there will be year folder prior to that as shown
Last Friday of every month there is excel sheet generated as shown below
End piece in file name "-14-26-48" remain same but prior pieces changes as per year and date. I would like to get this excel sheet path and name build in PowerShell. So how can i code so i get following path without manually entering complete path and filename. whenever i run new code i should get path and file name for last sheet which was generated last Friday of last month.
$oldbk = Import-Excel -Path '\\hcohesity05\cohesity_reports\2022\7\07-29\Cohesity_FETB_Report-2022-07-29-14-26-48.xlsx'
CodePudding user response:
If you just want the file from the last Friday of the previous month the easiest way to find it is not to build the path, but just search a month's folders and filter for files created on a Friday, sort by time created, and get the first one (once sorted the last folder created will be the first item).
$LastMonth = [datetime]::Now.AddMonths(-1).ToString('yyyy\\M')
$TargetFolder = Get-ChildItem (Join-Path '\\hcohesity05\cohesity_reports' $LastMonth) |Where{$_.CreationTime.DayOfWeek -eq 'Friday'}|Sort CreationTime|Select -first 1
$FilePath = (Resolve-Path (Join-Path $TargetFolder.FullName 'Cohesity_FETB_Report-*-14-26-48.xlsx')).Path
$oldbk = Import-Excel -Path $FilePath
CodePudding user response:
You can use the [DateTime]
type to help you out here.
# To do this, we create a new datetime,
# (using the current month and year, but the first day).
$endOfMonth = [datetime]::new(
[DateTime]::Now.Year,
[DateTime]::Now.Month,
1
).AddMonths(1).AddDays(-1)
# Then we add a month to it and subtract a day
# (thus giving us the last day of the month)
# Now we create a variable for the last Friday
$lastFriday = $endOfMonth
# and we keep moving backward thru the calendar until it's right.
while ($lastFriday.DayOfWeek -ne 'Friday') {
$lastFriday = $lastFriday.AddDays(-1)
}
# At this point, we can make the ReportPath, piece by piece
# ($BasePath would be the path until this point)
$ReportPrefix = "Cohesity_FETB_Report"
$ReportSuffix = "14-26-48.xlsx"
$ReportPath = Join-Path $BasePath $lastFriday.Year |
Join-Path -ChildPath $lastFriday.Month |
# A Custom format string will be needed for the month/day
Join-Path -ChildPath $lastFriday.ToString("MM-dd") |
Join-Path -ChildPath "$reportPrefix-$($lastFriday.ToString('YYYY-MM-dd'))-$reportSuffix"
$reportPath