dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
759
share rss forum feed


aurgathor

join:2002-12-01
Lynnwood, WA
kudos:1
Reviews:
·Frontier Communi..

[Excel] extracting numeric data from logfile and putting into Ex

I have a logfile written by mencoder, and I'd like to automatically extract some data from it and put them into an Excel spreadsheet, or into any format (i.e. comma separated) that Excel can easily read.

quote:
Video stream: 600.336 kbit/s (75042 B/s) size: 346981384 bytes 4623.828 secs 110866 frames

Audio stream: 128.000 kbit/s (15999 B/s) size: 73981701 bytes 4623.856 secs
PSNR: Y:37.43, Cb:42.30, Cr:42.84, All:38.57

I need the bolded numbers. The logfile contains several hundred pages of crapola, then a few lines of useful data, several hundred pages of crapola again, and so on.

Can Excel do this directly? (I have Excel 2000 SP3)
What would be the best and quickest way to do this?

TIA
--
Wacky Races 2012!

dave
Premium,MVM
join:2000-05-04
not in ohio
kudos:8
Reviews:
·Verizon FiOS

1 edit

Re: [Excel] extracting numeric data from logfile and putting int

On Windows, I'd use Perl. Can you program?

Otherwise you might be able to kludge a solution by findstr'ing to reduce it to the lines you need, followed perhaps by some clever use of the 'for' command.

Is this a once-only deal or a recurring task?

avguser

join:2003-02-09

1 edit
reply to aurgathor
Not sure how your data is structered, but the approach I would take is as follows. assume the Video Stream text string is in cell A1 The formula is:
MID(A1,FIND(": ",A1,1)+2,FIND("kbit",A1,1)-FIND(": ",A1,1)-2)

That formula takes th string after the first ": " and up to the "kbit" You can do similar with the second string you need
Edit..might need a "Value( )' around the string above if you need to conver tot a number.


aurgathor

join:2002-12-01
Lynnwood, WA
kudos:1
Reviews:
·Frontier Communi..
reply to aurgathor
I can program, I'm just looking for something quicker and slicker than a C program. I did enter some data by hand, but I got sick of it, and I expect to do it enough that I definitely want to automate it as much as possible. Will take a look at perl.

The data is just a plain text file containing a few pages of stuff like:
quote:
[...]
Opening video filter: [expand osd=1]
Expand: -1 x -1, -1 ; -1, osd: 1, aspect: 0.000000, round: 1
Opening video filter: [harddup]
Opening video filter: [scale w=480 h=272]
==========================================================================
Opening video decoder: [mpegpes] MPEG 1/2 Video passthrough
The selected video_out device is incompatible with this codec.
Try appending the scale filter to your filter list,
e.g. -vf spp,scale instead of -vf spp.
VDecoder init failed
Opening video decoder: [ffmpeg] FFmpeg's libavcodec codec family
Selected video codec: [ffmpeg2] vfm: ffmpeg (FFmpeg MPEG-2)
==========================================================================
Forcing output FourCC to 30355844 [DX50].
MP3 audio selected.
[...]

and lots and lots of:
quote:
1 duplicate frame(s)!
Pos: 0.3s 9f ( 0%) 0.00fps Trem: 0min 0mb A-V:-0.013 [0:0]
Skipping frame!
Pos: 0.5s 14f ( 0%) 0.00fps Trem: 0min 0mb A-V:-0.030 [0:0]
Skipping frame!
Pos: 1.2s 31f ( 0%) 0.00fps Trem: 0min 0mb A-V:-0.034 [32:127]
demux_mpg: 24000/1001fps progressive NTSC content detected, switching framerate.
Pos: 3.0s 76f ( 0%) 0.00fps Trem: 0min 0mb A-V:-0.085 [62:127]
1 duplicate frame(s)!

plus the few lines I need, already shown in the OP.

First, one need to search for "Video stream:", and everything will be in that line, or the one shortly after it that starts with "PSNR:"
--
Wacky Races 2012!

dave
Premium,MVM
join:2000-05-04
not in ohio
kudos:8
Reviews:
·Verizon FiOS
said by aurgathor:

I can program, I'm just looking for something quicker and slicker than a C program.

That's why Perl exists.

Your algorithm is something like this:

while read next line from stdin
  if matches regexp /Video stream ......./
      save the numbers that the regexp snagged
  else if matches the other one
       write one line to stdout containing all required data
 

