Forum Discussion
Konstadinos Siskidis
May 12, 2024Copper Contributor
Renaming files in a directory using file names in excel.
I created a excel file named “New_file_names.xlsx” and “New_file_names.csv” Both files have the same content in column 1 and the information starts at A1 sheet1 They are in J:\temp directory. The f...
LeonPavesic
May 16, 2024Silver Contributor
Hello Konstadinos Siskidis,
this error occurs because the Cells.Item method is expecting a numeric index for the row and column, not a file name. Instead, you should read the values from the Excel file into a dictionary or a similar structure to map original file names to new file names.
# Specify the path to your Excel file
$excelFilePath = "J:\Temp\New_file_names.xlsx"
# Load the Excel file
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($excelFilePath)
$worksheet = $workbook.Worksheets.Item("Sheet1")
# Define source and destination directories
$sourceDirectory = "J:\temp2"
$destinationDirectory = "J:\temp3"
# Create a hash table to store the new file names
$fileNameMap = @{}
# Read the Excel file content into the hash table
$row = 1
while ($worksheet.Cells.Item($row, 1).Value() -ne $null) {
$originalFileName = $worksheet.Cells.Item($row, 1).Value()
$newFileName = $worksheet.Cells.Item($row, 2).Value()
$fileNameMap[$originalFileName] = $newFileName
$row++
}
# go through files in the source directory
Get-ChildItem -Path $sourceDirectory | ForEach-Object {
$file = $_
$originalFileName = $file.Name
$baseName = [System.IO.Path]::GetFileNameWithoutExtension($originalFileName)
$extension = $file.Extension
# Check if the original file name is in the hash table
if ($fileNameMap.ContainsKey($baseName)) {
$newFileName = $fileNameMap[$baseName]
# Create the full path for the destination file
$destinationFilePath = Join-Path -Path $destinationDirectory -ChildPath ($newFileName + $extension)
# Copy the file to the destination directory with the new name
Copy-Item -Path $file.FullName -Destination $destinationFilePath
Write-Host "Renamed and copied: $($originalFileName) -> $($newFileName + $extension)"
} else {
Write-Host "No mapping found for: $($originalFileName)"
}
}
# Clean up
$workbook.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
If your files or Excel rows are not in the same order, you’ll need a different approach to match the old and new file names. Please ensure that the order of the files in the directory and the order of the names in the Excel file are the same. If they are not, you might need to sort them first.
Konstadinos Siskidis
May 18, 2024Copper Contributor
Thank you for your response.
In the meantime, I ask a friend of mind and he help me using excel macros and work just fin.
But I am still wanting to understand this Powershell reminds me old dos coding.
please if you have time explain what you mean by dictionary, yes, the sequence in the directory is the same in excel one to one. what we did in excel we use the new file name from excel and use the extension name from say directory temp2 and put the combine file names in directory temp3.
let me know please.