Forum Discussion

ChrisC365's avatar
ChrisC365
Brass Contributor
Sep 24, 2020
Solved

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

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 se...
Help Wanted
  • hasanemresatilmis's avatar
    hasanemresatilmis
    Oct 09, 2020

     

    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])"
    }

     

farismalaeb's avatar
farismalaeb
Iron Contributor
Sep 24, 2020

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.

ChrisC365's avatar
ChrisC365
Brass Contributor
Sep 24, 2020

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

  • hasanemresatilmis's avatar
    hasanemresatilmis
    MCT
    Sep 25, 2020

     

    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Ş

    • ChrisC365's avatar
      ChrisC365
      Brass Contributor
      Oct 08, 2020

      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.

      • hasanemresatilmis's avatar
        hasanemresatilmis
        MCT
        Oct 09, 2020

         

        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])"
    }

         

  • farismalaeb's avatar
    farismalaeb
    Iron Contributor
    Sep 25, 2020

    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

Resources