Script to copy MS Excel files from one location to another and keep a log

[ChrisC365]

Hi 

Not sure if PowerShell is design for this, I was thinking back to the days when you would create a BAT file.  

I am wanting to automate the process of copying 2 MS Excel files; each has a separate defined name, the source name never changes but the files are used constantly so someone may have these files open.

1. Script to copy the two files regardless if they are open at source.

2. Save each file to a new location(could be two separate location for each) with a new filename using the following naming convention "dd-mm-yyyy - hhmmss - <Existingfilename>" ...etc 

3. Also, create a simple log or txt to record each file being transferred, log the new filename, outcome; fail or saved, date, time and file size when it was transferred.  Save the txt file to a local folder, so over time I will build up a record when files are transferred.

4. Is it possible to have a window or progress indicator which has information for the user to see how the operation is progressing.

5. A fail safe, if the transfer does not work alert the user and stop the process, plus if feasible log the failed outcome as per step 3.

Chris

[#2]

@ChrisC365 

With powershell, the Sky is the limit, but what you provide is not enough.

firrst of all, how can the script identify the file name if its automatically generated, is it in a fixed folder.

[#3]

@farismalaeb 

The files are not automatically generated, apology for any confusion, what I mean is I want to automate the process rather than me manually copying these files across, so for example : 

existing file name is "newProjects.xlxs", I want to copy this to a new location and rename the copy as  - "24-09-2020 - 100015 - newProjects.xlsx"

existing file name is "oldProjects.xlsx", I want to copy this to a new location and rename the copy as  - "24-09-2020 - 100015 - oldProjects.xlsx"

Then next day or week, I repeat the process again as above except the name changes - 

"01-10-2020 - 130014 - newProjects.xlsx"

"01-10-2020 - 130014 - oldProjects.xlsx"

Chris

[#4]

@ChrisC365 

$sourceFile="C:\MySource\lsasetup.log"
$DestFile="C:\MySource\"+(get-date).ToString("ddMMyyyy")+".bak"
$FailLogLocation="C:\MySource\Logcopy.txt"
try{
Write-Host "Copying the file, Please wait..."
Copy-Item $sourceFile -Destination $DestFile
}
Catch{
Write-Host "Ops, Failed"
Add-Content -Path $FailLogLocation -Value $error[0]
}

I try to make it as simple as possible

[#5]

Hi @ChrisC365 ,

The script will run correctly if you define the Source, Destination and Logs folders. Only files that cannot be copied and their errors will be in the Logs folder.

# Input Parameters
$Date = Get-Date -Format "dd-MM-yyyy-dddd HH-mm"
$SrcFiles = "C:\Source"
$Dstfiles = "C:\Destination\"
$Logs = "C:\Logs"

# Get Files On Source
$Files = Get-ChildItem -Recurse “$SrcFiles”

# Copy Files From Source to Destination
Foreach ($File in $Files)
{
    Try
    {
    Write-Host "Copying the $File" -ForegroundColor Yellow 
    Copy-Item -Path "$SrcFiles\$File" -Recurse -Destination "$DstFiles\$Date - $File" -ErrorAction SilentlyContinue
    Write-Host "Completed" -ForegroundColor Green
    }
    
    Catch
    {
    Write-Host "$File is not copied. Log File is in $Logs" -ForegroundColor Red
    Add-Content "$Logs\Log - $Date.txt" -Value “$File not copied because $($Error[0])”
    } 
}

I hope it will be useful for you.

Hasan Emre SATILMIŞ

[#6]

Hi @ChrisC365,

Did it work? 

[#7]

@hasanemresatilmis 

Hi Many thanks

The source folder has many files inside this location, I was just needing to extract two particular files, for example "First filename1.xls" and "Second Filename.xlsx". The script does copy the folder contents but its just two files I need.

[#8 · Accepted solution]

Hi @ChrisC365,

Could you try this?

# Input Parameters
$Date = Get-Date -Format "dd-MM-yyyy-dddd HH-mm"
$SrcFile1 = "filename1.xlsx"
$SrcFile2 = "filename2.xlsx"
$Srcfolder = "C:\Source\"
$Dstfolder = "C:\Destination\"
$Logs = "C:\Logs\"


# Copy Files From Source to Destination

    Try
    {
    Write-Host "Copying the $SrcFile1" -ForegroundColor Yellow 
    Copy-Item -Path "$SrcFolder\$SrcFile1" -Recurse -Destination "$DstFolder\$Date - $SrcFile1" -ErrorAction SilentlyContinue
    Write-Host "Completed" -ForegroundColor Green
    }
    
    Catch
    {
    Write-Host "$SrcFile1 is not copied. Log File is in $Logs" -ForegroundColor Red
    Add-Content "$Logs\Log - $Date.txt" -Value "$SrcFile1 not copied because $($Error[0])"
    } 

    Try
    {
    Write-Host "Copying the $SrcFile2" -ForegroundColor Yellow 
    Copy-Item -Path "$SrcFolder\$SrcFile2" -Recurse -Destination "$DstFolder\$Date - $SrcFile2" -ErrorAction SilentlyContinue
    Write-Host "Completed" -ForegroundColor Green
    }
    
    Catch
    {
    Write-Host "$SrcFile2 is not copied. Log File is in $Logs" -ForegroundColor Red
    Add-Content "$Logs\Log - $Date.txt" -Value "$SrcFile2 not copied because $($Error[0])"
    }