Effortless File Organization: Automating File Copy with PowerShell and Excel

Are you tired of manually managing your files and folders? In this article, we’ll explore a straightforward PowerShell script designed to streamline your file organization process. This script copies files from one folder to separate folders at a different location based on information provided in an Excel workbook:

# Define source and destination folders, and Excel file path.
$sourceFolder = "C:\SourceFolder"
$destinationFolder = "C:\DestinationFolder"
$excelPath = "C:\File_List.xlsx"

# Import Excel data into a PowerShell object.
$excelData = Import-Excel -Path $excelPath

# Create dictionary to track copied files and avoid duplicates.
$copiedFiles = @{}

# Loop through each file in the source folder.
Get-ChildItem -Path $sourceFolder -File | ForEach-Object {
  # Get the base name (excluding extension) and full name (including extension) of the file.
  $fileName = $_.BaseName
  $fullName = $_.Name

  # Skip already copied files, checking both base and full name for duplicates.
  if ($copiedFiles[$fileName] -or $copiedFiles[$fullName]) {
    Write-Host "Skipping '$fullName' as it has already been copied."
    continue
  }

  # Check if a corresponding entry exists in Excel data, searching for either base or full name.
  $fileEntry = $excelData | Where-Object { $_.'Column1' -eq $fileName -or $_.'Column1' -eq $fullName }

  if ($fileEntry) {
    # Extract associated items from the "Column2" in Excel data.
    $items = $fileEntry.'Column2'

    # Create individual folders for each associated item.
    foreach ($item in $items) {
      # Create the target folder path.
      $targetFolder = Join-Path -Path $destinationFolder -ChildPath $item
      New-Item -Path $targetFolder -ItemType Directory -Force -ErrorAction SilentlyContinue

      # Copy the file to each item's folder.
      $destinationPath = Join-Path -Path $targetFolder -ChildPath $fullName
      Copy-Item -Path $_.FullName -Destination $destinationPath -ErrorAction SilentlyContinue
    }
  }

  # Mark both the base name and full name as copied to avoid duplicates.
  $copiedFiles[$fileName] = $true
  $copiedFiles[$fullName] = $true
}

Write-Host "Finished copying files."

Let’s break down the code step by step to understand how it works.
Setting the Stage: Defining Paths

# Define source and destination folders, and Excel file path.
$sourceFolder = "C:\SourceFolder"
$destinationFolder = "C:\DestinationFolder"
$excelPath = "C:\Workbook.xlsx"

Here, we set up the source folder containing our files, the destination folder where we want to organize them, and the path to an Excel workbook that holds our organizational data.

# Import Excel data into a PowerShell object.
$excelData = Import-Excel -Path $excelPath

We import data from the Excel workbook into a PowerShell object, allowing us to leverage it for efficient file management.

Avoiding Chaos: Preventing Duplicates

# Create dictionary to track copied files and avoid duplicates.
$copiedFiles = @{}

To ensure an organized system, we create a dictionary named $copiedFiles to keep track of files that have already been copied, preventing duplication.

The Main Act: File Looping and Copying

# Loop through each file in the source folder.
Get-ChildItem -Path $sourceFolder -File | ForEach-Object {
    # Get the base name and full name of the file.
    $fileName = $_.BaseName
    $fullName = $_.Name

    # Skip already copied files.
    if ($copiedFiles[$fileName] -or $copiedFiles[$fullName]) {
        Write-Host "Skipping '$fullName' as it has already been copied."
        continue
    }

    # Check for a corresponding entry in Excel data.
    $fileEntry = $excelData | Where-Object { $_.'Column1' -eq $fileName -or $_.'Column1' -eq $fullName }

    if ($fileEntry) {
        # Extract associated items from "Column2" in Excel data.
        $items = $fileEntry.'Column2'

        # Create folders for each associated item.
        foreach ($item in $items) {
            $targetFolder = Join-Path -Path $destinationFolder -ChildPath $item
            New-Item -Path $targetFolder -ItemType Directory -Force -ErrorAction SilentlyContinue

            # Copy the file to each item's folder.
            $destinationPath = Join-Path -Path $targetFolder -ChildPath $fullName
            Copy-Item -Path $_.FullName -Destination $destinationPath -ErrorAction SilentlyContinue
        }
    }

    # Mark both base name and full name as copied to avoid duplicates.
    $copiedFiles[$fileName] = $true
    $copiedFiles[$fullName] = $true
}

Here, the script iterates through each file in the source folder, checks for duplicates, and copies files to specified folders based on Excel data. The process ensures a well-organized destination folder.

Curtain Call: Finishing Touches

Write-Host "Finished copying files."

The script concludes by letting you know that the file copying process is complete. With this PowerShell script, you can efficiently manage and organize your files, saving time and effort in your daily workflow.