and that should be expressible in about the same number of lines of Perl.


PapaDos
Cum Grano Salis
Premium,MVM
join:2001-02-08
Lasalle, QC
kudos:3
reply to aurgathor
Since you want to import in Excel, the best solution is to use a macro in Excel to do the job. Regexp are available in VBA macros too, no need to install and use another programming/scripting language...

Unless you want to learn a new language, of course...
--
Festina Lente


PhoenixDown
FIOS is Awesome
Premium
join:2003-06-08
Fresh Meadows, NY
kudos:1
Reviews:
·Verizon FiOS
reply to aurgathor
Here's my method when I feel lazy (also not a great programmer).

1 - import log file to excel ensuring everything is in Col A and not spread out.

2 - Add to columns, moving the data in Col A to Col C.

3 - Add #'s to Col A so you can sort either by =A1+1, etc. Then copy and paste as values so they don't change with a sort.

4 - Using the filters, filter for "Video Stream:" and "Size:" and "PSNR:". toss an X into the appropriate rows in Col B

5 - I generally delete the unused rows at this point.

6 - In col D, use a formula to extract the values (some combo of search/substitute/replace).

that's a very basic idea
--
1/22/2012 Delegate Count
Newt 25 | Romney 14 | Ron Paul 10 | Santorum 8


aurgathor

join:2002-12-01
Lynnwood, WA
kudos:1
Reviews:
·Frontier Communi..
said by PhoenixDown:

Here's my method when I feel lazy (also not a great programmer).

1 - import log file to excel ensuring everything is in Col A and not spread out.

"03/06/2012 03:09 PM   111,035,851 DH_800x480_HQA13_2500.avi.txt"
The above is one log file that contains a grand total of 26 lines with useful data, so the data must be extracted prior, or during the import process.

In any case the extractor is already done:
quote:
1, 801.679, 1951868, 49.48, 50.69, 51.10, 49.90
2, 829.254, 2019004, 49.90, 50.98, 51.32, 50.28
3, 828.170, 2016366, 49.89, 50.98, 51.32, 50.27
4, 827.726, 2015285, 49.89, 50.98, 51.32, 50.27
5, 827.703, 2015229, 49.90, 50.98, 51.32, 50.27
6, 827.826, 2015528, 49.89, 50.98, 51.32, 50.27
[...]

And Excel imports this data in the exact same way.
But what I want this displayed a little differently so that data in column 1 would be in row 1 like: 1, 2, 3, 4, 5, 6, etc., and so on with the rest.
--
Wacky Races 2012!


2kmaro
Think
Premium,ExMod 1 BC
join:2000-07-11
ColossalCave
kudos:1

2 edits
This doesn't look too difficult to me. Get in touch via email through 'HelpFrom @ JLathamSite.com' (remove the spaces to make a valid email address) - I'll either need one of your log files attached to work from, or a workbook with the extracted data as you've shown in your last post to work with.
If we're going to work from the extracted data as you've provided, then please include an example sheet of how you would want to display those 6 rows of data. I'm thinking right now that you would want

1.................2................then 3, 4, 5 and 6 as columns?
801.697...........829.254
1951868...........2019004
49.48.............49.90
50.69.............50.98
51.10.............51.32
49.90.............50.28


Oh, and yes, Excel macro could do this directly as a single step process. I have to do this kind of thing a good bit with text files derived from a personnel database system where each record consists of several rows of totally different data - find a particular row within the record and then extract specific values from that row.
--
...then THINK! again.


2kmaro
Think
Premium,ExMod 1 BC
join:2000-07-11
ColossalCave
kudos:1

2 recommendations

reply to aurgathor
Got it! I actually expected you to send the entire original data file, but I went with what you sent.

The attached workbook should do what you want. As the Instructions sheet indicate, it starts off by allowing you to browse to the .csv file and then it opens it and imports the data from it, transposing it into column format in the process. It closes the .csv file without making any changes to it at all.

Remember that macros must be enabled (Macro security set to at least Medium) for this to work.

As for being concerned about the size of the original data file, that wouldn't have been a problem - we would have opened it as a text file and read the data 1 line at a time and picked out the information we wanted from it. But this works just fine since you already had a process built up to do that stuff.
--
...then THINK! again.


aurgathor

join:2002-12-01
Lynnwood, WA
kudos:1
Thanks a lot, it works fine!