Lots of good advice from elsewhere about the mechanics of data extraction, however you are going to need some dirty coding skills to do anything useful with it.
Large data sets often contain corrupt lines, loopy data, strange characters, ohs instead of zeroes and every manner of formatting glitches. You need to validate and filter what you've got. (An example. Split a file into two then join them. There may well be the most subtle of flaws at the join. Possibly all normal lines are CRLF but at the join the end of line is just CR. This can go unnoticed or even cause the read-in to assume end of file!) As a minimum I would make sure that you're outputting exactly the same number of lines as you read.
Still on line-by line processing, it's very simple, and worthwhile, to add very basic sanity checking to the data. Even if a field isn't getting outputted, if it's easy to check then do it because it could indicate some more subtle trouble. Be aware that actual data may not conform to the official specs. Why does a price of -1 sometimes appear? An especially useful field to check is the last one that should always have something in it or the last in each row.
Log processing somewhere. That way you can set the process running and go to lunch. You have a record of what version of your program was used to create what outputs. Of course you're looking for '...lines rejected:0' all the time.
Bad source lines should be outputted to a failure file. (But quit after 15 lines.) You can visually examine a small amount of data to see what sort of weirdness you've got.
It may well be that inside the loop that processes each line you have to apply filters. This may not happen at the first pass, but as downstream analysis progresses you may be asked to give a more select data-set. Eg. exclude lines with products with 'test' in the name or product code starting with 9.
An often missed validation issue is missing or duplicated data. For example somehow Friday's raw data has been added to the end of Thursday's and Friday's is from the week before. How will anybody know? The network failed from 3pm to 5pm so nothing was recorded. Monday was a bank holiday where there shouldn't be any transactions but somebody has supplied data from the previous Monday. You are in a good position to do some simple sums, for example daily turnover or maximum period of no activity etc. These are bulk sanity checks used to give a human pause for thought and prompt for checking before poisoned data is passed further down the chain. It's probably not your job to decide what to do with a loopy batch, but you can highlight it and probably tweak your code to give a better data-set.
All the above is 'easy', one step at a time programming. You'll learn about automation, tidy workflows, loopy formatting and basic data anomalies. You'll also be a bit of an expert on spotting unusual data and what the fields are supposed to mean. That will be useful for...
Doing something useful with the data. You should be involved with the downstream analysis. This is not to suggest you should build analysis into your translation program, but you've got a framework ready to do it. Totals, averages, max and min, hourly, daily, weekly are all possible easy (NB Automated) outputs. You might think a database is a better tool, but for fiddly things simple coding may be better. Let me give an example: Smooth a set of data points. An easy moving average is nextPoint = (lastPoint *(0.8)) + (rawValue *(0.2)) [Adjust .8 and .2 to suit]. That's fine for continuous data but what about start of business each day? That's a special case where nextPoint = rawValue. Something to code perhaps.
Spurious data values is a good example of the cross-over between raw data crunching and analysis. When somebody punched in £175 when they meant £1.75 do we really want to include that in our analysis? It's a bit of an art, or fudge, but the raw data processor can easily calculate a mean and standard deviation for a couple of thousand data points, or an actual distribution for all rows of data. You /might/ want to throw out, mark, highlight or otherwise draw attention to unexpected values at the data crunching stage or use it to inform the analysis stage. Perhaps add another column with a blank for OK and 'H' for higher than expected and so on.
You will become a skilled craftsman, able to turn a huge tree into useful planks from start to finish. You'll learn who wants what sort of planks for what purpose and be able to saw up the raw wood in the right way to avoid splits and shakes. Moreover if you spot a diseased tree you can raise the alarm.
awk
(you'll need to read up on the "language" used, butawk
is also standard to use inside shell scripts, just likegrep
). As your text files have a different format each, you probably need a custom solution for each. As how to get Excel not to choke on large files, I have no idea, but I'd second that for large sets of data maybe Excel is the wrong tool.