Forum Discussion
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 file has file names.
In directory J:\temp2 they are files that I would like to rename using the information from the excel file without changing the file extensions.
After they are renamed I would like to store them in directory J;\temp3.
I have a script but is giving me errors I am including it
Any help will be much appreciated.
# Specify the path to your Excel file
PS J:\temp2> $excelFilePath = "J:\Temp\New_file_names.xlsx"
PS J:\temp2>
PS J:\temp2> # Load the Excel file
PS J:\temp2> $excel = New-Object -ComObject Excel.Application
PS J:\temp2> $workbook = $excel.Workbooks.Open($excelFilePath)
PS J:\temp2> $worksheet = $workbook.Worksheets.Item("Sheet1")
PS J:\temp2>
PS J:\temp2> # Define source and destination directories
PS J:\temp2> $sourceDirectory = "J:\temp2"
PS J:\temp2> $destinationDirectory = "J:\temp3"
PS J:\temp2>
PS J:\temp2> # Iterate through files in the source directory
PS J:\temp2> Get-ChildItem -Path $sourceDirectory | ForEach-Object {
>> $file = $_
>> $originalFileName = $file.Name
>>
>> # Retrieve the corresponding new file name from the Excel sheet
>> $cellValue = $worksheet.Cells.Item($originalFileName, 1).Formula
>>
>> # Extract the actual value from the formula (if it's a formula)
>> if ($cellValue -match '^=') {
>> $cellValue = $worksheet.Evaluate($cellValue)
>> }
>>
>> # Construct the full path for the destination file
>> $newFileName = Join-Path -Path $destinationDirectory -ChildPath ($cellValue + $file.Extension)
>>
>> # Copy the file to the destination directory
>> Copy-Item -Path $file.FullName -Destination $newFileName
>> Write-Host "Renamed and copied: $($originalFileName) -> $($newFileName)"
>> }
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
At line:6 char:5
+ $cellValue = $worksheet.Cells.Item($originalFileName, 1).Formula
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
Renamed and copied: 2x01 Back Off Mary Poppins-1.mkv -> J:\temp3\Two and a Half Man (2004)-S02E01-Back Off Mary Poppins.mkv
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
At line:6 char:5
+ $cellValue = $worksheet.Cells.Item($originalFileName, 1).Formula
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
# Clean up
PS J:\temp2> $workbook.Close()
PS J:\temp2> $excel.Quit()
PS J:\temp2> [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
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.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.
