Mass Update File Extensions

Summary:

When we import attachments from a customer legacy system, we sometimes use a mapping table that matches the customer to the file. This allows us to sometimes link attachments to customers that might not have a file extension, resulting in a 404 error when the customer tries to view the attachment in Cargas. This script will allow you to mass update files with no extension to have the extension specified. 

Environment:

• All CE Environments

Prerequisites:

• Attachments imported via legacy system
• Attachments linked to customers with no file extension

Notes and Precautions:

There are 2 main aspects of this PowerShell script. The first is meant to only grab the names of the files, along with the new file name with the file extension. The second script will actually adjust the files. The third script is meant to be run in SQL to update the files in bEntityFile to match the file explorer.

Also, the PowerShell scripts will look through all subfolders of the folder you are in, so you need to be pointing at the main attachments folder you need to adjust.

Script 1 (PowerShell, Grab List Only):

Get-ChildItem -Path $folder -Recurse -File | Where-Object { -not $_.Extension } |
    ForEach-Object {
        $newName = $_.FullName + ".pdf"
        Write-Output $newName
    }

Script 2 (PowerShell, Update File Extensions):

Change $newName to have a different file extension as needed

Get-ChildItem -Path $folder -Recurse -File | Where-Object { -not $_.Extension } |
    ForEach-Object {
        $newName = $_.FullName + ".pdf"
        Rename-Item -Path $_.FullName -NewName $newName
    }

Script 3 (SQL):

update bEntityFile
set [FileName] = concat([FileName], '.pdf')
where right([FileName], CHARINDEX('\', REVERSE([FileName]) + '\') - 1) not like '%.%'