kulls,

There are lots of ways that you could do this. But firstly, you didn't say what you want to do with data_line[4|5|6]?

But anyway, lets assume that you want to ignore them. Then you could do something like:

use strict;

my (@fields, @data);

while (<DATA>) {
    chomp;
    my ($field, $value) = split;
    next if !defined $value;
    push @fields, $field;
    push @data, $value;
}

my $insert = "INSERT INTO foo ("
    . join(",", @fields)
    . ") VALUES ("
    . join(",", map { qq("$_") } @data)
    . ");";

print "$insert\n";

__DATA__
 col1  data_line1
    col2  data_line2
    col3  data_line3
          data_line4
          data_line5
          data_line6
    col4  data_line7
[download]

Update: Actually, don't do that - it's bad because it doesn't use placeholders. Here is a better method, following from the example given in the DBI recipes Tutorials.

my $fieldlist = join(",", @fields);
my $placeholders = join(",", map {'?'} @fields);

my $insert = qq{
    INSERT INTO foo ($fieldlist) 
    VALUES ($placeholders)};
[download]

You would then pass your @data list to db->execute, eg $dbh->execute(@data);

Cheers,
Darren :)