Shell Programming and Scripting

Hi all,

I am due to start receiving a weekly csv containing around 6 million rows. I need to do some processing on this file and then send it on elsewhere.

My problem is that after week 1 the files that I will receive are likely to contain data already received in previous files and I need to strip this data out before sending on.

Initially my plan was to keep a list of each row of data sent and then to check if each row in a new file is already present in my sent list. However it soon became clear that at week 2 I would be checking each of 6 million rows to see if they appeared on a list of 6 million already sent, but at week 5 would be checking against 30 million rows.

I was hoping that someone may have a more efficient way to achieve this.

It is likely that the data will start to be purged after week10 so I would say a max sent list of around 60 million rows.

Any ideas would be appreciated

Being a theoretical question as opposed to a real how-to question, what are you already doing to process the first file? Bear in mind that what you're asking for is more of a framework question than it is a scripting issue.

There are plenty of ways to skin the cat, but which way have you started to do it? No sense in us providing a method that doesn't fit your approach.

We must assume you have a mainstream database engine which can handle CSV files and a computer which has capacity for this task. You really don't mention much about the data or the computer.

At a design level, each record must contain a unique key and whatever information is a parameter to the "purge". Unless you know the source "purge" rules your database of "data already processed" will just grow.


It would make more sense to fix the data feed design at at source. A convention is to mark the record in the source database with a unique extract run reference to prevent repeat extracts - whilst also allowing a rerun.

I've had to create a differential list before for a similar task.

Records 1 to 100 would be sent, followed by 90 - 300, followed by 250 - whatever.

Each time I would create a list of the last N lines captured. In my case, 5 was sufficient, you may need more or less. I would then search for the last lines that I've captured and process from there. Upon completion, I create my new 'last N lines' and repeat for the next time 'round.

Eventually, the solution is to fix the distribution method to be consistent.

Please show us the structure of your csv file (fields, keys...)

Jean-Pierre.

CSV is txt file, is command diff not suitable for you ?

Unique key - this is not required, record as a whole could be unique and there is no need for unique columns in it. Worst, after applying normalization or some form of transformation, 2 records could be unique or not be so.

Fixing the problem at scope, is definitely out of question, though I agree it makes sense to do that, most of the time, its completely out of scope. Not everything flows in line. We need to work on what has been received or what could be potentially received in a real world scenario.