Home > Mobile >  Copying an Excel file and modifying the date to the next day (and looping this for multiple days)
Copying an Excel file and modifying the date to the next day (and looping this for multiple days)

Time:12-14

I am having difficulty implementing this in powershell. So I have a file called "FileName 12.14.2022.xlsx" which is in MM.DD.YYYY format in the filename.

I want to create a loop such that it will copy this file and create multiple copies of this file for future days. e.g. creating files with the filenames:

  • FileName 12.14.2022.xlsx
  • FileName 12.15.2022.xlsx
  • FileName 12.16.2022.xlsx
  • FileName 12.17.2022.xlsx

And I can set the loop counter to go as far forward as I want. Any ideas how I can do this using powershell?

I tried to extract the day month and year and adding an extra day to it, but kept throwing an error.

CodePudding user response:

This is what I came up with, It's not the best but it does the job(Hopefully)

$Path = "Enter path here"
$File = "FileName 12.14.2022.xlsx"

#Removes all of the stuff from the file name in a very dirty way
$Date = $File.Split("FileName"   " "   ".xlsx") | ? {$_.trim() -ne "" }
$DateTime = [datetime]::ParseExact("$($Date[0]) $($Date[1]) $($Date[2])", "M d yyyy", $null)

#Will ask how many times you want it to loops
#The $e variable is there to it will add days to the date
$i = Read-Host "Times to loop: "
$e = 1

#The loop that will make it all happen
1..$i  | % { 
    
    $AddingDate = $($([datetime]::ParseExact("$($Date[0]) $($Date[1]) $($Date[2])", "M d yyyy", $null)).AddDays($e)).ToString("MM.dd.yyyy")
    $NewFileName = "FileName $AddingDate.xlsx"
    Copy-Item "$Path$File" -Destination "$Path$NewFileName"
    $e = $e   1
}

CodePudding user response:

For this you can use a loop incrementing the date taken from the original sourcefile like below:

# get the FileInfo object from the original source file
$sourceFile = Get-Item -Path 'X:\ThePathTo\FileName 12.14.2022.xlsx'
# extract the date from the source filename (as string)
$sourceDate = ($sourceFile.BaseName -split '\s ')[-1]
# extract the filename prefix (in demo "FileName")
$sourcePrefix = ($sourceFile.BaseName -replace "${sourceDate}$").Trim()

# convert the $sourceDate string into a real DateTime object
$startDate = [datetime]::ParseExact($sourceDate, 'MM\.dd\.yyyy', $null)
# set the end date by adding X days to the original start date (for demo three weeks)
$endDate   = $startDate.AddDays(21)

# next, in a loop copy the file
while ($startDate -le $endDate) {
    $startDate = $startDate.AddDays(1)  # increment to next day
    # construct the new file name
    $newName   = '{0} {1:MM.dd.yyyy}{2}' -f $sourcePrefix, $startDate, $sourceFile.Extension
    $newFile   = Join-Path -Path $sourceFile.DirectoryName -ChildPath $newName
    $sourceFile | Copy-Item -Destination $newFile -WhatIf
}

P.S. I have added switch -WhatIf to the copy-item line so you can first see in the console what would happen. If you are satisfied this is correct, remove that -WhatIf switch and run the code again

  • Related