Thursday, October 9, 2008

Windows Tip: Copy a File List Using the Command Line

Copying a list of files in a folder is a fairly simple process involving the Command Window. There are some programs out there that will do the job for you, but I think that's overkill.

I am going to outline the whole process of creating a file list and copying it in order to give the reader a better understanding of all of the steps. Skip to the end of this post for a quick and easy method of creating and saving the listing into a text file in one step. Please note that there are many subtly different ways to do this and I am only presenting a couple of them. If you have a preferred method, share it in the comments.

To display a file list and copy it, we will take the following steps:

  1. Open the Command Window.
  2. Run the dir command to display the file list.
  3. Copy the list and paste into a document or spreadsheet.

Open the Command Window
The first step is to open the Command Window. Open the Run dialog, by clicking on and then on . Alternatively, you can hold down the Windows key () on your keyboard and then tap the R key. Type cmd in the text box and click OK.



When the Command Window opens, you will find yourself in a default location, such as C:WINDOWSSYSTEM. To run a simple dir command I usually navigate to the location of the files I want to list. Another option is to enter the path of the files after the dir command, but if you don't get the output you want, then you have to type it all over again. Say you want run a file list of F:booksscans. To navigate there, you would type in F: and tap the Enter key, then type in cdbooksscans and tap Enter (cd stands for 'change directory'). Another option is to type the following in the Run dialog before opening the Command Window:

cmd /K F:&&cdbooksscans

The /K parameter carries out an operation in the Command Window and then leaves the window open. The && separates two commands. The above operation changes the drive to F:, then changes the directory to booksscans. Type in cmd /? to learn more about cmd.exe parameters.

One other option for opening a Command Window is to install the "Open Command Window Here" Powertoy from Microsoft. After it is installed, you can right-click on any folder in Windows Explorer. Choosing the Open Command Window Here menu item will open a Command Window that is already pointing to that folder, eliminating the need to navigate there.

Display File List

Of course, there are also several ways to copy the file list. I will present two here. The first involves running the dir command and copying and pasting the resulting list. The second involves piping the directory listing to a text file and then copying and pasting from there. You can also open a text file directly into a spreadsheet.

For the first option, type in dir /b and hit Enter. You will get a bare listing of file names without any other information about the files.
Of course, if there are subdirectories, those will be displayed as well. You can get rid of those by adding /a:-d before the /b. You can change the sort order, as well. Type dir /? in the Command Window for a complete list of display options.

Copy File List


To copy the file list, click on the icon () in the upper left corner of the Command Window and hover your mouse over Edit, then choose Mark. Next you can use your mouse to highlight the entire listing. Then hit the Enter key to copy the selection, or you can click on the upper left icon and Edit again, then choose Copy. See the sequence of images below. Now you can paste the listing into a document or spreadsheet.
The second option is to pipe the directory listing to a text file. Then, you can open the file directly in a document or spreadsheet. All you need to do is append the dir /b command with a greater than sign (>) and the location and name of the file where you want the listing saved. If you omit the location, the file will be saved in the current directory and the name will be included in the listing.
To shorten this whole process you can enter the following command in the Run dialog:
cmd /c f:&&cdbooksscans&&dir /a:-d /b > list.txt

Notice that I changed the /K parameter to /C. While /K leaves the Command Window open after running commands, the /C parameter closes it at the end. If you don't want to type in the path, you can copy it from the Address Bar in Windows Explorer, paste it into the Run dialog and edit the command from there.

As I mentioned above, you do not need to navigate to the folder before running the dir command. You can type the full path of the folder in between the dir and the parameters, but then you would either be saving the text file in whichever folder the Command window is pointing to, or you would have to add the full path where you want to save the text file. In this case, the command would look like this:

cmd /c dir f:booksscans /a:-d /b > f:booksscanslist.txt


This gives you a lot of flexibility, but can lead to much longer commands.

To create a menu item in the Windows Explorer context menu to save a file list in a text file, check out the following link: http://www.theeldergeek.com/file_list_generator.htm

Thursday, May 8, 2008

Remove multiple hyperlinks in Excel

I was trying to match up claims and payments in my flexible spending account, so I attempted to copy the tables from my benefit company's web site into Excel (I am using version 2003).

The first issue that I ran into was that the cells in the tables would not copy into cells in Excel and the "Text to Columns" function did not work well, even after reformatting the text to a fixed-width font (Courier, in this case). I solved this by first copying the tables into Word and then into Excel. It seems that my browser talks to Word better than it does to Excel.

The second problem was that several of the columns in each table were full of hyperlinks and I accidentally clicked on them several times, which opened my benefits site in a separate browser window each time. I tried reformatting the cells to 'Normal' and I tried using the Paste Special > Values, but neither worked. I succeeded only in changing the font, so they no longer looked like hyperlinks, but I could still click on them.

Fortunately, Microsoft has a solution for this problem right on it's support site. Removing a single hyperlink is easy. Right-click on it and choose Remove Hyperlink from the context menu. However, removing multiple hyperlinks is a completely unconventional and unintuitive process in my opinion.
  1. Type the number one (1) into an empty cell.
  2. Right-click on the cell and choose Copy from the context menu.
  3. While holding down the CTRL key, select the cells with the hyperlinks.
  4. Click Paste Special on the Edit menu, click Multiply in the Operation section and click OK.
Voila, no more hyperlinks. You can also find the solution here.