About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Sunday, March 19, 2017

Rename files in a folder using T-SQL (generic)

SCENARIO
You have downloaded 50 files into a folder from somewhere like Facebook, and the files have gobbledygook names.  You want to rename the files with a base name + a number.

For example:
25488106_959991_6827072580628_o.jpg  >> FredAndWilma01.jpg
25488256_959951_1851503523073_o.jpg  >> FredAndWilma02.jpg

Here is a code snippet which I keep in a proc.  It simply generates the rename code that can be run from a command window, a batch file, or as I have done below, right here in T-SQL provided that you turn on xp_cmdshell.  For those that are security conscious, you can turn this on temporarily.


Use Admin
go

IF OBJECT_ID('dbo.cameraFileRenumberGeneric') Is Not Null
       DROP PROC dbo.cameraFileRenumberGeneric
GO
CREATE PROC dbo.cameraFileRenumberGeneric
AS
/*     DATE          AUTHOR        REMARKS
       1/1/17        PPaiva        Initial creation.

       DESCRIPTION
              Renumbers camera files with a numeric offset.
              Generates rename code for xp_cmdshell.

       SCENARIO
              You want to rename all files in a folder with a base name + a number
              Example:      25488106_959951922091_6828033757072580628_o.jpg  >> FredAndWilma01.jpg
                                   25488256_959965365151_1853783911503523073_o.jpg  >> FredAndWilma02.jpg
                                 
*/
RETURN -- This is a code snippet, not meant to run

-- DROP TABLE #t
CREATE TABLE #t(
       ID int NOT NULL IDENTITY(1, 1),
       Filename varchar(100)
       )

INSERT INTO #t (Filename)
       exec xp_cmdshell 'dir /b C:\Paul\CoCreativeJourneys\Clients\Past\2017-07-08_FredAndWilma\Images'

DELETE #t
WHERE Filename Is Null

SELECT *,              
              'Exec xp_cmdshell ''rename "C:\Paul\CoCreativeJourneys\Clients\Past\2017-07-08_FredAndWilma\Images\'
              + Filename + '" FredAndWilma'
              + dbo.udfPadLeft(ID, 2, '0') + '.jpg'''
FROM #t      


No comments:

Post a Comment