in reply to Using perl to break up sentences in rows into Separate columns

Update:
I didnt notice line 4, whose format deviates from the other sample lines in that it doesnt have a monetary amount that i could discern. I think you'll need to provide a more specific explanation of the problem, including more analysis of the data that needs parsing.

Also, please, please, please, use some HTML tags to format your post


From your very brief explanation of what you're trying to due, it appears that your data lines have: The monetary indicators are not identical, but my observation is that they are some indication of the units, i.e. pounds, dollars or euros, then the dollar amount and then a scale factor (mil or million) - im assuming others are valid.

The second column looks pretty free form, but after it should be the word 'due'. Is this always the case? So, a regex something like this could work:

$line =~ /^( # start $1 \D+ # unit ind. (US$ for ex) \d+(?:\.\d+)? # the amount \s+ # whitespace [A-Z]+ # scale (mil for ex) ) # end $1 (.+) # $2 - the second part \sdue\s # literal word 'due' surrounded by spaces (.+) # $3 $/xi; my @columns = ($1, $2, $3)
Now keep in mind that your problem statement is rather ambiguous, but i think that this regex will work for the sample data you provided.

You'll probably have to add tweaks for various additional possibilities, such as commas in the numbers. This also assumes that the word 'due' does not appear anywhere in the data that should be in the second column.

Anyway, this should get you started. Note i did not test my code.

  • Comment on Re: Using perl to break up sentences in rows into Separate columns
  • Download Code

Replies are listed 'Best First'.
Re^2: Using perl to break up sentences in rows into Separate columns
by thcsoft (Monk) on May 10, 2005 at 08:49 UTC
    i think your regex is ok. apart from that i'd put a '?' after each group, and make the regex itself act greedily.

    language is a virus from outer space.
Re^2: Using perl to break up sentences in rows into Separate columns
by mariah (Initiate) on May 10, 2005 at 13:56 UTC
    hopefully this looks better I apologize for not clarifying what the data looks like before hand, below is a larger sample of the data with each data line placed after number sign
    As you can see the data dump is pretty sloppy and that is why I need perl to clean it up.
    Thanks again
    instrument_name # Header
    #LOCAL CURRENCY#
    #FOREIGN CURRENCY#
    #144A Promissory Note Program
    #3(A)3 CP prog
    #3(A)3 CP prog auth amt CAD1 bil
    #3(A)3 CP prog auth amt US$14 bil
    #4(2) CP prog auth amt US$300 mil
    #4(2) CP prog auth amt US$5 bil
    #4(2) CP prog auth amt US$6 bil
    #4(2) Extendable CP prog auth amt US$2 bil
    #4(2) Extendible Commerical Notes prog
    #CANADIAN CP prog auth amt CAD0 mil
    #CANADIAN CP prog auth amt CAD0 mil
    #CP prog Rule 144A
    #EURO 5 bil CP prog
    #Euro CP prog auth amt US$0 mil
    #"MAJOR" multi-jurisdiction repackaging note programme #US$30 million class A secured floating rate credit-linked notes due 2010
    #"MAJOR" multi-jurisdiction repackaging note programme ¥3 billion class A secured floating rate credit-linked notes due 2010
    #"MAJOR" multi-jurisdiction repackaging note programme ¥4 billion class A secured fixed rate credit-linked notes due 2012
    #AUD190 mil 6.38% nts due 10/21/2011
    #AUD400 mil fltg rate nts due 10/21/2009
    #AUD410 mil 6.195% nts due 10/21/2009
    #Archstone I PLC
    #Asgodia CDO Ltd. US$80M CLN (Private & Confidentia Channel CDO, Limited
    #Checkers: US$195 million credit default swap (no liability issued)
    #Classes A-C Credit Linked Notes due 2008
    #EUR 471.15 million floating-rate notes
    #EUR 471.15 million floating-rate notes
    #EUR 50 million credit default swap portfolio A, series 2003-3/4
    #EUR 633.20 million asset-backed floating-rate notes
    #EUR 72 million floating rate notes
    #EUR 72 million floating rate notes
    #EUR Class C fixed spread coupon credit-linked notes series 2004-17 (Up to EUR5 billion programme)
    #EUR Class C floating spread coupon credit-linked notes series 2004-18 (Up to EUR5 billion programme)
      Ok, well thats a much larger data sample, but most of those lines in no way fit into your original problem, which means that your original specification of what you want done with it no longer applies.

      I also deal with a lot of quite poorly formatted data, and the one thing that you really need to pin down is exactly what you want done with the data.