How do I make a spreadsheet of my hard drive folders and subfolders and contents.

stpauler · October 22, 2011, 6:33am

I’m on Windows 7 with Excel 2007.

What I want is to make a spreadsheet of my hard drive with all of my MP3s on it which are all neatly organized alphabetically into their subfolders.

So, what I’d love is to be able to have all the data show up into Excel with each level in its own column. So it would look something like this:


I:	A/	Abba	20th Century Masters	01-Waterloo
I:	A/	Abba	20th Century Masters	02-SOS

I’ve tried a couple different programs (PrintFolders) and tried my DOS skills but I couldn’t get it right without a lot of editing afterwards. Since I’ve got over 2TBs of music ripped to my harddrive, it would take a lot of work.

The_Niply_Elder · October 22, 2011, 6:48am

You will have to download diruse.exe from Microsoft and use it as described here:

http://ss64.com/nt/diruse.html

Basically, you call on the command, specify a directory to scan (ie. D:\music*.mp3, this will scan and filter for mp3 only). Then you give it a size threshold (say, 0 bytes to output all mp3), and slap on a few flags such as /M to output size in megabytes, finally send the output to a text file (>C: emp\mp3summary.txt) then copy and paste into excel.

Also lookup how to use Tree command for an alternative.

Oh yeah, I recommend that you put all these commands into a batch file and test the syntax on a small test sample folder. I used this command at work to scan a 7 Tb server and it took a half hour to finish.

The_Niply_Elder · October 22, 2011, 6:53am

On second thought this is better:

http://ss64.com/nt/dir.html

stpauler · October 22, 2011, 7:10am

Thanks, but I’m having a difficult time with this in the command prompt.

I’m trying dir /a/s/b and there’s way too much data if I just go through the bands starting with the letter A. I could do dir /a/s/b/p but that would take a lot of time to get through all of them.

I’m also not getting how to send this over to a text file. I’ve typed >C: emp\mp3summary.txt into the command prompt but the syntax is incorrect. What am I missing?

Quartz · October 22, 2011, 7:23am

Treesize Pro is what you need. It can export to Excel.

The_Niply_Elder · October 22, 2011, 10:29pm


Dir D:\Music\*.mp3 /A /O:GN /S >C:	emp\mp3summary.txt

Type the above text into a text file using Notepad. Then save and close it, rename it removing the .txt extension and replace it with .bat extension. Double click the file to run it. Look for the summary file in the temp folder.

stpauler · October 23, 2011, 8:04pm

Thanks again, but it seems that it can’t find the songs since they’re in subfolders on top of that. The I: drive has A-Z subfolders with the artist subfolders underneath those and then the album sub-sub folders underneath again.

I’ve been playing with Treesize pro and seem to be having the same issue.

Any other ideas?

Ximenean · October 23, 2011, 9:07pm

You could use the attrib command, which gives you the full file path. Something like

attrib /s i:* > c: emp\summary.txt

would yield

A I:\A\Abba\20th Century Masters\01-Waterloo.mp3
A I:\A\Abba\20th Century Masters\02-SOS.mp3
… etc. …

Then open it in Excel specifying backslash as the delimiter. (the initial A is a file attribute that you can strip away with Excel if necessary).

Duke · October 23, 2011, 11:56pm

There’s a way of doing this in VBA, too, but it’s in one of Bill Jelen’s books and I fear that the VBA script is a bit too long to claim as “fair use”. But it may be on his MrExcel site somewhere.

Number · October 24, 2011, 5:06am

Paste the script below into a text file with a .vbs extension. Customize the value of the strRoot variable with the location of the folder which all of your music is under. Customize the value of the strReport variable with the location to write the script’s output to.

Running the script will create a comma-delimited text file which can be opened in Excel.

Advantages of this method are that it is pretty fast, won’t choke on special characters like batch scripts can, and doesn’t require installing any additional software.


strRoot = **"c:\songs"**
strMask="*.mp3"
strReport=**"C:\SongReport.csv"**

intCount = 0

