Please use placeholders! They will automatically escape the special characters (like the apostrophe) in your data. If you're not familiar with what these are, check out What are placeholders in DBI, and why would I want to use them?.

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]
Notice how much easier this code would be to update if your flatfile format were changed, as merlyn suggests above. I think once you get the hang of placeholders, you will really be glad you did -- they make inserting data a breeze (among their other good qualities). If you have any questions about these suggestions, feel free to ask. Also, I'd recommend reading the DBI documentation sections about placeholders, and using Super Search here at the monestary to learn more.

Hope this helped.

blokhead

In reply to Re: Replacing a ' in a string by blokhead
in thread Replacing a ' in a string by SamueD2

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.