I've got a data set that looks somewhat like this:
|value 1|value two|"|Value 3 was "NULL"|2001/06/06|

[download]

It is a very large file and It is being imported daily into an MS-SQL server by a SAS script

unfortunately, in this case SAS can't handle quotes very well. So I must clean the file before SAS gets its ruddy little paws on it.

I had hoped to do a quick and sexy inline thing to the effect of: 

perl -pi.bak -e "s/\|\"\|/\|\|/g" filename

[download]

sweet ... unfortunately there are too many special characters for an inline to work
then I moved on to 
open OUTFILE, ">>@ARGV[0].dat";
while (<>) {
    $_ =~ s/\|\"\|/\|\|/g;
    print OUTFILE $_;
}
close OUTFILE;

[download]

sweet, it works! no problem

but now i've found that unmatched quotes still happen at other places in the data. I must instead come up with a way of saying:

"if you find a | before you find a second quote you must substitute it for a blank space"

now one way would be to: 

while (<>) {
    split /\|/;
    my $output = "|";
    foreach my $value (@_) {
        if ($value =~ m/"{1}/) {  
            $value =~ s/"//g;
        }
        $output =. $value . "|";
    }
    print $output;
}

[download]
the two questions i have are:
1) is there a better way to do this? I figure regex's are so powerful that this could probably be done in one line. if I went out and bought that O'reilly book on mastering regular expressions do you think i'd be able to write that one myself?

2) is there a way to match any ODD number of quotes as opposed to just one quote?

In reply to Delete unmatched quotes from a delimited file? by Buckaroo Buddha

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.