text handling issues in CR 11

[thiagarr]

Hi,

This is part of a post from Data access forum titled "Access logfile from UNIX server in CR XI", posted yesterday.

I have been trying to get the data in CR today. I have had good success in that respect though there are some "exception handling" issues I need some advice on.

I use CR 11 and the source file is a text file and I select Access / Excess and text file in the options. The logfile has thousands of lines of data and each line has lots of unwated text. I show 4 sample lines from the file below

2006.10.09  01:20:22 [ExecuteThread: '16' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (Tooling) - FILTER: 3

2006.10.09  02:28:34 [ExecuteThread: '16' for queue: 'weblogic.kernel.Default'] a.s.xxx.com[5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (INSERTS) - FILTER: 2

2006.10.09  04:42:48 [ExecuteThread: '5' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (animal DNA) - FILTER: 2

2006.10.09  05:18:21 [ExecuteThread: '6' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (ibg) - FILTER: 3

All we want to use from the above lines is the date from the beginning of the line and the text that is enclosed in the curved brackets. Sample CR output will be something similar to the following: (The last number shown here is the count of each category displayed in descending order, which is already being done now).

Date Category Count

2006.10.09 Tooling 20
2006.10.09 INSERTS 18
2006.10.09 animal DNA 16
2006.10.09 ibg 15

Now, things seem OK except on tow scenarios as follows:

1 Whenever there is a a comma character encountered within the brackets, I get only the part of the string that is after the left bracket and before the comma. It looks like when CR comes across a comma, it seems to skip the rest and go to the next line?

2 When the text within brackets is enclosed in a double quotes ("), the whole selection is returned as blank entry and returned with empty result.

I am not sure what could be the reason for this.


Some of the formulae used are:

to get all characters to the right of the left bracket:

@left1 - mid({Oct_2006_txt.Title},instr({Oct_2006_txt.Title},"(")+1)

To get the characters to the right of the right bracket:


@rightt1 - trimright(mid({Oct_2006_txt.Title},instr({Oct_2006_txt.Title},")",1)+1))

to get the required characters:

Replace ({@left1},{@Right1},"")

Any suggestions on how the above issues can be sorted out would be greatly appreciated.

 

[lbass]

Try:

left({Oct_2006_txt.Title},10) + " "+
extractstring({Oct_2006_txt.Title},"(",")")+" "+
trim(right({Oct_2006_txt.Title},2))

This assumes that the number can be one or two digits, but would work even if always only one.

-LB

 

[thiagarr]

LB,

Thank you for the response. Definitely, the extractstring function made my life easy. I am not sure why the book has it somewhere else away from the string functions, but that part worked good. Thank you for that.

But the behaviour of the other two issues I mentioned still persists. I am providing examples of some of the the lines I have issues with here for your reference.

2006.10.09  12:19:09 [ExecuteThread: '17' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (child,s pants) - FILTER: 2

2006.10.23  19:18:40 [ExecuteThread: '21' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (Medication for Seizures, Keppra) - FILTER: 2

2006.11.06  10:08:02 [ExecuteThread: '21' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: ("used auto parts: door") - FILTER: 2

2006.10.22  20:29:58 [ExecuteThread: '24' for queue: 'weblogic.kernel.Default'] a.s.xxx.com[5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (Used Auto Parts: Carburetor) - FILTER: 2

2006.10.26  04:49:48 [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: ("Plastic Bags SAMPLE") - FILTER: 2

2006.10.26  04:49:34 [ExecuteThread: '19' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (Plastic Bags SAMPLE) - FILTER: 2

2006.10.24  12:44:24 [ExecuteThread: '21' for queue: 'weblogic.kernel.Default'] a.s.xxx.com [5] weblogic DocDeterminationEJB.keywordSearch  NO KEYWORD MATCH: (Ladies Tee Shirt-SAMPLE") - FILTER: 2

child,s pants - shows up as child
Medication for Seizures, Keppra - shows up as Medication for Seizures
While Used Auto Parts: Carburetor is OK, "used auto parts: door" is part of the blank entry returend. Same result in plastic bags sample entry also.
Ladies Tee Shirt-sample seems fine (with only one quotes on the right)

What else can I try to fix the issues?

Thank you for your continued support.

 

[lbass]

Please copy the formula you are now using into the thread.

I tested my formula by creating text like yours, and it worked fine, so I'm not sure what the issue is.

-LB

 

[thiagarr]

LB,

Thank you. Maybe there is some other setting in my PC that is causing the issue. I will also try and repoduce this at home and see what happens.

In the meantime, the formula used is (I display three different fields in the report. This is the formula for obtaining the text from the lines.

extractstring({Oct_2006_txt.Title},"(",")")

Thank you.

 

[thiagarr]

LB,

I tried the few lines I posted here (copied them in to a text file) and the formula I used here and got exactly the same result posted earlier.

Formula used:

extractstring({Oct_2006_txt.Title},"(",")")

Result:

Date_created keyword
2006.10.09 child
2006.10.23 Medication for Seizures
2006.11.06
2006.10.22 Used Auto Parts: Carburetor
2006.10.26
2006.10.26 Plastic Bags SAMPLE
2006.10.24 Ladies Tee Shirt-SAMPLE


I am not sure what I am doing worng here. Thank you.

Thanks and regards,

Chandra

 

[lbass]

I don't see anything wrong. You could try single quotes instead of double, and see if that makes any difference. Or try adding in spaces, as in:

extractstring({Oct_2006_txt.Title},' (' , ') ')

I don't really think this should matter, but just in case.

-LB

 

[thiagarr]

LB,

No, as suspected there was no change. I added another line with child's pants instead of child,s pants and that came up perfect.

It is a mystery for me. Do any of the default settings in CR matter??

 

[lbass]

I don't know. Sorry.

-LB

 

[thiagarr]

LB,

Thank you for your help.

I am researching this further and I will post back, if and when I find a solution.

BTW, I tried the file with only one line of data and the result was same. I also tried with different search criteria, etc and as soon as it came across a comma or a ", it had erroneous data returned.

Thank you for your support and help.

 

[thiagarr]

Hi,

I just tried creating an entry or two with comma in the text file and even without any formula, the text is split to two fields and displayed before the comma as one field and after the comma as the second field. So, the issue is not in the formula we are using but in the way CR reads the input file.

So, basically even if I call it a text (and not csv) file, it treats it like a CSV file.

You said you tested the formula by creating text like mine. I am wondering what you did different to what I have done in my file.

Thank you and sorry to keep bothering you.

 

[lbass]

I just created a formula and placed text in that, including commas and quotes.

-LB

 

[thiagarr]

LB,

Thank you LB for the clarification.