Need to extract lines of text

[jpeters01]

Hi, I have data that looks like this:

Accession#: 14CP123

Reason for study:
1. Shortness of breath
2. Chest pain

Summary:
1. First diagnosis
2. Second diagnosis
3. Third diagnosis

The data is stored as {document_text_c.line_text} where each line of text is stored separately as {document_text_c.line_text} with a different line count.

I need to pull the text into a spreadsheet with column headers:
Acc#, Finding #1, Finding #2, Finding #3

and row of data that displays:
14CP123, First Diagnosis, Second Diagnosis, Third Diagnosis


I appreciate your help!

Jan

 

[SkipVought]

Hi,

This does not seem to be a Crystal Reports question. Unless I'm vastly mistaken, this should be posted in forum707, as you are probably using Microsoft Excel.

Please give a concrete example of the FIRST file name and the LAST file name, where your source data resides.

I assume that the FIRST file contains
[tt]
Accession#: 14CP123
[/tt]
I assume that the SECOND file contains
[tt]

[/tt]
I assume that the THIRD file contains
[tt]
Reason for study:
[/tt]
...and so on.

Please post your original question, concrete examples requested and answer my assumptions in forum707, if this is not for CR but rather Excel.

 

[pmax9999]

I can see how this could be a CR question but don't understand the data sufficiently to be able to offer a solution. Assuming it is CR, please post some sample data (how it would look if the field was added to the report and the field set to "can grow" if the text is over multiple lines), the ASCII character that creates the line breaks, and what you want the output to look like using the sample data.

Cheers
Pete

 

[BettyJ]

I believe your data is stored in one field named {document_text_c.line_text}. I would also like to see your answer to pmax9999's question.

I am not sure if this will work correctly but you can try the following formulas:
@Acc#
[tt]trim(split(split({document_text_.line_text},":")[2],chr(13))[1]);[/tt]

@all_dx
whileprintingrecords;
[tt]split({document_text_.line_text},":")[4];[/tt]

@Finding #1
[tt]split(trim(split({@all_dx},".")[2]),chr(10))[1];[/tt]

@Finding #2
[tt]split(trim(split({@all_dx},".")[3]),chr(10))[1];[/tt]

@Finding #3
[tt]trim(split({@all_dx},".")[4]);[/tt]


Place @Acc#, @Finding #1,@Finding #2 and @Finding #3 in Details section.

To pull the text into a spreadsheet,
File -->Export -->Export Report --> Microsoft Excel 97-2000(XLS)

 

[Charliy]

Betty, one problem with that is that if he's using actual diagnosis codes, then they may or may not also contain a period. A typical set of diagnoses could look like: 030.01 297 202.2

I think the way to get there would invlove shared variables with a lot of complex formulas to populate them. This is made more difficult because there doesn't seem to be anything to group by.

 

[BettyJ]

Thank you, Charliy, I didn't think of that.
Then if the formulas are changed to the following it may work:
@Finding #1
[tt]mid((split({@all_dx},chr(10))[2]),4);[/tt]

@Finding #2
[tt]mid(split({@all_dx},chr(10))[3],4);[/tt]

@Finding #3
[tt]mid(trim(split({@all_dx},chr(10))[4]),4);[/tt]

 

[jpeters01]

Thank you all for your replies...sorry for not being more specific. I am using Crystal XI. The data is not stored in files, there are no file names, no carriage returns. The data comes into the application via HL7 message and lines are determined by line length. Each line is stored in a table where each line of text is {document_text_c.line_text} with a different line count {doc_text_c.line_cnt}. I am hoping this will better explain how the data is stored in the database.

SUMMARY: {doc_text_c.line_text} {doc_text_c.line_cnt} = 49
1. Left ventricular function normal.{doc_text_c.line_text} {doc_text_c.line_cnt} = 50
2. Culprit vessels right coronary. {doc_text_c.line_text} {doc_text_c.line_cnt} = 51
3. Single vessel disease. {doc_text_c.line_text} {doc_text_c.line_cnt} = 52
4. 95% stenosis in proximal RCA. {doc_text_c.line_text} {doc_text_c.line_cnt} = 52
5. Successful percuatneous coronary interventional revascularization of (line_cnt 53)
proximal right coronary artery. {doc_text_c.line_text} {doc_text_c.line_cnt} = 54
6. Good stent expansion. {doc_text_c.line_text} {doc_text_c.line_cnt} = 55

I need to export the data to an excel file but am having trouble getting the output to display correctly in columns and rows here, so the Excel column headers are:

Finding #1, Finding #2, Finding #3

In the first row this should display:
In the cell under Finding #1 Left ventricular function normal.
In the cell under Finding #2 Culprit vessels right coronary.
In the cell under Finding #3 Single vessel disease.

I have excluded the Acc# from the request because I am pulling that from a field, not the text, so is not a problem. Again I appreciate your help with this!

 

[pmax9999]

So what is it that associates line numbers 49 to 55 with the patient/diagnosis. How would I know that line 48 or 56 relate to a different patient/diagnosis? Is that the Acc# field you refer to, and if so can we assume the report is grouped by that field? If not currently grouped by that field, is there anything preventing you from taking that approach?

Please remember that we are not familiar with your environment (application, data structure etc), so all we have to go on is your explanation. The more information you can provide the more likely we can assist.

Cheers
Pete