General discussion

Import a folder content (a list of files) to excel


Discussion is locked

Reply to: Import a folder content (a list of files) to excel
Reporting: Import a folder content (a list of files) to excel
- Collapse -

I'm assuming you want a list of the filenames in the directory.

So first, I'll say this is a database operation and would be better suited to Access. Excel, and all other spreadsheets, are for doing calculations. Each cell in a spreadsheet can be thought of as a unique calculator. You'll get MUCH better results, and vastly improved flexibility if you use a bonified database app like Access for this sort of thing.

That aside, the only way to do what you want, is to write a custom script that reads the file names of a directory and then lists them in Excel or Access. If you don't know VBA, there are plenty of books on the subject, and you might be able to find a ready-made script for such a task if you use Google.

- Collapse -
Assuming that...

you aren't very knowledgable of Visual Basic for applications try the following.

1. Open a command prompt (Start | Run | type in cmd /k then click OK)
2. Type the following command:
DIR "C:\directory desired\subdirectory if necessary\" /b" > c:\dirlist.txt now press the enter key
3. You now have a file named dirlist.txt with all those filenames
4. Now type EXCEL c:\dirlist.txt and again press the enter key.
5. Excel will open with the filenames in column A for you to do with as you wish.

- Collapse -
DOS command lines do not work

Hi Mr. O'Daniel,
I applied your suggestion and DOS replies with an error message.

Following are the steps I executed:
1- Opened a DOS window (clicking on Start then Run)with the command cmd /k
2- Typed the command DIR "C:\the directory I need\the subdirectory I need\" /b" > c:\dirlist.txt and pressed the enter key

DOS replies with: The system cannot find the file specified

I know the file I want exist because when I typed in the command cd C:\the directory I need\the subdirectory I need the directory changes to that directory and when I type dir the system list all the files in the directory.

Am I typing the commands you listed improperly?

- Collapse -
The problem here is

that there are not paired quotes (there are three rather than two or four) so that the /k switch does not work properly. Actually, I rarely use the /k switch and quotes: here I would have just typed cmd in the Run box, and then C:\path\ /b > C:/dirlstn.txt at the cmd prompt ? substituting numbers for the n in the filename so that multiple directories can be handled prior to moving them to Excel.

The trouble with the /b switch alone, as suggested, is that (1) the subdirectories and files are intermixed; (2) it gives the file names of only one folder at a tine. The first can be changed by adding the /og switch; the subdirectories will then be listed in alphabetical order followed by the files in order. If you have files with the same name but different extensions, you might want to use the switch /ogne this orders directories first, then files by name and then by extension.

To get information on an entire tree that has many subdirectories, subdirectories, sub-subdirectories, etc., the above requires that you do all these one at a time ? a very tedious job. You can add the /s switch to the command line, and it will get all the filenames in all of the subdirectories at all levels. The problem is that the entire path is prefixed to each file name. I find such a list to be very difficult to read, and even more difficult to manipulate in Excel.

Omitting the /b switch provides the default directory format; here, each subdirectory is displayed separately. The directory path is displayed, followed by its contents. Also, the date, time, and file size are displayed in separate columns if you wish this information. This is a little more difficult to handle in Excel, but if you are familiar with Excel operations, you can figure it out, and macros will aid the operations. The details are too lengthy to discuss here; if you want to discuss this further, contact me through my profile.

You can view the appearance of the output by using the /p switch rather than redirecting the output to a file. Also, you can check out the various switches available in the DIR command by typing dir /? at the cmd prompt.

Hope this helps


- Collapse -
Sorry, the DOS command still generates an error message

Maybe I am still typing the suggested DOS command improperly. Following is what I did:

1- Clicked on Start, then Run, typed the command cmd and pressed Enter. A DOS session was opened.
2- Typed the following command
C:\the path I need\ /b > C:/dirlst1.txt
Note: the path I need is a: dir\sub dir\sub sub dir
I also replaced the "n" by the number 1, since I only need the list of files in 1 sub sub dir.

The DOS error I get is:
'C:\Program' is not recognized as an internal or external command, operable program or batch file.

Is it possible that this is caused by the fact that the "sub sub dir" I am attemting to list is in the Program Files directory?

- Collapse -
I'm very sorry

It was after midnight, and I guess I was too sleepy; I didn't tell you to type in the DIR command, and I typed the wrong slash in the file name. Try typing this at the command prompt (&gtWink

dir /b a:\dir\subdir\subsubdir\ >c:\dirlst.txt

Hope this helps


- Collapse -
Got it

Hi Frank, I have used the ?commands? breakdown from Kees (divide to conquer submitted 12/29/05), in referenced to your instructions and it worked perfectly. Thank you for your time and help.



- Collapse -
divide and conquer

Do it in three steps.

1. cd to the folder you want: cd ''program files\sub dir'' or whatever it is. If your folder is on the a:-drive, it's a two step process. First a: then cd ''\folder\sub folder\sub sub folder''
2. dir to see that you're correct
3. dir > c:\dirlistn.txt
No need to do it all in one error-prone command.

It makes sense to keep you root folder clean. It wouldn't harm at all to put the work files in a temp folder, like
3. dir > c:\temp\dirlistn.txt (if you've got such a folder)
3. dir > c:\windows\temp\dirlistn.txt (if you've got write access to c:\windows\temp)

Use DOSKEY to repeat commands if you want to have info on multiple folders.

Of course, wild cards are allowed in step 2 and 3, like
dir *.exe


- Collapse -
Works for me

Thank you very much Kees, your instructions worked perfectly. Now with your precious help I am able to import lists of DIR files to my hart?s content.



CNET Forums

Forum Info