Printing the last file name in each sub directory

[maxray]

Hi,

I need a code, that finds out the last file in each sub directory, and then prints the result to an excel sheet.

Last file name: when all the files are sorted alphabetically, then the last alphabetical file name.

e.g, if there are 5 folders, and each contains 10 files, then the final resulting excel should contain the names of 5 files (1 each from all the 5 folders)

please help!
Thanks in advance

[Squashman]

You did not specify the directory structure.
Also, batch files cannot write to excel files. The best you can do is create a csv and then open the csv in Excel.

[LotPings]

Just made a comparison between batch and powershell on this

Batch took me about 10 min:

Code: Select all

:: Get "Last" files from all subfolders of %CD%
@Echo off&SetLocal EnableExtensions EnableDelayedExpansion
Set "Cnt=100"
:: clear vars
For /f "delims==" %%A in ('Set Last 2^>Nul') Do Set "%%A="
for /d  %%A in (*) Do (
  Set /A "Cnt+=1"
  PushD "%%A"
  For /f "delims=" %%B in (
    'Dir /B/A-D/O-N * 2^>Nul'
  ) Do If Not defined Last!Cnt:~-2! Set "Last!Cnt:~-2!=%%~fB"
  Popd
)
If Cnt leq 100 Exit /B 1
Echo These are the "last" files of %Cnt:~-2% subfolders
Set Last
(
For /f "Tokens=1*delims==" %%A in ('Set Last') Do Echo:"%%~fB"
) > "%__CD__%For_Excel.csv"

Powershell 2 mins:

Code: Select all

 gci -dir  |%{gci $_ -file|sort|select -last 1 FullName}|Export-csv For-Excel2.csv -notype

The Powershell version could be extended to create a real excel file, even without excel but a module.

[penpen]

This is an unfair comparison, because (in contrary to the powershell version) the batch file is far from beeing optimal:
For example environment variables "cnt" and "last..." are not needed, "pushd" and popd could also be avoided, ... .

Sketch (untested):

Code: Select all

:: Get "Last" files from all subfolders of %CD%
@echo off
setlocal enableExtensions disableDelayedExpansion

@(
    <nul (
      for /D %%a in (*) do @(
         set /P "="""%%~fa\""""
         dir /A:-D /B /O:-N "%%~a"
      )
   ) 2>nul
) | findstr "^\"\"" | findstr /v "\"$"

endlocal
goto :eof

Sidenote: I don't know if this is optimal, but it should be faster than the batch you tested.


penpen

[maxray]

Thanks Squashman, LotPings and penpen for your replies.

Directory structure: normal windows directory structure...folders inside folders
e.g. 10 folders (season1, season 2...season 10) inside a folder named smallville.

Yes, the code need not write to a excel file. csv works for me and i can open it in excel.

I dont know how to work with powershell..therefore i will work with the batch code only.
but i am gonna try the powershell version also...seems far more simpler!!

Thanks again to you all