Need List of Files in Folders and Sub Folders by File Type and Export to Text File

gracie1972
gracie1972 used Ask the Experts™
on
I had this question after viewing List folders and sub-folders of a path with its size.

I need to take this a step further.  I need to be able to search for file types like .mdb, and ,accdb..
Then output data to a text file I can link to.  
All the code I have inserts into the same worksheet, but this keeps crashing my computer.

Ideally I would like to include:
Folder
Sub Folder
Path
Name
Type
Size
Last Modified By or Owner (Not sure if possbile)
Date Last Modified

Exported to a text file.  

We have an insane amount of access databases, I need to do a formal audit to gain an insight of what is out there, size and when used last.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is easily achievable in CMD.EXE. Just type the command
DIR *.* /OE /S

If you need output to a text file just redirect the DIR command output:
DIR *.* /OE /S > DirOutput.txt

If you need more info about DIR command options, issue:
DIR /?

Owner is also available - use /Q option.

Last modified By is audit info which is not preserved obviously.

Author

Commented:
I only need access databases that are on the network, when I have tried this first to change the directory to my mapped drive it does not allow me to run this.  Could be limitations of my permissions.
OK, you may use  DIR *.accdb   and  DIR *.mdb   to list this file types.

If you have access to mapped drive in Windows Explorer then the DIR command should also work. Of course, it does not support UNC paths as a current folder so you have to either assign drive letter to the mapped drive or use DIR with UNC path, e.g.:

DIR  \\10.75.234.123\ShareName\*.mdb /S

If your access rights are restricted then you simply don't see everything... or even Access denied error appears (not obvious for DIR command).
The best you can do here is to ask some Domain admin to issue the DIR command for you.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I would think Powershell would be a more efficient solution.  You can select specific fields and even exprot to CSV fairly easy.

There are many examples out there on this.  Even one that looks for MDB files:
https://social.technet.microsoft.com/Forums/en-US/7c6c91b3-7376-47da-8bfc-6469d362e2e4/powershell-script-to-search-a-network-drive-for-mdb-files-and-export-them-to-csv-file?forum=winserverpowershell
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
PowerShell:
$startFolder = 'C:\Temp'
$mask = '*.accdb', '*.mdb'
$outFile = 'C:\temp\db.csv'
Get-ChildItem -Path $startFolder -Recurse -File -Include $mask |
	Select-Object -Property `
		DirectoryName,
		FullName,
		Name,
		Extension,
		@{n='SizeKB'; e={[math]::Round($_.Length / 1kb, 2)}},
		LastWriteTime,
		@{n='Owner'; e={$_.GetAccessControl().Owner}} |
	Export-Csv -NoTypeInformation -Path $outFile

Open in new window

@oBdA

you have excellent skills in PowerShell.

How would i modify the above code. so that it looks for all files and not just '*.accdb', '*.mdb'  can i replace it with '*.*'?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
It's that simple, yes.

Author

Commented:
@oBdA  there seems to be an issue, it stops after so many records, is this better to write in VBA?
This is what I am using and works, but seems to stop after so many records....

$startFolder = 'V:\DATA\RightsourceManagement\Clinical Programs\'
$mask = '*.accdb', '*.mdb'
$outFile = 'C:\Users\ANB0944\Documents\Code\db.csv'
Get-ChildItem -Path $startFolder -Recurse -File -Include $mask |
	Select-Object -Property `
		DirectoryName,
		FullName,
		Name,
		Extension,
		@{n='SizeKB'; e={[math]::Round($_.Length / 1kb, 2)}},
		LastWriteTime,
		@{n='Owner'; e={$_.GetAccessControl().Owner}} |
	Export-Csv -NoTypeInformation -Path $outFile

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Clarify "seems to stop"

Author

Commented:
The file stops updating after 119 records.  Powershell does not give an error message just nothing more happens.  Should I just let it sit there?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
How many files dies it have to search, roundabout?
If it's a large tree, where a large subtree has no .accdb or .mdb files, it might take a while until it finds the next one.

Author

Commented:
Okay, I am not sure, I just know there are at least 300-400, does this code allow subfolders?  Maybe that is what is going on?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
I meant the whole tree, not just the db files.
Yes, you wanted subfolders, so it'll search the complete tree down from the start folder:
Get-ChildItem -Path $startFolder -Recurse -File -Include $mask |

Author

Commented:
Okay, I will try again, it is not pulling subfolders which is odd and only stops at the main one.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
As a quick test, create a quick folder structure and place a few files in them.  Then you will quickly see if it traverses subfolders.

I agree that a network share can take a while to walk if it is a large file system so it can "appear" hung.

Author

Commented:
I did a quick comparison and in the main folder alone there are 222 .accdb databases, the Powershell code above only found 181 across multiple folders.  It is picking up sub folders, just not capturing all the data...
And what about the DIR /S command?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Output doesn't start in the main folder. It walks down the tree first.
Again: how many file altogether in the tree are we talking about?

Author

Commented:
I would have to check each folder, there are about 60+ sub folders.   I will let you know.

Commented:
Gracie,

Please try the below script which will scans through your local file server and then report the result to your email address.

