Instead of trying to figure out the headers and formatting on the fly, you should probably prep the header row. For example, load the file, read the header line and then make it all lowercase and save it somewhere. Then use the new file for processing in your script.

A lot of places spend time prepping/formatting data before they actually process it. It is a great place to catch errors that might otherwise be overlooked. As for the guy changing 'City' to 'Country' well there are limits on what you can be expected to do. Garbage In will result in Garbage Out.

I've got code that does something like this. I set up a "translation alias DB" (which is just a text file that I set up to be easily edited and parsed by me). So this winds up essentially being a hash table that shows that a,b,c,x all mean City, etc. So there is a "normalization" step.

This alias table just gets built empirically. If some name shows up that doesn't translate, I add it to the alias DB. If you are lucky this process converges and fewer and fewer "new terms" show up. Sounds like you need to be alerted when new columns show up too.

One idea is to use DBI::CSV, that way you can make SQL queries using the standard "normalized" terms. And if some new column is in there, it won't matter. Of course if this user puts the country into the city column you will have to fix the file manually before using it! In your case say: name, contactname, organization or whatever all mean "name" or "contactname"; e-mail, EMail means email, etc.

update:Looks like your code has the name vs column thing figured out! Great job! You do want to "disconnect" column number from field name. Also for some of my users I wrote Excel spreadsheet app for them to help thing out. Sometimes helping the user generate better data is a good way to go as it saves hassle at your end!

<disclaimer>I commend your desire to make your system flexible and useful, and I'm certainly not a BOFH, but...</disclaimer>

Sometimes, you can just make things too hard on yourself. I'm not saying that this is the case here--but in general, trying to make a system idiot proof is impossible. You can bend over backwards trying to do it, but if the users continually send you any random crap and expect your code to parse it and deliver the messages, neither you nor your clients are going to be happy with the system. In cases similar to this, I simply have the system look at the header, and if it can't find the columns it wants, then send an error EMail telling the client what the problem(s) are, and let them fix the problem and send it again. Since they're using CSV files, they're likely to be generating 'em from Excel, so they can rename the columns appropriately...

Again, I'm not advocating that you frustrate your clients. I'm simply suggesting that sometimes there are situations that can be (1) a time sink, (2) continually frustrating, and (3) anti-profitable. Just make sure that you're not putting yourself in a situation where you're going to have to continually "fix" your code because your user can't give you what the program wants. Been there, done that, got the T-shirt, washed it enough times for the lettering to fade...

I actually have those checks in place right now, believe it or not. If the correct headers are not found in the CSV, it emails them stating that the CSV is improperly formatted with an attachment of a correctly formatted CSV for them to use.

Alas, my employer knows this client is important, so it is their request that I make it idiot-proof. It's very frustrating. Sometimes being a programmer really sucks.

This module has a couple of other features that make it my preferred module for handling unpredictable user-uploaded csv files. It handles multiline data well, it lets you to apply filters to pre-process the data, and it has very easy-to-use error handling.