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!

In reply to Re^2: Tie::Handle::CSV dynamically detecting header names by Marshall
in thread Tie::Handle::CSV dynamically detecting header names by sier

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.