How do I get a folder's filename list into Excel?
1. Use Command Prompt to export the listing
Open a Command Prompt
Type cmd and press Enter to open the black Command window.
Open Windows Explorer and assume you're working on the WX_emails folder.
- Navigate to the folder
- Right click in the address bar and choose Copy address
Switch to the Command window you opened and
- Type the <drive letter>: and press Enter (i.e. for the D drive, type D:<Enter>).
- Type cd " (i.e. cd and a space and a double quote).
Choose Paste as shown.
Add the closing double quote and press <Enter>. You will see the final prompt as D:\IBM\WX_emails> which means you are now at the WX_emails folder.
The command to list files is dir followed by a 'mask'. The mask searches for filetypes. An asterisk in the mask means include any file. Therefore *.* would mean include any file and any extension. *.PDF means include only all PDF files. Since you are loading all files in the folder, you need a list of all files and we're going to send that listing to a text file so we can move the list to Excel.
Type dir *.* > WX_email_list.txt
You would use whatever name is appropriate.
We can now open this file in Excel
Placing the text file containing the file list in Excel
Create a blank Excel worksheet.
Switch to the Data tab and click on From text
Navigate to the folder where you saved the text file you want to open, select it and click on Import.
In the Excel Text Import Wizard which appears next, make sure Fixed width is selected and click on Next.
Scroll down till you see the various columns appear amongst the vertical lines. There may be leading or trailing spaces but these can be ignored except for the filename column. Drag the vertical line preceding the filename to touch the its first character.
And click on Next and then Finish.
Click OK to place the CSV file beginning in the first cell of the blank worksheet, A1.
Filenames for MasterFile CSV load file column EL_Current_File_Name_tf
The filenames are displayed in Excel, here in column F. All other columns and rows 2 through 7 can be deleted.
Column F would be used as column EL_Current_File_Name_tf in a MasterFile CSV load file.