1

Hi all I will be glad to get some help with windows batch script file to manipulate images, I am migrating from old software, it used to save the files in folder and each file had its own user id (e.g. 10050.jpg) I have like 1000 of these images, I would like to distribute the images to new folder with the image name and create SQL file to update the new software for example:

10050.jpg, 10051.jpg, 10052a.jpg, 10052b.jpg

Will go to:

/root/10050/10050.jpg
/root/10051/10051.jpg
/root/10052/10052a.jpg
/root/10052/10052b.jpg

And SQL file created:

update users set user_img = 10050/10050.jpg where user_id = 10050;
update users set user_img = 10051/10051.jpg where user_id = 10051;
update users set user_img_a = 10052/10052a.jpg where user_id = 10052;
update users set user_img_b = 10052/10052b.jpg where user_id = 10052;

Can anyone help me write a batch file to extract this information? I am a rank beginner at this. Thank you!

Manuel Allenspach
  • 12,467
  • 14
  • 54
  • 76
JavaSheriff
  • 7,074
  • 20
  • 89
  • 159
  • 1
    please edit your post, your first list has a 10053b, your second 10052b, the last has the id 10050 twice - so it's not clear at all what you want. Are the `id`s exactly (and always) 5 numeric digits? – Mat Apr 03 '11 at 10:38
  • I edited the post, sorry for the confusion,ID is always 5 numeric digits, some times user will heve few pictures then it will be 10052a.jpg and 10052b.jpg – JavaSheriff Apr 03 '11 at 14:19

2 Answers2

2

Given that the IDs in file names always consist of 5 digits, the algorithm could be like this:

  1. Take a file at the old location.

  2. Extract the leading 5 characters from the file name as the respective user's id.

  3. If there's no corresponding subfolder by the new root path, create it.

  4. Copy the file to the new location.

  5. Add the corresponding SQL script line to the SQL script file.

  6. Repeat steps 1..5 for all the relevant files.

And here's my attempt at an implementation:

SETLOCAL

SET "oldroot=X:\originalpath"
SET "newroot=Y:\newrootfolder"
SET "sqlscript=Z:\path\to\script.sql"

FOR %%F IN (%oldroot%\*.jpg) DO CALL :process "%%F"

ENDLOCAL
GOTO :EOF

:process
SET filename=%~nx1
SET userid=%filename:~0,5%

IF NOT EXIST "%newroot%\%userid%\" MKDIR "%newroot%\%userid%"
COPY %1 "%newroot%\%userid%"

>>%sqlscript% ECHO update users set user_img = '%userid%\%filename%' where user_id = %userid%
Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • @kurumi: Post that as a question and wait for the answers. :) I'm offering a solution that answers the requirements for this question, as stated by the OP. – Andriy M Apr 03 '11 at 15:12
  • EXCELLENT! i added SetLocal EnableDelayedExpansion to the top, and i changed the last line to append to the script [ECHO XXX >> %sqlscript%] and its working great! thank you! – JavaSheriff Apr 03 '11 at 18:53
  • I meant appending the line too, thanks for mentioning it, I fixed that in my script. As for your other change, I would really like to know why you would need that in a script like this. (I mean to fill in the gaps in my knowledge about delayed expansion, not to argue.) – Andriy M Apr 03 '11 at 19:17
1

If you have a choice, here's a Ruby for Windows script

require 'fileutils'
root="C:\\root"
o = File.open("sql.txt","w")
Dir["*.jpg"].each do| jpg|
    dirname = jpg.scan(/^(\d+)/)[0].first # get the nbumber
    Dir.mkdir(dirname) if not Dir.exists?(dirname) #make directory
    FileUtils.copy( jpg , root + "\\"+dirname) #copy / move
    o.write( "update users set user_img = #{dirname}/#{jpg} where user_id = #{dirname}\n" ); #create sql
end
o.close

I don't know why you are using *nix directory syntax when you are in windows, but change the root variable accordingly as you deem fit.

kurumi
  • 25,121
  • 5
  • 44
  • 52
  • Thank you, the script looks much complicated then batch script, I will try Ruby it and let you know. what is nix directory? – JavaSheriff Apr 03 '11 at 14:22
  • you have not seen a batch solution, so how do you know its complicated than batch? ;) – kurumi Apr 03 '11 at 14:23
  • that's much shorter than anything you could possibly do with a `.cmd` script. – Mat Apr 03 '11 at 15:01