Importing a folder of filenames into Excel

Ever had to get a list of filenames into Excel? You can’t just drag the files into Excel as this will import the complete files. I had to do this recently, and rather than key in the file names one by one, I asked on a forum and got a number of good replies. Here’s the best route I’ve found from that.

The first thing you need to do is create a text file in the file directory listing those file names. Don’t worry, it’s not as hard as it sounds. To make this text file:

1. Open a command prompt: Click Start, then Run, and type cmd

2. Navigate to the directory with your list of file names using the command cd, e.g. type cd c:\photos\2008 and press Enter.

3. Create the text file: Still at the command prompt, type dir /b > tempfile.txt then press Enter

command prompt for file directory

This will create a text file called tempfile.txt with a list of all the files in that directory. Now we have to import the list from that file into Excel:

4. Close the command box by typing exit and click Enter.

5. Open Explore (right click on Start and click on Explore) and navigate to the directory with your list of filenames. The file tempfile.txt should now be there as well.

6. Right click on tempfile.txt and Select Open with then navigate down the list to Microsoft Excel and click on it.

This will import the file listing into a new Excel workbook, but you’ll probably want to get them into another workbook:

7. Select the file listing in Excel by left clicking and dragging to highlight the list (or on the keyboard, hold down the Shift key and click the Down arrow key) and copy the highlighted block with Ctrl-c

8. Open the workbook you want and paste in the listing where you want it using Ctrl-v

You can clean up the listing now:

9. Among the file names you will have imported the file name tempfile.txt. Delete this row in Excel: Highlight that row then click on Edit and then Delete.

10. You’ll also have file extensions which you may not want (e.g. doc): Click Ctrl-H and just search for .doc and replace with blank throughout the spreadsheet.

And that’s it!

Update (10 September 2009): Thanks to a post at How-to Geek, I found a free utility called Dir Print to create the text file of the directory listing. This saves having to go to the command prompt. You can also filter the directory listing to just a particular file type by modifying the box in the top right corner of Dir Print, eg for only Word files, enter *.doc.

5 Responses

  1. Bass Drum Mallets Says:

    I don’t agree with everything in this piece, even so do make some profound points. Im surely interested in this matter and I myself do alot of researching as worth. Possibly way it was a well worth thoughtout and superior read and so i figured I would get away from you a comment. Feel totally free to locate my webpage sometime and allow me know anything think.

  2. Farshad Says:

    Thanks! It helped me a lot!

  3. Gandhi Says:

    Thanks
    It helped me. It did my job.

  4. Monkeybusytyping Says:

    Excellent tip! Helped reduce the number of tasks on a large project. Will definitely be using this on a weekly/monthly basis. Thanks for the guidance!

  5. KMSO Says:

    it is good info that give me more ideas

    but, i faced some issue regarding the arabic language that not appeared in list

    do you think, it can be re-cover

    thanks

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment. Please do not use keywords in the Name field. Comments must relate to the post topic. This website is here to help people, not for advertising purposes.


× six = 6

` `