• Your quoting problem will disappear when you use placeholders. See the DBI manpage for details.
• Please, go a little easier on the shiftlock key.
• Variables that include numbers as part of the names are almost always a sign of misdesign.
• How much code will you have to change when you add the 63rd column to your spreadsheet? Isn't there any way to get column headings from the sheet, so you can do a real dynamic mapping of the needed columns?
• Short of that, how about not copying every variable all over creation, but simply creating a map (hash) from database name to positional index, and using that map both to create the columns to insert and to select the proper value from an array for the placeholder. There's other code here in the catacombs to show how to do that.

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.

Also, I'd recommend not using temporary variables inside your while loop for all the data. With placeholders, you can just pass the entire @data array as arguments for the SQL statement, and also reuse the SQL statement for each insert statement. Here's an example of how you can avoid using the temporary variables (untested):

my @fields = qw/CLIENT RECORD CONTROL CUSIP .../;

... # open file and run sanity checks

my $columns = join ',', @fields;

# create the correct number of placeholders
my $placeholders = join ',', ('?') x @fields;

my $sql = "INSERT INTO table ($columns) values ($placeholders)";

my $sth = $dbh->prepare($sql);

while ( defined $line = <INFILE> ) {
  chomp $line;
  my @data = split /\t/, $line;

  ... # probably should check to see if all the fields are defined.

  # then insert using our statement, passing the values of @data to
  # replace the placeholders in the SQL statement
  $sth->execute(@data);
}
[download]

Hope this helped.

blokhead

As an added benefit, you have to prepare your SQL statement only once, because it no longer contains any variable data. You just execute() the prepared statement with the new data for the placeholders.

The code could look a bit like this — obviously, you must merge it in with what you've got, at the appropriate places:

my $sql = "INSERT INTO mf_forms (CLIENT, RECORD, CONTROL, CUSIP, FROMA
+CCT, F_IND, TOACCT, T_IND) VALUES (?,?,?,?,?,?,?)";
my $sth = $dbh->prepare($sql);
[download]

$sth->execute($CLIENT, $RECORD, $CONTROL, $CUSIP, $FROMACCT, $F_IND, $
+TOACCT, $T_IND);
[download]

If this is a CGI application, you're going to want to check out prepare_cached(). If you're going to do SELECT statements, see also bind_columns() and fetch().

------
We are the carpenters and bricklayers of the Information Age.

Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

Makeshifts last the longest.