Only Taking Certain Values From A List In Perl

First I will describe what I have, then the problem. I have a text file that is structured as such ----------- Start of file----- name,name2,ignore,name4,jojobjim,name3

Solution 1:

You would typically put the set of valid indices in a list and use array slices after that.

@valid = grep { defined($column_mapping{ $headers[$_] }) } 0 .. $#headers;

...

my $sql = sprintf 'INSERT INTO tablename ( %s ) VALUES ( %s )',
  join( ',', map { $column_mapping{$_} } @headers[@valid] ),
  join( ',', ('?') x scalar @valid);
my $sth = $dbh->prepare($sql);

...

my @row = split /,/, <INPUT>; 
$sth->execute( @row[@valid] );

...

Solution 2:

Because this is about four different questions in one, I'm going to take a higher level approach to the broad set of problems and leave the programming details to you (or you can ask new questions about the details).

I would get the data format changed as quickly as possible. Mixing CSV columns into an XML file is bizarre and inefficient, as I'm sure you're aware. Use a CSV file for bulk data. Use an XML file for complicated metadata.

Having the headers be an XML comment is worse, now you're parsing comments; comments are supposed to be ignored. If you must retain the mixed XML/CSV format put the headers into a proper XML tag. Otherwise what's the point of using XML?

Since you're going to be parsing a large file, use an XML SAX parser. Unlike a more traditional DOM parser which must parse the whole document before doing anything, a SAX parser will process it as it reads the file. This will save a lot of memory. I leave SAX processing as an exercise, start with XML::SAX::Intro.

Within the SAX parser, extract the data from the <csv> and use a CSV parser on that. Text::CSV_XS is a good choice. It is efficient and has solved all the problems of parsing CSV data you are likely to run into.

When you finally have it down to a Text::CSV_XS object, call getline_hr in a loop to get the rows as hashes, apply your mapping, and insert into your database. @mob's solution is fine, but I would go with SQL::Abstract to generate the SQL rather than doing it by hand. This will protect against both SQL injection attacks as well as more mundane things like the headers containing SQL meta characters and reserved words.

It's important to separate the processing of the parsed data from the parsing of the data. I'm quite sure that hideous data format will change, either for the worse or the better, and you don't want to tie the code to it.