BugwoodIDS:Importing filenames into Excel

From Bugwoodwiki
Jump to: navigation, search

For large batches of files copying file names into a spreadsheet can be extremely tedious and may result in errors. A faster way to do this with little chance for error is to use some DOS commands. Here is an example of how this is done.

Contents

DOS commands and jargon you will need to know before you start

The Command prompt

This typically looks something like:

C:\Documents and Settings\Joe\My Documents>

Whatever is listed to the left of the ">" sign is your current location on the computer. It is the same as the "Address" that appears when browsing folders on your computer.

cd

"cd" stands for "change directory". This command is used to change your current location on the computer, just like clicking on a folder takes you into that folder.

For example, if I type in

cd my pictures

at the command prompt listed above, I'm telling the computer to put me into the "my pictures" folder which is in "F:\Documents and Settings\Joe\My Documents". If successful, my new command prompt will read

C:\Documents and Settings\Joe\My Documents\My Pictures>

If you want to go back one level, the command is

cd..

and the new command prompt that will appear is

C:\Documents and Settings\Joe\My Documents>

dir

"dir" tells the computer to list everything in a directory. Depending on the number of files, this may be a long list.

The command that you will end up using to harvest this list is

dir >C:\[FILENAME].txt

If you want a listing of the files in your current location and all subfolders, the command you will use is

dir /s >C:\[FILENAME].txt

These commands take the list that is normally displayed on the screen and put it into a text file. When using the command, replace [FILENAME] with whatever you want the file to be called.

The full example - This is how you do it in Windows XP using Office 2003 or earlier

For this example, I am using Windows XP and I have a folder of images located at F:\New Images

  • Click "Start"
  • Select "Run..."
  • Type cmd

This stands for "Command". It will most likely start you off at with a command prompt looking something like

C:\Documents and Settings\Joe\My Documents>
  • Type f:

This will tell the computer to go to the specified drive on your computer. If your files are on the drive you are currently on, you can ignore this. For this example, the command prompt now reads

F:\>
  • Type cd new images

This is where you use the "cd" command to get to the files. If you have the folder with the files open in windows, you can use the information in the address line to guide you along. My command prompt now looks like

F:\New Images>
  • Type dir >C:\NewImageFilenames.txt

This will create a text file called "NewImageFilenames.txt" in the root directory (C:\) of my C drive.

  • Close the "DOS Command" window or type "exit"
  • Open Excel
  • Select "File"
  • Select "Open..."
  • Change "Files of type:" to "All Files (*.*)". This is located at the bottom of the "Open..." window.
  • Find the text file you just made and open it in Excel. This will start the "Text Import Wizard"
  • Choose "Fixed width" and click "Next>"
  • In the preview window, scroll down until you can see filenames.
  • Create a break line at the beginning of the file name.
  • Delete any other break lines that are on the preview window.
  • Click "Finish"
  • Select the entire first column and delete it. This is other information not needed.
  • Browse through the rows of data and delete extra spaces and other items that are not filenames. These are left over from the header and footer displayed in the "dir" command.

You should now have a fairly clean list of file names that you can copy to the Bugwood Data sheet or the spreadsheet of your choice.

Personal tools
Namespaces

Variants
Actions
Navigation
Projects
Participation
Other Bugwood Resources
Export Current Page
Toolbox