Rename Files On Disk With T-sql

I am using T-SQL I have a few excel files located here: C:\MyFiles\ I want to remove all the apostrophes in the file names in that directory. Now to remove apostrophes one would us

Solution 1:

SET NOCOUNT ON;

 CREATETABLE #FileList
    (
     FileID INTIDENTITY(1, 1)
    ,Line VARCHAR(512)
    )  

 CREATETABLE #temp
    (
     isFileThere BIT
    ,isDirectory BIT
    ,parentDirExists BIT
    )

 DECLARE@CommandVARCHAR(1024)
 ,  @RowCountINT
 ,  @counterINT
 ,  @FileNameVARCHAR(1024)
 ,  @FileExists BIT
 SET@Command='dir C:\MyFiles\  /A-D  /B'

 PRINT @CommandINSERT #FileList
        EXEC master.dbo.xp_cmdshell @CommandDELETEFROM #FileList
 WHERE  Line ISNULLSELECT@RowCount=COUNT(*)
 FROM   [#FileList]

 SET@counter=1

 WHILE ( @counter<=@RowCount ) 
    BEGINSELECT@FileName= [Line]
        FROM    [#FileList]
        WHERE   [FileID] =@counterSET@Command='C:\MyFiles\'+@FileName+''

 PRINT @CommandINSERT  [#temp]
                EXEC master.dbo.xp_fileExist @CommandSELECT@FileExists= [isFileThere]
        FROM    [#temp] 


        IF @FileExists=1AND CHARINDEX('''', @FileName) >0SET@Command='REN "C:\MyFiles\'+@FileName+'" "'+ REPLACE(@FileName, '''', '') +'"'ELSESET@Command=''SET@counter=@counter+1

        PRINT @Command

        IF LEN(@Command) >0EXEC master.dbo.xp_cmdshell @CommandENDDROPTABLE #FileList

 DROPTABLE [#temp]

Solution 2:

First you need to enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Then you can use sp_cmdshell to retrieve the files names from the directory

Declare@DirectoryTABLE (Files Varchar(MAX))
Declare@FileTABLE (Name varchar(MAX))
INSERTINTO@DirectoryEXEC XP_CMDSHELL 'DIR "D:"'Insertinto@FileSelect reverse(LEFT(reverse(Files),charindex(' ' ,reverse(Files)))) from@DirectorySelect*from@FILE

Now you get the file names in the table variable @FILE and use function like replace or your own custom function to replace apostrophes with the exact filename

Solution 3:

Try this it will work for you

1) Create a sp as mentioned below

CREATEPROCEDURE dbo.ListPathsXML
@FileSpecVARCHAR(2000),
@orderVARCHAR (80) ='/O-D',--sort by date time oldest first@xmlFileList XML OUTPUT

ASDECLARE@myfilesTABLE (MyID INTIDENTITY(1,1) PRIMARY KEY, FullPath VARCHAR(2000))
DECLARE@CommandLineVARCHAR(4000)
IF @orderISNOTNULL-- abort if the order is sillyBEGINSELECT@CommandLine=LEFT('dir "'+@FileSpec+'" /A-D /B /S '+@order,4000)
   INSERTINTO@MyFiles (FullPath)
       EXECUTE xp_cmdshell @CommandLineDELETEFROM@MyFilesWHERE fullpath ISNULLOR fullpath ='File Not Found'ENDSET@xmlFileList= (SELECT fullpath FROM@MyFilesFOR
                          XML PATH('thefile'),
                              ROOT('thefiles'),
                              TYPE)

2) and then give a name of directory where you want to replace the name of files

DECLARE@LotsOfText NVARCHAR(MAX),
       @iiINT,
       @iiMaxINT,
       @FileVARCHAR(2000),
       @Command NVARCHAR(4000)
DECLARE@filesTABLE (MyID INTIDENTITY(1,1) PRIMARY KEY, [Path] VARCHAR(2000))

DECLARE@FileList XML
EXECUTE ListPathsXML 'D:\QAconfig\',
    DEFAULT , @XMLFileList=@FileList OUTPUT

INSERTINTO@files(path)
   SELECT   x.thefile.value('fullpath[1]', 'varchar(2000)') AS [path]
        FROM@FileList.nodes('//thefiles/thefile') AS x ( thefile )
--don't look at the current errorlog!SELECT@ii=1, @iiMax=MAX(MyID) FROM@Files
WHILE @ii<=@iiMaxBEGINSELECT@File= [path] FROM@filesWHERE MyID=@ii
   print @FileSELECT@command='EXEC master..xp_cmdshell'+'''MOVE '+ Replace(@FILE,'''','''''') +' '+REPLACE(@FILE,'''','') +''''
   print @commandEXECUTE sp_ExecuteSQL @command--, N'@lotsOfText nvarchar(max) output ',@lotsoftext output SELECT@ii=@ii+1END