Excel: Import all file names from a hard disk directory

Some users manage their music files, photos or other files with Excel. In the case of unmanageable quantities of thousands of files, however, simply taking over the file names is a lengthy task. But you can make your work easier.

Open the Windows command prompt and go to the folder where you saved the files. Now type the following command: “dir / b / a-d -› file list. txt “. The old DOS command “dir” lists the entire contents of a directory. Use the “/ b” switch to limit the display to the file names; further displays such as headline, creation date or time are no longer required. The switch “a-d” means that no subdirectories are displayed. With “-› file list. txt “finally you create a text file in this directory called file list.txt, in which the dir command enters the list of file names.

Now start Excel and go to “File -› Open “. Click “Browse” and in the next window set “Text files (* .prn, * .txt, * .csv)” in the drop-down menu at the bottom right. Navigate to the folder in which the files whose names you want to import are located and open the file dateiliste.txt.

With a trick from DOS times you can import the list of files in a directory into Excel and even subdivide the file names.

Excel now calls the Text Conversion Assistant for help. Make sure that the “Separate” option is selected and set “852: Central European (DOS)” next to “File origin” so that special characters are displayed correctly in the file names. Click on Continue”.

In the second step of the wizard, you can subdivide the file names if required so that Excel assigns them to several columns. For example, if you want to transfer the names in one column and the file extensions marked with a dot to a second column, delete the checkmark in front of “Tabstopp”, place a new one in front of “Other” and enter a dot (“.” In the field next to it) ) a. For MP3 files, where the artist and title are often separated by a hyphen (“-“) in the file name, you could enter this dash as an identifier for separating the file name. The spreadsheet immediately shows you in the “Data preview” window what effect your settings have. In the third step you define the data format for the individual columns. To do this, mark the columns in the preview and select the desired format above.

When importing file names, however, you can usually leave it at the default setting “Standard”. After clicking on “Finish”, Excel copies the entries in your file list to its spreadsheet.


Related Articles

Back to top button