Log External Data Over Time

After half a day, I will offer this to help discussion. I will say that I am not an expert on this kind of data processing/database management, so someone who is more skilled at this may have better suggestions to offer.

I might suggest a more detailed example. More detail about the text file. More detail about how you are currently processing these files. More details about what you know and don't know how to do. For example, are the two "records" given very accurate as far as the arrangement of the text file? Is there really a row of hyphens in between date and data like indicated? How many fields in the employee data? This is the kind of question that probably benefits greatly from a good sample file or files (maybe a sample of your text file and a sample of the current spreadsheet you are using to process the files that shows what is working, what you are having trouble with, and an indication of what you expect at the end of the process).

Here's a brief overview/flowchart of how I think I would process these files (note that I probably would not use VBA for this, but one should be able to program the steps in VBA as well. It depends on which programming language is preferred).

0) I am assuming that the text file is comma delimited or otherwise easily parsed into fields. I'm not sure how much difference there is if I assume rows of hypens or not.
1) Import raw data file into an empty tab. Import External Data -> From Text -> text import settings as needed -> Save query definition so that the file can easily be refreshed as it changes.
2) In an adjacent tab, use lookup functions (MATCH(), INDEX(), etc.) to rearrange the data into a more "database" format where I get one record/row per date.
Ex: date -- data field 1 -- data field 2 -- data field 3 --...
2a) I would be concerned that the lookups in this could be slow, as they will likely need to be exact match linear searches. I would try to pay attention to duplicated efforts in these lookups to try to make them as streamlined as possible.
2b) If I had any control over the program creating these text files, I would see if there were options in that program to arrange the data better.
3) With the data arranged in a "one row per record" kind of format, I would have a very nice "database" source for pivot tables (can we assume you are familiar with pivot tables?).
3a) one pivot table to do daily summary
3b) a second pivot table to do weekly summary
3c) a third pivot table to do the monthly summary

Does that look anything like the procedure you are trying to use? I get the impression that you know how to do step 1 and step 3 already, so maybe step 2 is the step that you are having trouble with?