Re: Tie::Handle::CSV dynamically detecting header names

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.

Comment on Re: Tie::Handle::CSV dynamically detecting header names

Replies are listed 'Best First'.
Re^2: Tie::Handle::CSV dynamically detecting header names by Marshall (Canon) on Mar 25, 2009 at 17:25 UTC
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!	[reply]

Replies are listed 'Best First'.

Re^2: Tie::Handle::CSV dynamically detecting header names
by Marshall (Canon) on Mar 25, 2009 at 17:25 UTC

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!

[reply]