Extracting Data and Importing to Excel

[jaykf014]

Hey... I'm a novice programmer. I guess that is expected as I am posting a question here! Haven't developed too much other than simple programs in college courses. I have some knowledge of PHP, Java, HTML, and SQL. I'm not necessarily looking for someone to do my work for me (although that would be sweet), I am willing to learn and understand the process myself, I just need some (or maybe a lot of) guidance.

What I need to do is come up with a solution that allows me to run a script that scans a series of IIS5 and IIS6 (W3C) logs, extracting bandwidth information only, converting those numbers into kilobytes (as the default values are bytes), and then import this bandwidth information into Excel.

Can anyone get me jump started on this? Where should I start with? What language(s) is best for the task? What should I research and look into in order to do make this happen?

Thanks

Jason

[Expertz]

Does your output look like list of numbers. If so, look into the writedata command.

>?writedata

The line below will write the output (assuming it is a list of numbers, to a file called data.txt that you can open in excel and load. If the output is not a list of numbers, then you have to make it so. Post all of the code that you are using so that it is clear what you are trying to do.

>writedata(`c:\\temp\\data.txt`,soln3);

[Gusgr8]

You could do this. First create a blank text file, eg cracker.txt, in a convenient directory, eg C:\Project\data, and then close this blank file.
Next insert these commands in your worksheet after soln4:=

writedata[APPEND]("C:\\Project\\data\\cracker.txt",soln[2,1]);
seq(writedata[APPEND]("C:\\Project\\data\\cracker.txt",soln||k[2,1]),k=1..4);

Run the worksheet and the two commands above will copy all the soln data to the text file. Then open the text file with Excel using Files of Type Text (*.prn; *txt; *.csv) then click next, next and finish. If you want the headings to the columns (z, F1(z), etc) the easiest way is to type them into the Excel spreadsheet.

Good luck with your project,

[jaykf014]

OK this should clarify what data I am working with. Below is one entry in the log. There are hundreds of entries per log. Each log varies in size between 1KB and 20,000KB. The arrangement of the columns are slightly different between IIS5 and IIS6 logs, but that is another issue. So the bandwidth information is located in columns sc-bytes and cs-bytes. There is one entry here and the numbers corresponding to those columns are 14266 and 543. So those are the numbers I need to extract, convert to KB, add together, and import to Excel.

#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2008-10-01 04:00:05
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Cookie) cs(Referer) sc-status sc-substatus sc-win32-status sc-bytes cs-bytes time-taken
2008-10-01 04:00:19 IP.IP.IP.IP GET /URL/URL/URL/URL/URL.htm - 80 - IP.IP.IP.IP Mozilla/5.0+(compatible;+Yahoo!+Slurp/3.0;+http://URL.URL.com/URL/URL/URL/URL) - - 200 0 0 14266 543 31