Convert txt log file into a chart?

D

droog

Hello,

Could somebody help me out in this task that I have to do for work? I
was given a weblog file which is about 22 thousand lines and lists web
activity on the server and I was asked to extract just the dates and
time where the error occured and plot a chart. The dates and times are
listed in the beggining like this

2004/10/12:00:02:10 ........error
2004/10/12:00:04:23 ........error


After extracting this data I need to create a chart showing the dates
and time when the error occured.

Any help would be appreciated.
Thanks
 
D

Dave Peterson

If all that stuff is in one column, I'd insert two columns to the right.

Then do data|text to columns
Make it fixed width
the first field is ymd (or ydm???)
the second would be just the colon and I'd skip that field
and the third would be general.

2004/10/12 is the first field
: is the second field
00:02:10 is the third field

And plop it into that adjacent column(s).

(I like to keep the original column for just for a bit. If things are ok,
delete the original column (if you like).)
 
D

Dave Peterson

Then I would add the two columns to get date/time in one cell.
format it as mm/dd/yyyy hh:mm:ss
(or what you like)

Or use a helper column filled with formulas like:

=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+TIMEVALUE(MID(A1,12,8))

and format it a date/time.
 
Top