Hey IT folks!

Hope you can help me with a rather messy text file that I am trying to import to excel.. 

The text file looks like this:
$$$
ankle swelling
HISTORY:    Trauma
COMPARISON:    []
Technique: AP, lateral and oblique views

FINDINGS: There is no evidence of acute fracture or dislocation. There is lateral soft tissue swelling seen. No evidence of any bone destruction.
IMPRESSION: Soft tissue swelling but no evidence of obvious acute fracture is demonstrated.

$$$
basic
INDICATIONS: <<Clinical Information>>
COMPARISON: [< None. >]
FINDINGS: [  ]
$$$

I would like it to go into excel as this

ColumnA   ColumnB   ColumnC
ankle swelling
  HISTORY   Trauma
ankle swelling
  COMPARISON:   None
ankle swelling   Technique:    AP, lateral and oblique views
ankle swelling   FINDINGS:   There is no evidence of acute fracture or dislocation. There is lateral soft tissue swelling seen. 
basic
  INDICATIONS:   <<Clinical Information>>
basic
  COMPARISON:   [< None. >]
basic   FINDINGS:   [  ]

the only things that are consistent that would identify each column are: 
  • The start of new title (columnA) is immediately after $$$ this indicates start of new record
  • Each of the sub titles (columnB) ends in ":" 
  • Each of the descriptions (columnC) starts after the ":" 

    Any one has any ideas? VBA script maybe? but i wouldn't know where to begin!
Thanks in advance.

4 Comments   [ + ] Show Comments

Comments

  • Is there a reliable way of determining the end of a record? Is there a $$$ at the end of a single or series of records? VBscript would indeed work to convert the existing layout into something like a comma delimited text file which would then import directly into Excel. However, as I'm getting a strong indication that you are not a programmer, how do you plan to maintain and develop any program that someone in this forum may offer?
  • Hey EdT, thanks for your reply, I don't know enough about VBscripts, this is a new task that come up and maybe the case I need to start learning about VB in more detail, the $$$ dollar is at the end of each series of records, the records within the series has ":" i am only interested in what is before and after the ":" within each series of records. hope this helps?
  • Google "Excel text to columns" you should be able to import the text files using that method.
    • lol Thanks KHaught! I know how to do text columns but what about the getting into the format i describe above?
  • Once this is imported with a "from text" import delimited by your $ to show a new record, it should be easy to split the text the way you want using text to columns delimited on your : I was able to do it in a few minutes in excel. You could then easily store it in a macro to be run against any other imported text in the future. The other option would be to split the text using formulas such as this -
    =LEFT(A12,(FIND(":",A12,1)-1)) assuming A12 contained this string from your example -
    INDICATIONS: <<Clinical Information>>

    Same could be used to find the split in any of your cells which contain the : delimiter.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share