Smart .bat file badly needed to find and copy files.

October 15, 2018 at 03:22:37
Specs: Macintosh
PC - Windows 10...

Sorry, I'm no code cutter and my 'basic' skills have been on hold for some time, so I really need some help here to run a (hopefully simple) .bat file to find, compare and copy files from one folder to another.

The setup...
I have an Excel spreadsheet that has approx 740 unique part numbers (for car parts) in a list/column (but each part number is a number only which does not have/include a '.jpg' suffix).

I also have a folder with approx over 14,000 '.jpg' files that have file names (plus the '.jpg' suffix) that may match some of these Excel part numbers.

I want to take the 740 unique part numbers (that do not have a '.jpg' suffix) from the Excel file and run a script that checks/finds whether each Excel number has a match in the 14,000 '.jpg' files folder and if so, to then copy or move that '.jpg' file to a new folder so it reduces the picture files down to only the ones needed to be viewed and checked for possible use.

Can this be done with a simple batch file and if so, would someone out there be able to suggest or help to get this to work.

Many thanks, lexip

message edited by lexip


See More: Smart .bat file badly needed to find and copy files.

Reply ↓  Report •

#1
October 15, 2018 at 03:54:03
read post #2 first :)

Can be done.

You will need to convert the file to .csv for MS-DOS which can be done with excel like this:
select file --> save as --> select the location and name & in the "save-as" drop down menu select CSV (MS-DOS)

I need a location(full path) of the folder where the 14000 .jpg are stored
The location of the new .csv file
The folder where i should be making new folders
And make a backup of the folder with 14000 pictures and place it somewhere safe just incase i manage to screw up somehow

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz

message edited by hidde663


Reply ↓  Report •

#2
October 15, 2018 at 06:11:03
No need for a .bat file, it can be done very easily within Excel using the FILES() function and a Named Range.

First,
get the complete path name to the folder holding your 1400 jpg's something like
C:\Users\Mike\Pictures\*.jpg

In your worksheet, place this path name into cell A1

Next,
Select Formula Tab
Select Define Name
In the Name box enter: jpg_namelist
In the Scope box make sure it reads Workbook
In the Refers To box enter: =FILES(Sheet1!$A$1)
Press OK

Next, in cell A2 enter the formula:

=IFERROR(INDEX(jpg_namelist,ROW()-2),"")

Drag down 1400 rows and you should have all your filenames in column A.

Select all 1400 rows and do a Copy/Paste/Values and
now you can either add the .jpg extention to your 740 names or use
the Text To Column function to break apaprt your 1400 names into name and extension.

Combine your 740 to the 1400 in a new column
and use the Remove Duplicte function
and your done.

See how that works for you.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#3
October 15, 2018 at 06:23:05
I'll leave it to you mmcconaghy, doing it nativly is way better

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz


Reply ↓  Report •

Related Solutions

#4
October 15, 2018 at 06:24:30
Just so you know, the FILES() function is an XLM 4.0 legacy holdover
and works up to Excel 2007, not sure about new versions.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
October 15, 2018 at 06:29:42
hidde663

I have a .bat file from my last job that does nothing but get filenames and sizes of jpgs, gifs, tiffs
that I would then have to paste into an Excel sheet, was very happy to find the above solution.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#6
October 15, 2018 at 07:51:55
Updated formula:

If you want ONLY filenames without the .jpg extension then use this formula in cell A2:

=IF(ISERROR(INDEX(jpg_namelist,ROW()-2)),"",LEFT(INDEX(jpg_namelist,ROW()-2),SEARCH(".",(INDEX(jpg_namelist,ROW()-2)),1)-1))

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#7
October 15, 2018 at 13:15:56
Morning gang, sorry for no reply but I was catching zzzz's.

Thank you for such a quick reply, I'm very grateful and I'll try this at work when I get there, but... a couple of things to just make sure I'm heading in the right direction.

First up, it's 14,000 jpg files in a folder, not 1,400 that I need to look at, sort and copy (some) to a new folder.

And just to clarify, I need a script or batch file that will look at a column/list of 740 data entries in an Excel spreadsheet (that do not have a jpg suffix) - with each entry being a mix of letters and numbers and between 4 - 10 characters/letters long - and compare these individual entries to all the 14,000 jpg files sitting in a separate folder, looking for a match in the filename.

If a match is found, then I need the script to move or just copy that jpg file into a fresh folder.

I'm also using a recent version, I think it's 2016 Excel if that's of any help too.

So does that clarify or change things??

Thanks for your help so far... lexip


Reply ↓  Report •

#8
October 15, 2018 at 13:50:27
My Excel solution cannot move or copy the files from folder to folder for you,
but can tell you which files need to be copied/moved, so it is not a complete
solution for you.

Perhaps hidde663 can jump back in and offer a batch solution that covers
all of your requests.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#9
October 15, 2018 at 15:01:42
Will do tomorrow, I do not have time right now, check back in about 12 hours

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz


Reply ↓  Report •

