If you work for a financial organization you would know and understand the importance of reporting. You would also know that it’s not just the data in the files that’s important but also data about the files. For example, you might need to keep track of the list of files, modification dates, file size, type etc. on a routine basis.
In fact, such data is considered very crucial for statistical and analytical reasons. They can tell you the frequency of reporting, frequency of modification and report growth trends.
Now, the pain is in maintaining such records. It is not possible to manually note the details. Hence, you might find this Excel trick very useful in those scenarios.
Steps to Import Directory and File Details
Take a look at the image below. It is my documents folder and I have many important documents in it. And, I regularly export the file and directory list to an Excel sheet for maintenance.
Step 1: Navigate to the subject directory on Windows Explorer and copy the path to that directory. Here it’s C:\Users\Sandeep\Documents.
Step 2: Now open a browser of your choice, paste the copied location on the address bar and hit Enter. I did it on Firefox. The URL will be prefixed by file:/// and contents of the subject directory will be rendered as a web page.
Step 3: Right-click anywhere on the web page and choose Save Page As. That will create an offline copy of the web page.
Step 4: Again, on Windows Explorer, navigate to the location of the file you just created. Copy the complete path.
By complete path I mean the location of the file and the file name. Something like, C:\Users\Sandeep\Desktop\Documents.htm
Step 5: Open an Excel sheet. Switch to Data tab and select to Get External Data From Web.
Step 6: On the window that comes up, paste the complete path on the address bar and hit on Go. It will load the contents.
Step 7: Take note of the yellow arrows contained inside small yellow boxes. Clicking on them will select a frame from the web page.
Here’s a section of what I needed and what I selected.
Step 8: When you are done click on Import. In no time you will have the details on an Excel sheet. Everything arranged in column to column.
Do it With Command Prompt
There is one more way to get a similar result. Though I personally do not prefer it, I will put it down for you.
Open command prompt, navigate to the subject directory and type the following command.
dir > filename.txt, where filename is the name of your file
Then open an Excel book and import the contents of filename.txt to the sheet. During import you will have to define column separators.
The sad part here is that the data isn’t always formatted properly in this process. Thus, not my preference.
We spend a lot of time in organizing our computers and data. With regular usage we end up storing numerous important files in it. Then, to be sure that we do not lose them we depend on stuff like password protection, antiviruses and backups. To me the above trick is just an add-on to what we already have.
If at all due to some malfunction I lose my data and do not have backup (or lose the backup as well) I would at least know from my list the data that I have lost.