Crystal modifying data type - formula to undo?

[IAmKarl]

I am reposting this question again, I cant seem to find anyone who can fix this problem..... and i really dont want to have to buy a 5k$ program *a special analyzer* to do what i can do in crystal -


I am pulling data from a txt file that is comma delimited, it is a log file in the format of
---
Date/time, Port Number, Ip Address, DNS Address, Direction, Ip Address, DNS Address, User, Auth Code
---
all on one line, one line per transaction - tens of thousands of lines

so, an example example might be as follows

1/4/2003 15:02:10, 80/www, 14.123.232.98, machine-15.somehost.com, Outgoing, 85.42.195.23,

http://www.foo.com,

Usr236592, 44kfjel6

now crystal sees this whole file perfectly, and it is working as desired from this file, with the exception of it sees the ip addresses, in the form of *for instance* 14.123.232.98 (although those numbers can be anywhere between 1 and 255), for some reason its calling this field a currency, and it will only show the first and second number set out of the 4, so for instance, it sees that as 14.123 - and its ignoring the data following it, and i need a way to tell crystal to stop treating the field as a currency and display it as a string, whether theres a trick to do it, or if i have to run the whole field through a formula, there is only one ip address per field, and let me stress that crystal is reading this whole file correctly and is treating all the other different fields correctly.

Karl P

 

[lbass]

I think this works to extract the IP Address, assuming your text field always starts with a date and time, and is followed by a number with "/

http://www"

after it--and I don't know if those assumptions are valid. The date field could vary in length and this would still work. You might have to fool with this a bit if the "/

http://www"

are not regular features of your text field:

if left(

replace(left(
(mid({yourtextfield},instrrev({yourtextfield},".",45)-11))+
(mid({yourtextfield},instrrev({yourtextfield},".",45)+3)
), 15) ,","," &quot,1) = "/" then

replace(replace(left(
(mid({yourtextfield},instrrev({yourtextfield},".",45)-11))+
(mid({yourtextfield},instrrev({yourtextfield},".",45)+3)
), 15) ,","," &quot,"/

http://www",

" &quot else

if left(

replace(left(
(mid({yourtextfield},instrrev({yourtextfield},".",45)-11))+
(mid({yourtextfield},instrrev({yourtextfield},".",45)+3)
), 15) ,","," &quot,1) = "w" then

replace(replace(left(

(mid({yourtextfield},instrrev({yourtextfield},".",45)-11))+
(mid({yourtextfield},instrrev({yourtextfield},".",45)+3)
), 15) ,","," &quot,"w", " &quot else

replace(left(

(mid({yourtextfield},instrrev({yourtextfield},".",45)-11))+
(mid({yourtextfield},instrrev({yourtextfield},".",45)+3)
), 15) ,","," &quot

-LB

 

[IAmKarl]

Ok, first off, thank you very much for a response to this, i am newer to Crystal reports, but as im looking at this i think your parsing the whole line, crystal reports upon looking at this file automaticly breaks down this file as it would a database *ie in field explorer under database fields and temp_txt (which the name of the datafile i am pulling off of is named temp.txt) you see entries for all the different colums as if there individual database colums, so all i am looking for is a formula to parse the colum as opposed to the whole line, for instance the first ip address crystal sees as field temp_txt.ip(client) and the second ip address is seen (to crystal reports) as temp_txt.ip(server) im just looking for a function to tell crystal reports to treat temp_txt.ip(client) and temp_txt.ip(server) as strings, and not currency, any help/suggestions?

 

[lbass]

I'm sorry for misunderstanding the first time. I spent some time trying different ways of working with this field, just substituting an IP number for your field, and I see what you mean--the formulas (using picture, replace, split, etc.) always fail because of the periods--unless there are quotes around the number--but that's what your problem is in the first place--getting the field to be treated as text. I wonder whether you could run a report with just this field, export it to Wordpad (or?), do a find (space)/replace (&quot and add the quotes, and then somehow use this as a separate data source--but I don't have a clue whether that's actually possible. Maybe someone else can help.

-LB