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:TempNew_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: 2×01 Back Off Mary Poppins-1.mkv -> J:temp3Two 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)
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 sheet1They 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 itAny help will be much appreciated. # Specify the path to your Excel file
PS J:temp2> $excelFilePath = “J:TempNew_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: 2×01 Back Off Mary Poppins-1.mkv -> J:temp3Two 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) Read More