How to Copy a List of Files in a Windows Folder Into an Excel List

I have quite a few ebooks. Most are in .pdf and .epub formats. I tend to forget which ones I have. For some time I have been thinking about how to organize them in such a way as to be able to view them at a glance. I am diligent about the filenames: I use the format, “Title – Subtitle – Author.pdf” (for example).

I am limited by the amount of information I can contain in the filename and do not have many ordering options (i.e., alphabetical, file type, etc.).

I wanted to move my file structure into an Excel spreadsheet in order to add more descriptors, such as whether I’ve read the book, whether the book is high on my priority list, and what the book is about (i.e., tags), among others.

Here is how I have done it using the command prompt to output a text document containing a directory list then importing this list into Excel:

Step 1

First I located the folder for which I want to create a list. I do this by pressing “Win-E” to open Windows Explorer.

Step 2

Once I’ve located my folder I then hold the “Shift” key, right-click the folder and select “Open Command Window Here.” It’s important to note that this only works with folders, not libraries. Libraries point to a specific folder, so I select the folder located under the library icon. If the library points to a drive, I’d right-click the drive letter from the folder.

Step 3

Next I type “dir /b > dirlist.txt” without quotes into the command prompt and press “Enter.” This creates a list containing file names only. To include file sizes and dates, type “dir > dirlist.txt” instead. To also include files in sub-directories, type “dir /b /s > dirlist.txt” to create a list of files with the full directory structure name, such as “C:\folder\subdirectory\file.txt.”

Step 4

I then open Excel and press “Ctrl-O” to bring up the Open dialog window.

Step 5

I find the folder containing the files, click the file type drop-down menu and select “Text Files (*.prn,*.txt,*.cvs)” or “All Files” and ouble-click “dirlist.txt” to open it.

Step 6

I finish by clicking “Finish” in the Text Import Wizard window and then go through the options. I unselect the delimiter from “Tab” to “Other” and I type in a dash “-” to indicate that I want to separate the columns using this symbol from the filename. Finally, I import the directory list into Excel.

Step 7

While all books have titles and authors, not all books have subtitles. Some of my cells in column B contain authors and others subtitles. I want to move all authors into the same column (i.e., column C). Fortunately, all cells that contain authors have a filename associated with it (e.g., .pdf, .epub, .mobi).

I will copy & paste the following formula into each empty cell in column C to copy the contents of column B that contains “.pdf”

=IF(COUNTIF(B1,”*.pdf*”),B1,””)

Step 8

I do the same for empty cells in column C corresponding to each filename (i.e., .epub, .mobi) until they are full.

Step 9

Next I need to remove the formula and save the values from column C so to do this I select the entire column and press “Ctrl-C” and rather than simply “Ctrl-V” to paste I right-click and hit “Paste Special” and select “Values” – this assures that the author names are not removed when I remove them from column B.

Step 10

And finally, I delete authors from column B. To do this I select the entire column B and run a search & replace (Ctrl-F) for each filename type (i.e., .pdf, .epub, .mobi) in column B, replacing each author name with a blank cell.

I have no doubt there is an easier way to do this (please share!!!) but in meantime, this is how I have managed to change my folder of hundreds of ebooks into a clean spreadsheet that I can add columns for additional details.

Next, I need to figure out how to append additional ebooks without having to do it manually …

Advertisements