all you need to do is to change the values according to your environment:

    $ScanPath = "V:\DATA\RightsourceManagement\Clinical Programs\"
    $ResultFile = "C:\Users\ANB0944\Documents\Code\db.csv"
    $ResultEmailRecipient = "gracie1972@company.com"
    $ResultEmailSender = "Sender@yourCompany.com"
    $IncludeFilter = @('*.accdb', '*.mdb')
    $MailServer = "emailserver.company.com"

Open in new window


Function Convert-Size {
    <#
        .SYSNOPSIS
            Converts a size in bytes to its upper most value.
    
        .DESCRIPTION
            Converts a size in bytes to its upper most value.
    
        .PARAMETER Size
            The size in bytes to convert
    
        .NOTES
            Author: Boe Prox
            Date Created: 22AUG2012
    
        .EXAMPLE
        Convert-Size -Size 568956
        555 KB
    
        Description
        -----------
        Converts the byte value 568956 to upper most value of 555 KB
    
        .EXAMPLE
        Get-ChildItem  | ? {! $_.PSIsContainer} | Select -First 5 | Select Name, @{L='Size';E={$_ | Convert-Size}}
        Name                                                           Size                                                          
        ----                                                           ----                                                          
        Data1.cap                                                      14.4 MB                                                       
        Data2.cap                                                      12.5 MB                                                       
        Image.iso                                                      5.72 GB                                                       
        Index.txt                                                      23.9 KB                                                       
        SomeSite.lnk                                                   1.52 KB     
        SomeFile.ini                                                   152 bytes   
    
        Description
        -----------
        Used with Get-ChildItem and custom formatting with Select-Object to list the uppermost size.          
    #>
        [cmdletbinding()]
        Param (
            [parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
            [Alias("Length")]
            [int64]$Size
        )
        Begin {
            If (-Not $ConvertSize) {
                Write-Verbose ("Creating signature from Win32API")
                $Signature =  @"
                    [DllImport("Shlwapi.dll", CharSet = CharSet.Auto)]
                    public static extern long StrFormatByteSize( long fileSize, System.Text.StringBuilder buffer, int bufferSize );
"@
                $Global:ConvertSize = Add-Type -Name SizeConverter -MemberDefinition $Signature -PassThru
            }
            Write-Verbose ("Building buffer for string")
            $stringBuilder = New-Object Text.StringBuilder 1024
        }
        Process {
            Write-Verbose ("Converting {0} to upper most size" -f $Size)
            $ConvertSize::StrFormatByteSize( $Size, $stringBuilder, $stringBuilder.Capacity ) | Out-Null
            $stringBuilder.ToString()
        }
    }
    
    $ScanPath = "V:\DATA\RightsourceManagement\Clinical Programs\"
    $ResultFile = "C:\Users\ANB0944\Documents\Code\db.csv"
    $ResultEmailRecipient = "gracie1972@company.com"
    $ResultEmailSender = "Sender@yourCompany.com"
    $IncludeFilter = @('*.accdb', '*.mdb')
    $MailServer = "emailserver.company.com"
    
    #Powershell 3.0
    $FileList = Get-ChildItem -Path $ScanPath -Include $IncludeFilter -Recurse -File
    
    # Powershell 2.0
    #$FileList = Get-ChildItem -Path $ScanPath -Include $IncludeFilter -Recurse | Where-Object {! $_.PSIsContainer}
    
    $FileList |
        Select-Object FullName, LastWriteTime, @{Name='Size'; Expression={$_.Length | Convert-Size}}, @{Name='ByteSize'; Expression={$_.Length}} |
        Export-Csv -NoTypeInformation -Path $ResultFile
    
    $Summary = $FileList | Group-Object Extension | ForEach-Object {
        $ByteSize = ($_.Group | Measure-Object Length -Sum).Sum
        $_ | Select-Object -Property `
            @{Name='Type'; Expression={$_.Name}},
            @{Name='Size'; Expression={$ByteSize | Convert-Size}},
            @{Name='ByteSize'; Expression={$ByteSize}}
    } | Sort-Object -Property ByteSize -Descending
    
    $Body = @"
    User File Report for $($ENV:ComputerName):
    
    $($Summary | Format-Table -AutoSize | Out-String)
"@
    
    $Body
    
    Send-MailMessage -Attachments $ResultFile -To $ResultEmailRecipient -From $ResultEmailSender -Subject 'Daily Users File Report' -Body $Body -SmtpServer $MailServer

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Just keep it running. I'm pretty confident it will do its job.
If you look at the file list generated so far, you should (probably) see that none of the files found so far are located in the root of the search tree.

Author

Commented:
For some reason it still stops and doesn't update anymore once the file size get to 54KB.  Any ideas?
I suppose this is valid for PowerShell. What about the DIR /S command?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
How long have you let it run without interruption?
Does it show any errors in the console?
Is the script done (the prompt is back and you can enter commands), or just sitting there? If the latter, just keep it running.
Depending on the size of the tree, the speed of the storage, the network speed, the air humidity, etc., this can take hours to days.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I agree with oBdA.  You never said how large the network share is or how many files/folders it has. A 1 terabyte share with millions of files take take a bit.

>>I suppose this is valid for PowerShell.

Nope.  Unless there is some error, it is still running.  This can be confirmed with a simple look in Task Manager/Performance Monitor.  If it is using CPU, it is running.

I've never seen Powershell hang/crash/??? without displaying an error message of some sort.

>>What about the DIR /S command?

I don't think that shows all the requested pieces of information.  The output will also be difficult to load into a spreadsheet as requested.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial