I don't think it's unnecessarily complicated to import the data into an table, validate it and extract what you need.  It's called a staging table and that's the way I do all my importing of external data.  If you insert directly to your destination table, you don't have the ability to validate it or clean out the garbage you don't need.  I'd definitely go this route.

If the columns aren't consistent, then you are looking at some type of dynamic solution.  I don't know what you're up against, so I can't know what to recommend.