You take photos on your camera, they are named something similar to:
DSC_0001.nef
DSC_0002.nef
...
DSC_0421.nef
During your photoshoot, your memory card becomes full so you swap cards. After your photoshoot your new card has photos with these names:
DSC_0001.nef
DSC_0002.nef
...
DSC_0141.nef
You want to rename the second batch of files to be renumbered, starting from 0422 ending at 0562.
I'm not sure what other photographers do, but since I also wear an SQL hat, I simply created this proc:
USE Admin
IF OBJECT_ID('dbo.cameraFileRenumber') Is Not Null
DROP PROC dbo.cameraFileRenumber
go
CREATE PROC dbo.cameraFileRenumber
@Path
varchar(500),
@Base
varchar(30),
@NumDigits
int,
@OffsetAdd
int,
@Debug
bit = 1
AS
/* DATE AUTHOR REMARKS
1/1/17 PPaiva Initial
creation.
DESCRIPTION
Renumbers
camera files with a numeric offset.
SCENARIO
You
do a photoshoot with a memory card. Photos
are numbered as such:
DSC_0001.nef to DSC_0421.nef (421 files)
Your
memory card fills up. You change memory
cards, the new photos are now numbered:
DSC_0001.nef to DSC_0141.nef (141 files)
You
have a problem - you can't put all the files into the same folder because they
start
counting from the same base number.
Therefore you have overlapping file names.
Solution: Rename the second set of files to start
counting at 0422.
Desired
end result: DSC_0001.nef to DSC_0562.nef (562 files)
USAGE
--
Debug
cameraFileRenumber
'K:\Images\Nikon\2017\2017-12-17_Elizabeth\batch2',
'DSC_',
4, 421
,
0
--
Execute
cameraFileRenumber
'K:\Images\Nikon\2017\2017-12-17_Elizabeth\batch2', 'DSC_', 422
DEBUG
xp_fixeddrives
xp_cmdshell
'
*/
SET NOCOUNT ON
DECLARE @s varchar(1000)
SET @s = 'dir /b "' + @Path + '"'
CREATE TABLE #t (
ID
int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
Filename varchar(30),
FilenameNoExt
varchar(30),
Extension
varchar(10),
sNumWithoutBase
varchar(10),
NumWithoutBase
int,
NewNumWithoutBase
int,
NewFilename
varchar(30),
RenameCommand
varchar(1100)
)
SET @Path = dbo.udfAddBackslashIfNec(@Path)
-- Get filenames into table
INSERT INTO #t (Filename)
Exec xp_cmdshell @s
DELETE
FROM #t
WHERE Filename Is Null
IF @Debug = 1
SELECT Filename,
CHARINDEX('.', Filename) Pos,
Left(Filename, CHARINDEX('.', Filename) - 1) FilenameNoExt,
Substring(Filename, CHARINDEX('.', Filename) + 1, 100 ) ExtOnly
FROM #t
UPDATE #t
SET FilenameNoExt = Left(Filename, CHARINDEX('.', Filename) - 1),
Extension
= Substring(Filename, CHARINDEX('.', Filename) + 1, 100 )
UPDATE #t
SET sNumWithoutBase
= Replace(FilenameNoExt, @Base, '')
UPDATE #t
SET NumWithoutBase = convert(int, sNumWithoutBase)
UPDATE #t
SET NewNumWithoutBase = NumWithoutBase + @OffsetAdd
UPDATE #t
SET NewFilename = @Base + dbo.udfPadLeft(NewNumWithoutBase, @NumDigits, '0') + '.' + Extension
UPDATE #t
SET RenameCommand = 'rename "' + @Path + Filename + '" "' + NewFilename + '"'
IF @Debug = 1
SELECT *
FROM #t
ORDER BY ID
DECLARE @MaxID int,
@ID
int,
@Cmd
varchar(1100)
SELECT @MaxID = Max(ID),
@ID
= 0
FROM #t
WHILE @ID < @MaxID
BEGIN
-- Get next ID
SELECT @ID = Min(ID)
FROM #t
WHERE ID > @ID
-- Get other attribs
SELECT @Cmd = RenameCommand
FROM #t
WHERE ID = @ID
IF @Debug = 0
Exec xp_cmdshell @cmd
Print @cmd
END
