5 Replies Latest reply on Dec 13, 2010 11:16 AM by illbe

    Seperate records from CSV

    illbe

      Title

      Seperate records from CSV

      Post

      Hello everyone, first of all thank you to this forum, you have been a lifesaver.

      I have data from a csv file (exported from an old Professional File database) imported into a database, the problem is that there is a field called notes (by me) that has multiple dates (2-23-55 etc.) followed by multiple coresponding notes. This is all one long string. I can parse the first date (the first date is in the very beginning of the text block on all records) by using LeftWord, the problem is that I don't know how to seperate the remaining dates from the text as the text length varies for each record. I would like to wind up with the date of the note (noteDate) in one field, and the text for each date in another (note). This may be a fairly simple thing to do in FM so please excuse my newbieness.

      Thank you for the help

      Tom

        • 1. Re: Seperate records from CSV
          raybaudi

          Hi

          put here some examples of that "notes" field.

          • 2. Re: Seperate records from CSV
            illbe

            Hi Ray, here is an example of the notes field I need to parse:

            11-10-10 SENT APP TO WMP W/CK/267. FAXD SANDY EKEY REQUEST FOR BPM & EKG. ORDERED APS DR JOHN WWW 123-8765 FAXD TO 123-4322 (IA-62). FILED 11-16-10 ENTERED POL# PER WMP PP PER GREG H2222222 NEED TO WITHDRAW APP - SAID FAMILY ISSUES AND CLIENT DOES NOT WANT TO PROCEED. TT SANDY CANCELLED MEDICAL. TT RECD FAX FROM VERISMA NEEDING LETTER OF AUTH. TT TONI @ 123-456-7404 @ VERISMA CANCELLED APS REQUEST.

            • 3. Re: Seperate records from CSV
              raybaudi

              Am I right that there are two dates inside that note ?

              There is always a full stop before a new date ?

              Which have to be the final wished result ?

              • 4. Re: Seperate records from CSV
                philmodjunk

                And how consistently formatted are your date entries?

                • 5. Re: Seperate records from CSV
                  illbe

                  1. Yes, there are two dates in that note

                  2. I'm not sure what a "full stop" is, if it's a period then no there is not always a period before a new date

                  3. I want to be able to parse the dates and connected notes into seperate records (I have ID's for each customer) with a noteDate and a noteText field, this will form a new relational table along with the customer ID. When the user brings up a customer a dated list of notes (in a portal or whatever) will be able to be viewed as well.

                  :-) not very ... The only consistant thing about these notes is that the first note date appears as the first "word" in every note.