Batch file to find filesize of two specific files and output in columns

[sterlecki]

I would like to create a batch file to look through a directory and all subdirectories, find two files by specific name and export the path, file name and file size in space delimited columns so that when opened in Excel I can sort them easily.  Both files will be present in the same directories in all instances.  Ideally I would like the output formatted as follows:

Path  FileName1  Filesize1 FileName2 Filesize2

Eventually I would like to calculate the ratio of filesize1 : filesize2 and IF it exceeds 25% write to the output file in the fashion described above.

thanks

[Geek-9pm]

Welcome!
Computer Hope is the number one location for free computer help.
The forum will help everyone with all computer questions.

You did not say which version of Windows you have. Also, which version of Excel? Answers to those two questions could make a difference.

Sorry I can not give you a direct answer.  For myself, a hybrid mixt of batch and VBA would be my choice. Ewell has VBA. Do you ever use VBA inside of Excel?

[sterlecki]

I'm still on XP and use Office 2007 for Excel.  I have written some fairly complex macros using VBA in Excel and Access Applications that I develop from time to time.

[Geek-9pm]

Office 2007 is good. You hare already done VBA stuff. Good.
(dictation)
To the scene that you already had experience with the spreadsheet program and its script language, it could be the hand to leave her to a DOS batch file to manipulate file lists.
Visual Basic for applications has enough commands to make lists of files.  Here is just one reference to a tutorial on the  Excel and VBA
http://www.excel-vba.com/excel-vba-contents.htm
Of course you already know of lot of that. But look for the stuff about file sixe. There is a specific function for file size. in VBA.
Because you posted this in the DOS section, expect to get some answers here are about using only DOS batch expressions. But I am directing you to pursue the VBA route instead.  Now some of the other members are going to criticize me for leaving the topic.   But my concern this to be the help you rather than jailing the  the discussion in DOS.
There are other excellent homes that going to depth with the kind of thing you're working on. Of course, CH it is my favorite forearm, but I had to solve a problem using VBA I would search the Internet for forms that go into considerable sentence about how to use VBA .
Tries keywords and probably find something very close to what you're looking for. Especially look for forms that lists the same kind of problem presented.
That is my best suggestion.

[sterlecki]

Joe,

My DOS is pretty rusty.  Any chance you could add a few comment lines to help me understand what each line is doing?

thanks

[sterlecki]

Thanks for the refresher.

I don't think copying the files is an option for me.  The files that I'm searching for gxdb.log and gxdb.db could both be gigabytes in size.  To complicate that the directory (server) that I'm searching through has over 100 large projects.  Each project folder contains 1 gxdb.log and 1 gxdb.db file.  I want to monitor the ratio of the .logfile to the .dbfile.  If it exceeds a certain threshold then I can flag the project for compression and rebuild.  Each project has thousands of files and can be 50 gigabytes in size.

the output file could look like this:

File Path:                                                  File Name:     File Size:
L:\Big Horn Basin\Big Horn Basin\   gxdb.db   159571968
L:\Big Horn Basin\Big Horn Basin\   gxdb.log   150536192
L:\contract\uinta_2d\jro\uinta jro ggx\   gxdb.db   5963776
L:\contract\uinta_2d\jro\uinta jro ggx\   gxdb.log   1376256
L:\Data\Templates\Project\   gxdb.db   1642496
L:\leasedata\48 Albers Equal Area\   gxdb.db   12935168
L:\leasedata\48 Albers Equal Area\   gxdb.log   1376256

Using VBA at GEEK-9PM suggestion is working but it is very slow.  I think DOS might be faster.  I can get what I need through VBA but when I was experimenting with just creating a file using the DIR command and searching for gxdb.* the file was created relatively quickly say 15-20 minutes where the VBA does not seem to make progress that fast.

Your thoughts?  Or I could just quit while I'm ahead!

[Squashman]

The problem you are going to have doing this with batch is that it can't handle numbers larger than 2,147,483,648. So basically any files larger than 2GB are not going to work.

[Squashman]

True that math in batch won't output a floating point decimal but you can simulate it. I don't think you would need an exact decimal output anyways. If we are just going off of a percentage then you could just use the modulus.

[Geek-9pm]

Does every folder have both
gxdb.log   
and
gxdb.db
Would there ever be one without the other?  Which might be missing?
do other files begin with
gxdb
Few? Lots?
How often are the files updated? Are the directories rather stable? You don't have directories coming and going daily.
If time is a factor, is time available for pre- processing of the report?


