Extract data from several text files to a graph in Excel

[Tinribs]

Hi All.

I have to do a daily report thing that includes backup space usage etc. We're running a bit low on free space on our SAN and I want to convince management that it needs looking at so I've decided that I need to show them a graph of data usage that will give a rough estimate of how much time we have left until we run out of space.

The daily report is a standard text file in which I manually enter the amount of data backed up and the amount of free space remaining (along with other stuff). All the files are kept in the same folder.

I have no coding experience (well - almost none) so I'm cheekily begging for some major assistance.

I need to extract the numbers from two specific lines in all the text files and export that to Excel.

The two lines are always the same and don't appear anywhere else in the file:

Size (TB): 35.07Capacity (TB): 7.66

I'm guessing that if the files are all in the same folder the name of the file isn't important but they're all named for the date the report was created (ie today's is "1st October 2021.txt")

 

I'm guessing this can be done in powershell but my knowledge of this and its associated terminology doesn't go far enough to let me even start.

Any help would be appreciated!

TIA

Mark P.

[Ditto]

I love these sorts of challenges! How structured is the text file? I'm sure it's possible to use powershell, but I'm wondering if the data concatenated and then imported to another worksheet in your work book. If yes, you could probably find a solution using something like XLOOKUP.

 

Once at this point, the real fun starts when you start to do forecasting. I don't have a lot of Excel experience on this, but I believe you can do time series forecasting which in theory should allow projections that take in to account termly data variations.

 

If there is nothing confidential in the data and you were happy to share a few days or weeks worth of data, I'd be happy to see what solution I can come up with. No worries if your not comfortable with that, but if OK drop me a PM.

 

There are of course other options, such as Python, R, and even maybe a relational database like SQLite that could be used. I've dabbled in all of those but I used to be a professional programmer. What's always a frustration with these tasks is that masses amount of time can be saved, but there's a learning curve to go through and in the work environment we never get time, to spend time, learning how to save time!!!