7 Replies Latest reply on Apr 12, 2011 12:43 PM by philmodjunk

    I am getting a data extract from the Radiology system; the Report Text looks something like...

    mark_d2x

      Your post

      I am getting a data extract from the  Radiology system; the Report Text  looks something like this when I get it ( I have made bold the bits I need to change):

       

      {\rtf1\ansi\deff0{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}}

      \viewkind4\uc1\pard\nowidctlpar\lang2057\f0\fs20 Surname: XXXXXXXX   Forename: XXXXXXXXX   DOB: XX/XX/XXXX

      \par CRN: XXXXXXX  Sex: Female

      \par -------------------------------------------------------------------

      \par Clinical History:   

      \par -------------------------------------------------------------------

      \par Date of Report:  29/11/2008

      \par  483413  04/02/2006 XR Chest  \line\line There is a pleural based density at the left base.  Lungs are essentially clear otherwise.

      \par

      \par  483413  04/02/2006 XR Lt Humerus  \line\line There is abnormal, modeling of the upper left humerus, consistent with an old fracture.

      \par

      \par  483413  04/02/2006 XR Pelvis  

      \par \line No definite fracture or other lesion.  \line\line No clinical details and the time of reporting.\line

      \par }

       

      What I need to do is clean this up as follows:

       

      Remove:

      {\rtf1\ansi\deff0{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}}

       

      \viewkind4\uc1\pard\nowidctlpar\lang2057\f0\fs20

       

      Substitute:

      \line with carriage return

      \par with carriage return

       

      So that the report then looks like this:

       

      Surname: XXXXXXXX   Forename: XXXXXXXXX   DOB: 08/07/1925

       

      CRN: XXXXXXX  Sex: Female

       

      -------------------------------------------------------------------

       

      Clinical History:   

       

      -------------------------------------------------------------------

       

      Date of Report:  29/11/2008

       

      483413  04/02/2006 XR Chest 

       

      There is a pleural based density at the left base.  Lungs are essentially clear otherwise.

       

       

      483413  04/02/2006 XR Lt Humerus 

       

      There is abnormal, modeling of the upper left humerus, consistent with an old fracture.

       

       

      483413  04/02/2006 XR Pelvis  

       

       

      No definite fracture or other lesion. 

       

      No clinical details and the time of reporting.

       

      Given that I want to import a huge amount of data it would be better to do this in Filemaker rather than  in Excel.

       

      What I think I should do is have a new field CLEAN_REPORT just so that the original text that I imported is held.

       

      Therefore:  CLEAN_REPORT = Report Text with those changes

       

      I have been looking at TRIM and SUBSTITUTE but I am not clear how to put those together to get what I need and indeed where.

       

      Any suggestions?

       

      Best wishes,

       

      Mark