First, I like the idea of using a 3rd party tool for this. For the people who aren't so comfortable in SQL, you can also do this manually with these steps:
1) Open a command prompt to your images folder
2) Type "dir /B > filenames.txt". This will write all the file names into a text file.
4) Import (or copy and paste) filenames.txt into Excel. Do your formula magic to concatenate/replace/etc until you have your new file name in another column.
5) Make a 3rd column with the same command that Chad mentioned - "rename {path to original files} original_filename new_filename" (you'll have a separate, full rename command for each file)
6) Copy the column in step 5 to a text file and save with a .bat extention (i.e., renameFiles.bat)
7) Run renameFiles.bat at a command prompt
Now all the files should have the new name. Test first of course.
You would still need to do a SQL step to rename TMS file names in the database. The SQL can be a bit daunting so here is a basic example to test first:
declare @filename nvarchar(255)
set @filename = '123.a.2021.jpg'
select @filename
-- result: 123.a.2021.jpg
select newfilename = replace((left(@filename, len(@filename) - charindex('.', reverse(@filename) + '.'))),'.','_') + right(@filename, charindex('.', reverse(@filename) + '.'))
-- result: 123_a_2021.jpg
-- note: also works with a file like '12345abc.jpg'
At this point you are looking at an update statement to MediaFiles that could seriously mess up your TMS (i.e., you would want to limit which paths to update), so I would probably reach out to GS. But let's say you are using a test database, or you live
life dangerously, it would look like this:
update [TMS].[dbo].[MediaFiles]
set FileName = replace((left(FileName, len(FileName) - charindex('.', reverse(FileName) + '.'))),'.','_') + right(FileName, charindex('.', reverse(FileName) + '.'))
where [PathID] = {the path where you changed the file names above}
Now your TMS file names match your "physical" file names.
Shoot me an email if you have questions. Thanks.
Steve
Steven
MOORE
Developer
& Database Administrator
Department
of Information Technology
The Museum of Modern Art
11 West 53 Street
New
York, NY 10019
212.708.9706
To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:External Email - Exercise Caution
Sorry to come late to the party, but you can do ALL of this in SQL on MS SQL Server. If you enable xp_cmdshell on MS SQL Server, you can execute DOS prompts (who remembers good old DOS prompts?) from within SQL. This means that you can use DOS to rename, copy, or move files.
SELECT @SQL = CONCAT('EXEC XP_cmdshell ''ren \\path\to\file\',@OldFile,' ',@NewFile,''', no_output ');
EXEC sp_executesql @SQL;
I usually load the candidates into a temp table, then within a loop, I extract the variables (including a variable for the new filename), use xp_cmdshell to rename the file, and then update the filename in the MediaFiles table.
As a bonus, if you have ImageMagick installed on the same server as your MS SQL Server instance, you can also execute ImageMagick commands with xp_cmdshell.
I have used this technique to rename Non-Morgan images with their loan numbers, to archive loan images into folders by year, to consolidate some legacy media into a central file folder, etc.
To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:External Email - Exercise Caution
Hi all,
Our institution has included our object ID as the filename for many years including the punctuation_2018.4.jpg etc. As we are starting to institute digital preservation policies and procedures, it was recommended to remove periods and add in an underscore and only have periods as part of the file extension. This would mean renaming the thousands of files on our servers and files in TMS. Has anyone performed a similar update or have any other recommendations?
Thanks,
Amanda
Amanda Shields
Associate Registrar
••••••••••••••••••••••••••••••••••••••••••••••
BRANDYWINE
RIVER MUSEUM
OF ART
P.O. Box 141
Chadds Ford, PA 19317
610.388.8134
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.