If you work for a financial institution, you know and understand the importance of reporting. You will also learn that not only the data in the files matters, but also the data about those files. For example, you may need to keep track of the file list, modification date, file size, type, etc. on a regular basis.
In fact, that data is considered very important for statistical and analytical reasons. They can tell you how often to report, how often to revise, and how to report growth trends.
Now, the pain is in maintaining such records. The details cannot be manually noted. Therefore, you may find this Excel trick very useful in those cases.
Steps to enter folder and file details
Take a look at the image below. It’s my documents folder and I have many important documents in it. And, I regularly export files and directory listings to Excel sheets for maintenance.
Step 1: Navigate to the themes folder on Windows Explorer and copy the path to that folder. This is C:\Users\Sandeep\Documents.
Step 2: Now open the browser of your choice, paste the copied location in the address bar and press Enter. I did it on Firefox. The URL will be prefixed with file:/// and the contents of the theme directory will be displayed as a web page.
Step 3: Right click anywhere on the web page and select Save the page as. That will create an offline copy of the site.
Step 4: Again, on Windows Explorer, navigate to the location of the file you just created. Copy the full path.
By full path I mean the location of the file and the filename. Something like, C:\Users\Sandeep\Desktop\Documents.htm
Step 5: Open an Excel sheet. Switch to Data tab and choose to Get external data from the web.
Step 6: On the window that appears, paste the full path into the address bar and click Go. It will load the content.
Step 7: Notice the yellow arrows inside the little yellow boxes. Clicking on them will select a frame from the web page.
This is part of what I needed and what I chose.
Step 8: When you’re done, click Import. Instantly, you’ll have the details on an Excel sheet. Everything is sorted by column.
Do it with Command Prompt
There is another way to get a similar result. Although I personally don’t like it, I’ll put it down for you.
Open a command prompt, navigate to the themes folder and type the following command.
dir > filename.txt, where filename is your filename
Then open an Excel book and enter the contents of filename.txt into the worksheet. During the import you will have to define the column separator.
The sad thing here is that the data is not always properly formatted during this process. So not my preference.
Conclusion
We spend a lot of time organizing our computers and data. With regular use, we will store many important files in it. Then, to make sure we don’t lose them, we depend on things like password protection, antivirus, and backups. For me, the above trick was just an add-on to what we already had.
If due to some glitch I lose my data and don’t have a backup (or even lose a backup) then at least I know from my list what data I’ve lost.
Top image credit: Monrovia Public Library
Categories: How to
Source: thpttranhungdao.edu.vn/en/