#10
October 15, 2018 at 15:36:45
Thanks gang, really appreciated (;->

Reply ↓  Report •

#11
October 15, 2018 at 18:23:50
Sounds like pretty simple VBA. Quick and dirty example:
Sub a()
  Const dir = "C:\Temp"
  Dim parts As Range, fso As Object, dest As String
  Set parts = Range(ActiveCell, ActiveCell.EntireColumn.End(xlDown))
  Application.StatusBar = "Searching range " & parts.Address(False, False)
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  With fso.GetFolder(dir)
    dest = fso.BuildPath(dir, "Found")
    If Not fso.FolderExists(dest) Then _
      .SubFolders.Add ("Found")
    For Each f In .Files
      If Not parts.Find(fso.GetBaseName(f), , xlValues, xlWhole, , , False) Is Nothing Then _
        f.Copy fso.BuildPath(dest, f.Name)
    Next 'f
  End With
  Application.StatusBar = False
End Sub

How To Ask Questions The Smart Way


Reply ↓  Report •

#12
October 15, 2018 at 19:32:00
Ah, ok Razor2.3, thanks a lot, I'll give this a try today.

I'll just check this with you though to make sure I'm going to do this correctly.

This script is run inside the Excel worksheet that has the 740 items listed - correct?

Does the VBA script automatically create a 'Temp' folder in the root directory where it will send the jpg files to?

How do I point it to the folder that contains the 14,000 jpg files it needs to vet/compare the Excel data to? Is that automatically asked on running the script?

Thanks, lexip.


Reply ↓  Report •

#13
October 16, 2018 at 01:13:19
why not use this directory:
"C:\Users\USERNAME_PLEASE_REPLACE\AppData\Local\Temp"
which already exists by default

Razor2.3's code is just an example, there is still some stuff missing. So wait for Razor to wake up in the next 3~5 hours, i believe it is 05:15 'o clock where he lives

I can not really read VBA that well, but i think you need to have developers tab checked in the excel "ribbon" in order to execute a macro(the above code by Razor2.3)
-->
press alt+F11 to acces the VBA tab, double click the Excel worksheet that has the 740 items listed
paste Razor2.3's code there.
-->
you can close the VBA window now (press alt+F11 again or hit the close button) and select the first item in the list of 740
-->
while the first item in the list is selected select from the ribbon the developer's tab --> macro's --> select the macro with the name a (which is the name of the sub) & click run

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz


Reply ↓  Report •

#14
October 16, 2018 at 01:26:08
Thanks hidde663,
I'm in Sydney, Oz and just got home and will do re Razor2.3.
Really appreciate the help here (;->

Reply ↓  Report •

#15
October 16, 2018 at 07:08:39
lexip: This script is run inside the Excel worksheet that has the 740 items listed - correct?
As VBA code, yeah. You didn't give a range for your 740 items, so the script just assumes the selected cell is the first entry.

lexip: Does the VBA script automatically create a 'Temp' folder in the root directory where it will send the jpg files to?
No, that's the pictures directory. Change it to the full path of your pictures. Script copies the matches to a "Found" subdirectory it makes.

lexip: How do I point it to the folder that contains the 14,000 jpg files it needs to vet/compare the Excel data to?
This is a Q&D example. No fancy prompting here. Just change "C:\Temp" to the desired path.

hidde663: why not use this directory: [%temp%] which already exists by default
'Cause I had a C:\Temp, and it had files I could test against.

How To Ask Questions The Smart Way


Reply ↓  Report •

#16
October 16, 2018 at 07:15:21
shall i make a script that reads the 740 items from a new .csv file?
-->
then check if a picture already exists that matches the item
-->
if true --> make a directory(folder) in lets say C:\temp\%variable_name_based_on_current_item%\
and move the associated .jpg file that directory

if false --> do nothing

loop 740 times :D

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz

message edited by hidde663


Reply ↓  Report •

#17
October 16, 2018 at 09:03:47
Honestly, processing a .csv like this is one of the few things batch is good for. You could do it in 1-3 lines, depending on how lazy you were feeling.

How To Ask Questions The Smart Way


Reply ↓  Report •

#18
October 16, 2018 at 09:51:56
ill try a 1 liner :D, tomorrow tho

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz


Reply ↓  Report •

#19
October 16, 2018 at 13:40:41
Well... what can I say!

Bloody fantastic!!! (;->

I've just run the VBA script this morning over a 'cuppa' and it did everything needed.

Thank you so much both of you, especially Razor2.3, you've just made my workload a hell of a lot easier.

Very much appreciated!

And hidden663, if you do happen to write that batch file, I'd be very keen to see it as I'm sure it could/would be useful for other things I've yet to tackle on this job.

Thank you again Razor2.3 (;->


Reply ↓  Report •

#20
October 17, 2018 at 02:12:38
@echo off&md Found&for /f "delims=." %%a in (C:\LOCATION_OF\excelfile.csv) do if exist "C:\PICTURE_FOLDER\%%a.jpg" move "C:\PICTURE_FOLDER\%%a.jpg" "%~dp0Found"

This will create a folder called found in the directory where "run-me-pls.bat" was executed from.
C:\LOCATION_OF\ will need to be replace
C:\PICTURE_FOLDER\ will also need to be replaced

bonus, its a 1-liner :)

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz

message edited by hidde663


Reply ↓  Report •

#21
October 17, 2018 at 06:22:59
I'm not sure replacing [Enter] with "&" counts as making it a one liner. Lazier, untested way:
PUSHD C:\temp
FOR /F "skip=1 delims=," %%a IN (some.csv) DO COPY "%%~a.jpg" Found 2>NUL

How To Ask Questions The Smart Way

message edited by Razor2.3


Reply ↓  Report •

#22
October 17, 2018 at 06:55:00
If Found does not already exist i get an error when trying to copy or move files to a non existing dir

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz

message edited by hidde663


Reply ↓  Report •

#23
October 17, 2018 at 08:42:45
I said lazy, not from a pristine environment. Besides, wild cards aren't being used, so if "Found" doesn't exist, all that will happen is a copy of the picture will be created, and the copy will be called "Found."

How To Ask Questions The Smart Way


Reply ↓  Report •

#24
October 17, 2018 at 11:06:47
:)      

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1180Mhz core@0.937v/2000MHz


Reply ↓  Report •

Ask Question