For Each strFile In DirToArray(strRoot, strMask, True)
    strPath = Right(strFile, (Len(strFile)) - (Len(strRoot) + 1))
    strPath = Chr(34) & Replace(strPath, "\", Chr(34) & "," & Chr(34)) & Chr(34)
    WScript.Echo strPath
    
    ReDim Preserve arrOutput(intCount)
    arrOutput(intCount) = strPath
    intCount = intCount + 1
Next

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objReport = objFSO.CreateTextFile(strReport, True)
objReport.Write Join(arrOutput, vbCrLf)
objReport.Close

Function DirToArray(strRootFolder, strFileMask, blnRecurse)
    Dim strCommand, objShell, objWshScriptExec, objStdOut
    Dim intLimit, strFile, arrOutput()

    strCommand = "%comspec% /c @dir " & Chr(34) &_
        strRootFolder & "\" & strFileMask & Chr(34) & " /a:-d /b"
        
    If blnRecurse Then
        strCommand = strCommand & " /s"
    End If

    Set objShell = CreateObject("WScript.Shell")
    Set objWshScriptExec = objShell.Exec(strCommand)
    Set objStdOut = objWshScriptExec.StdOut
    
    intLimit = 0

    Do Until objStdOut.AtEndOfStream
        strFile = objStdOut.ReadLine
        
        If Not blnRecurse Then
            strFile = strRootFolder & "\" & strFile
        End If
        
        ReDim Preserve arrOutput(intLimit)
        arrOutput(intLimit) = strFile
        intLimit = intLimit + 1
    Loop
    
    DirToArray = arrOutput
End Function

Number · October 24, 2011, 1:01pm

A minor edit. The previous revision would echo the path of every file as it ran, which is useful for testing but not desirable in practice.


strRoot = "c:\songs"
strMask="*.mp3"
strReport="C:\SongReport.csv"

intCount = 0

For Each strFile In DirToArray(strRoot, strMask, True)
    strPath = Right(strFile, (Len(strFile)) - (Len(strRoot) + 1))
    strPath = Chr(34) & Replace(strPath, "\", Chr(34) & "," & Chr(34)) & Chr(34)
    
    ReDim Preserve arrOutput(intCount)
    arrOutput(intCount) = strPath
    intCount = intCount + 1
Next

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objReport = objFSO.CreateTextFile(strReport, True)
objReport.Write Join(arrOutput, vbCrLf)
objReport.Close

WScript.Echo "Report complete."

Function DirToArray(strRootFolder, strFileMask, blnRecurse)
    Dim strCommand, objShell, objWshScriptExec, objStdOut
    Dim intLimit, strFile, arrOutput()

    strCommand = "%comspec% /c @dir " & Chr(34) &_
        strRootFolder & "\" & strFileMask & Chr(34) & " /a:-d /b"
        
    If blnRecurse Then
        strCommand = strCommand & " /s"
    End If

    Set objShell = CreateObject("WScript.Shell")
    Set objWshScriptExec = objShell.Exec(strCommand)
    Set objStdOut = objWshScriptExec.StdOut
    
    intLimit = 0

    Do Until objStdOut.AtEndOfStream
        strFile = objStdOut.ReadLine
        
        If Not blnRecurse Then
            strFile = strRootFolder & "\" & strFile
        End If
        
        ReDim Preserve arrOutput(intLimit)
        arrOutput(intLimit) = strFile
        intLimit = intLimit + 1
    Loop
    
    DirToArray = arrOutput
End Function

stpauler · October 26, 2011, 12:59am

Thanks all, number, I’m getting a script error with that for some reason:
Line: 18
Char: 1
Error: Type mismatch: ‘Join’
Code: 800A000D
Source: Microsoft VBScript runtime error

Any ideas?

Number · October 26, 2011, 1:18am

Ok, I see the problem. The script wasn’t expecting strRoot to be set to the root of a drive. This revision should take care of it.


strRoot = "c:\songs"
strMask="*.mp3"
strReport="C:\SongReport.csv"

intCount = 0

If Right(strRoot, 1) = "\" Then
    strRoot = Left(strRoot, Len(strRoot) - 1)
End If

For Each strFile In DirToArray(strRoot, strMask, True)
    strPath = Right(strFile, (Len(strFile)) - (Len(strRoot) + 1))
    strPath = Chr(34) & Replace(strPath, "\", Chr(34) & "," & Chr(34)) & Chr(34)
    
    ReDim Preserve arrOutput(intCount)
    arrOutput(intCount) = strPath
    intCount = intCount + 1
Next

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objReport = objFSO.CreateTextFile(strReport, True)
objReport.Write Join(arrOutput, vbCrLf)
objReport.Close

WScript.Echo "Report complete."

Function DirToArray(strRootFolder, strFileMask, blnRecurse)
    Dim strFolder, strCommand, objShell, objWshScriptExec
    Dim objStdOut, intLimit, strFile, arrOutput()
    
    If Right(strRootFolder, 1) = "\" Then
        strFolder = strRootFolder
    Else
        strFolder = strRootFolder & "\"
    End If

    strCommand = "%comspec% /c @dir " & Chr(34) &_
        strFolder & strFileMask & Chr(34) & " /a:-d /b"
        
    If blnRecurse Then
        strCommand = strCommand & " /s"
    End If

    Set objShell = CreateObject("WScript.Shell")
    Set objWshScriptExec = objShell.Exec(strCommand)
    Set objStdOut = objWshScriptExec.StdOut
    
    intLimit = 0

    Do Until objStdOut.AtEndOfStream
        strFile = objStdOut.ReadLine
        
        If Not blnRecurse Then
            strFile = strFolder & strFile
        End If
        
        ReDim Preserve arrOutput(intLimit)
        arrOutput(intLimit) = strFile
        intLimit = intLimit + 1
    Loop
    
    DirToArray = arrOutput
End Function

stpauler · October 26, 2011, 1:35am

Perfect! I hit the limit in Excel apparently so I have to do it by folder but that worked. Thank you so much!

Topic		Replies	Views
Free software for listing file sizes Factual Questions	5	788	August 3, 2009
How am I to place 40000 filenames in a .txt? Factual Questions	5	902	March 21, 2007
I want to compile my MP3s into a printed list...... Factual Questions	12	827	May 7, 2001
MS Windows Explorer - how do I copy directory tree data into other documents? Factual Questions	13	3480	December 12, 2003
Fastest way to move files to a folder and create the folder :XP Factual Questions	13	1505	June 21, 2006

How do I make a spreadsheet of my hard drive folders and subfolders and contents.

Related topics