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 use code like this.

update MyTable
set FileName= replace(FileName, '''', '')

If I had all the file name in the DB it would be easy to do with the code above. But I need to update the file names that are located on disk.

How would I go about doing this with T-SQL?

It must be T-SQL because I need to add it to my existing code in my Stored Procedure.

回答1:

SET NOCOUNT ON;

 CREATE TABLE #FileList
    (
     FileID INT IDENTITY(1, 1)
    ,Line VARCHAR(512)
    )  

 CREATE TABLE #temp
    (
     isFileThere BIT
    ,isDirectory BIT
    ,parentDirExists BIT
    )

 DECLARE @Command VARCHAR(1024)
 ,  @RowCount INT
 ,  @counter INT
 ,  @FileName VARCHAR(1024)
 ,  @FileExists BIT
 SET @Command = 'dir C:\MyFiles\  /A-D  /B'

 PRINT @Command
 INSERT #FileList
        EXEC master.dbo.xp_cmdshell @Command

 DELETE FROM #FileList
 WHERE  Line IS NULL

 SELECT @RowCount = COUNT(*)
 FROM   [#FileList]

 SET @counter = 1

 WHILE ( @counter <= @RowCount ) 
    BEGIN
        SELECT  @FileName = [Line]
        FROM    [#FileList]
        WHERE   [FileID] = @counter 

        SET @Command = 'C:\MyFiles\' + @FileName + ''

 PRINT @Command

        INSERT  [#temp]
                EXEC master.dbo.xp_fileExist @Command

        SELECT  @FileExists = [isFileThere]
        FROM    [#temp] 


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

        SET @counter = @counter + 1

        PRINT @Command

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

    END 

 DROP TABLE #FileList

 DROP TABLE [#temp]

回答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 @Directory TABLE (Files Varchar(MAX))
Declare @File TABLE (Name varchar(MAX))
INSERT INTO @Directory
EXEC XP_CMDSHELL 'DIR "D:"'
Insert into @File
Select reverse(LEFT(reverse(Files),charindex(' ' ,reverse(Files)))) from @Directory
Select * 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

回答3:

Try this it will work for you

1) Create a sp as mentioned below

CREATE PROCEDURE dbo.ListPathsXML
@FileSpec VARCHAR(2000),
@order VARCHAR (80) = '/O-D',--sort by date time oldest first
@xmlFileList XML OUTPUT

AS
DECLARE @myfiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, FullPath VARCHAR(2000))
DECLARE @CommandLine VARCHAR(4000)
IF @order IS NOT NULL -- abort if the order is silly
   BEGIN
   SELECT @CommandLine =LEFT('dir "' + @FileSpec + '" /A-D /B /S '+@order,4000)
   INSERT INTO @MyFiles (FullPath)
       EXECUTE xp_cmdshell @CommandLine
   DELETE FROM @MyFiles WHERE fullpath IS NULL
       OR fullpath = 'File Not Found'
   END
SET @xmlFileList = (SELECT fullpath FROM @MyFiles
                         FOR
                          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),
       @ii INT,
       @iiMax INT,
       @File VARCHAR(2000),
       @Command NVARCHAR(4000)
DECLARE @files TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, [Path] VARCHAR(2000))

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

INSERT INTO @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<=@iiMax
   BEGIN
   SELECT @File= [path] FROM @files WHERE MyID=@ii
   print @File
   SELECT @command='EXEC master..xp_cmdshell' + '''MOVE '+ Replace(@FILE,'''','''''') + ' ' +REPLACE(@FILE,'''','') +''''
   print @command
   EXECUTE sp_ExecuteSQL @command--, N'@lotsOfText nvarchar(max) output ',@lotsoftext output 
   SELECT @ii=@ii+1
   END