2 Replies Latest reply on Jul 20, 2015 2:07 PM by MarkGores

    parsing issue



      parsing issue


      I have a solution that takes information from our shipping computer and updates our Jobs program with the tracking number, shipper and date.  When the shipping department creates the shipments, they enter the job numbers into the note fields which I use to match up records and do the update.  The problem is there are frequently more than 1 job shipped in a shipment.  If there are a few with non-consecutive numbers they will enter them separated by commas, no problem.  If there are some that are consecutive the will enter the range separated by "--", (i.e. 65154--65162), again no problem.  My issue is every once in a while they will enter 2 ranges i.e.  65154--65162, 75117--75121.  That instance has my parsing script creating records counting from 65154 to 75121 which is around 10,000 records.

      (BTW - the system for job numbers is that the first one or two digits is the month, next is the year and last 3 a count that resets at the first of the month.  So 65154 is the 154th job received in June of 2015,  115001 would be the first job received in November, 2015)

      Is there an easy way to break the 65154--65162, 75117--75121 into 2 separate records?  That would allow my current script to parse it correctly.  Am I missing something easy?

        • 1. Re: parsing issue

          I think the first recommendation would be to not let them put job numbers into the notes field.  I would create a separate JobNumber Table, and put a small portal with something like:

          Job Number-Start    Job Number-End

          Start Field                       End Field

          Then if they just enter a number in the start field, and leave the end field blank, you could auto enter the start field number.  If they put numbers in each you can make sure the Start field is 'lower' then the End field.

          As far as parsing, is it always two dashes (--)? 

          And is it always separated by a comma?  What if they use a colon, semi-colon, period or blank space?

          • 2. Re: parsing issue

            We don't have a choice on where to put the numbers.  We're using the FedEx and UPS shipping programs and exporting csv files at the end of the day.  The solution I'm working on takes those csv files, combines the FedEx and UPS records into a third table, parses out the records then updates the "Jobs" database.

            It is usually two dashes, sometimes one for a range.  They always use a comma or a space for the non-sequential numbers. 

            I have the script so that it changes commas, spaces, colons, semicolons or periods into carriage returns.  Then it looks for -- and parses out the range by entering each number separated by a carriage return.  Then does the same thing for single dashes.

            The one place it breaks is when there are two sets of ranges in the field.  Even though the script first puts the carriage return between the two ranges it still looks at the first word and last word as the beginning and end point for the range.

            For now I put in a If patterncount (jobnumber; "--") > 1  =  "mark as an error" so I can manually go fix that record later.