If the CMD interpreter does some things faster, perhaps a hybrid approach be  might work well.

For example, this command does a very quick list:

Code: [Select]

dir gxdb.* /b /s >list.txtThe list can be read into Excel as a single column. VBA could parse the text and create new columns with the format you need. VBA would also get the FileLen and present in it rounded megabytes.

MS Excel: FileLen Function (VBA only)
In Excel, the FileLen function returns the size of a file in bytes.
The syntax for the FileLen function is:
FileLen( file_path )
file_path is the path to a file name that you wish to retrieve the size for.
Applies To:
    Excel 2007, Excel 2003, Excel XP, Excel 2000

http://www.techonthenet.com/excel/formulas/filelen.php
Does this help any? Sorry, I am rather slow. Old age.

[Geek-9pm]

The OP has a concern about speed on a very large set of files in many directories. If he knows fro sure that both files of interest must be in a directory, then it is not necessary to search for both files.

A simple, fast search for just the name of one file will give a list of locations where both files must exists.
Also, deferring file size visibility  until the time of generating the report speeds up processing. So suppressing all other file information creates a list very quickly. OP says  files are very large. Making the size visible**  twice puts a load on the interpreter that can reduce speed in when files have huge sizes.  So do file size later in preferred format.
Exanple:

Code: [Select]

DIR gxdb.log /B /S >listing.txtGives a nice Brief list of just the full-path file name for all  files with name gxdb.log starting in the current directory ans searching sub- directories also.  Other file details are skipped.

**  Making the size visible. When you use  a DIR command or its equivalent, the CMD  interpreter builds a list of names and attributes and size of each file. The file size is in binary and must be converted into a suitable format for display. Even if the output goes to a file, the file must be a TXT type  and a binary to ASCII conversion is mandatory. Even if the report file does not have the TXT in the extension, it still is a text file. So telling the CMD to not do anything but just the name of the file with full path is a lot quicker. A whole lot. Also, it makes the file size of the listing fie smaller.

Inside Excel VBA can fetch the files sizes and format a nice report.
OP, you must already know this, just making is plain to other readers.

[Sidewinder]

A simple, fast search for just the name of one file will give a list of locations where both files must exists.
Also, deferring file size visibility  until the time of generating the report speeds up processing. So suppressing all other file information creates a list very quickly. OP says  files are very large. Making the size visible**  twice puts a load on the interpreter that can reduce speed in when files have huge sizes.  So do file size later in preferred format.

Not sure why that would be true. Is VBA really faster than batch for pulling file info from the system?

This little snippet will produce a comma separated value file suitable for input to Excel where you can do your VBA thing:

Code: [Select]

@echo off
setlocal enabledelayedexpansion
set startfolder=L:\

for /f "tokens=* delims=" %%i in ('dir %startfolder% /s /b ^| findstr /i /c:gxdb.db /c:gxdb.log') do (
if /i %%~xi EQU .db set outline=%%~dpi,%%~nxi,%%~zi
if /i %%~xi EQU .log echo !outline!,%%~nxi,%%~zi
)

Good luck. 

[Squashman]

Not sure why that would be true. Is VBA really faster than batch for pulling file info from the system?

This little snippet will produce a comma separated value file suitable for input to Excel where you can do your VBA thing:

Code: [Select]

@echo off
setlocal enabledelayedexpansion
set startfolder=L:\

for /f "tokens=* delims=" %%i in ('dir %startfolder% /s /b ^| findstr /i /c:gxdb.db /c:gxdb.log') do (
if /i %%~xi EQU .db set outline=%%~dpi,%%~nxi,%%~zi
if /i %%~xi EQU .log echo !outline!,%%~nxi,%%~zi
)

Good luck. 

That code should work but you really don't need to pipe it to the findstr command.

Code: [Select]

@echo off
setlocal enabledelayedexpansion
set startfolder=L:\
pushd "%startfolder%"
for /f "tokens=* delims=" %%i in ('dir /a-d /s /b gxdb.db gxdb.log') do
        if /i %%~xi EQU .db set outline=%%~dpi,%%~nxi,%%~zi
if /i %%~xi EQU .log echo !outline!,%%~nxi,%%~zi
)
popd


[sterlecki]

Thank you all so much for your help and suggestions.  I apologize for not responding but other priorities have dragged me from this issue.  I will do some testing of the various suggestions and respond ASAP.

Thanks again to All of you.