AnsweredAssumed Answered

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

Question asked by mark_d2x on Apr 12, 2011
Latest reply on Apr 12, 2011 by philmodjunk

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

 

Outcomes