How to Add Extensions to Respective File Types

There are two types of files; TIF, & TXT stored in two different (Windows Server 2008 and a Networked Attached Storage) servers, shares, and folders without any extensions. Files will be copied over to another storage area. Approximately 92 million files. Any of the two different file types could be in any of the folders. Examples given below:

ServerName\ShareName\FolderName\FileName
1.\app_hpf\fcfs_data\HPF01\Arch037\1767\144
2.\app_hpf\fcfs_data\HPF01\Arch037\1767\146

SQL server database has a view, which has columns for the file type and UNC for each respective file in addition to other columns that are needed for the reports. Using a case statement, I am able to add the extensions to UNC path as shown in the two examples below:

Examples of UNC with extensions added using a case statement

case when filetype = 'PAG' THEN UNC + '.TIF' when filetype = 'CLD' THEN UNC + '.TXT' END

1.\app_hpf\fcfs_data\HPF01\Arch037\1767\144.TXT
2.\app_hpf\fcfs_data\HPF01\Arch037\1767\146.TIF

Now I want to be able to add extensions to these files so that when users click on the UNC path in the reports they are able to automatically open the file instead of selecting an application to open it.

What are the different and/or best way to accomplish this task? Can we write some sort of a script, encompassing xp_cmdshell, which basically loops through the records in the sql server view to identify the file type and therefore rename the files in the servers by adding the appropriate extensions.

I look forward to your help in this regard.

Thanks!

this will generate the RENAME command. Execute it in xp_cmdshell to rename the file

SELECT  cmd  = 'REN ' 
             + UN + ' '
             + RIGHT(UNC, CHARINDEX('\', REVERSE(UNC)) - 1)
             + case when filetype = 'PAG' THEN '.TIF' when filetype = 'CLD' THEN '.TXT' END
FROM     youtable
1 Like

Thanks khtan for the quick response. Could you please explain to me what is happening in this rename command and how to execute it in xp_cmdshell?

P.S. I have never done such a thing before, therefore, I am sort of lost.

will it loop through the entire 92 million files in the storage and add the extensions to them based on the file type listed in the sql server view/table?

that basically generate the RENAME command, like what you type in command prompt

REN old_filename new_filename.ext

that just generate the command. You will still need to execute it using xp_cmdshell

exec xp_cmdshell @cmd`

xp_cmdshell will not be able to handle all 92 mil of command in one go. You will need to do it in batches

1 Like

USE varianenm;
GO
DECLARE @CMD NVARCHAR(255)

Select @CMD = 'REN' + UNC + ' '
+ RIGHT(UNC, CHARINDEX('', REVERSE(UNC)) - 1)
+ case when Filetype = 'TIF' THEN '.TIF' when Filetype = 'TXT' THEN '.TXT' END

from HPF_Test
GO

exec xp_cmdshell @CMD

When I run the above script I get an error saying

"Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@CMD".

Could anyone please help me identify what is going on?

It ran fine using the script below:

USE varianenm;

DECLARE @CMD VARCHAR(255);

Select @CMD = 'REN' + UNC + ' '
+ RIGHT(UNC, CHARINDEX('', REVERSE(UNC)) - 1)
+ case when Filetype = 'TIF' THEN '.TIF' when Filetype = 'TXT' THEN '.TXT' END

from HPF_Test;

exec xp_cmdshell @CMD;

However, the output is "Access is denied" & NULL. What could this possibly mean?

display the command before you execute it

PRINT @CMD
exec xp_cmdshell @CMD

you will see that you missed once space after the REN

Thank you very much sir/madam for your help in this regard.

-Ahmedamm

Final solution is given below:

DECLARE @UNC AS varchar(255), @Filetype AS varchar(255), @CMD AS VARCHAR(255)

declare @HPF_Test as cursor;

SET @HPF_Test = CURSOR FOR

select UNC, Filetype
from HPF_Test;

OPEN @HPF_Test;
Fetch NEXT from @HPF_Test into @UNC, @Filetype;
While @@fetch_status=0
begin
SELECT @CMD = 'REN ' + @UNC + ' '

  • RIGHT(@UNC, CHARINDEX('', REVERSE(@UNC)) - 1)
  • case when @Filetype = 'TIF' THEN '.TIF' when @Filetype = 'TXT' THEN '.TXT' when @Filetype = 'XML' THEN '.XML' END
    FROM HPF_Test
    exec master..xp_cmdshell @CMD
    Fetch next from @HPF_Test into @UNC, @Filetype
    end

close @HPF_Test;
deallocate @HPF_Test;

Follow-up question given below:

Since there are approximately 92 million images that will need to be renamed it will take time for the whole process to run and complete. Therefore, I have a few questions that are listed below:

  1. What can be added to the script to test whether all files were copied or not?

  2. khtan suggested that we run the script in batches. How can I break it into batches?

Please help me in this regard. I am a novice in this regard.

Thanks!