Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
LitLIB: Get data from files example (Read 2544 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
LitLIB: Get data from files example
01.11.07 at 22:17:35
LitLIB: Get data from files example
In this example I will explore how you can use litLIB to extract data from files and use it in your spreadsheets.  
For an overview of the litLIB software please see
I want to achieve the following. My website statistics are collected weekly by a tool on the webserver. For each week there is a historical file showing things as the number of successful requests and the MB's people downloaded from the site. I have about 7 years worth of history with in total some 350 of these statistics reports. I would like to bring all this data together and show graphs where I display the successful requests against time or the amount of data requested against time.
To do this I must use the litLIB =Dir() function to get the files in a directory. I have saved all the report files into one directory: c:\logstats. The formula =Dir("c:\logstats\*.html",1) will return the first html file found in the directory. The formula =Dir("c:\logstats\*.html",2) will give the next file, and so on.
Once I have all the file names I can get the contents of the files using the =FileContents() litLIB function. An example of the use of this function: =FileContents("c:\logstats\2005-Dec-19-stats.html"). Of course I will not hard code the file names and use the cell address where the file names have been returned using the =Dir() function.
With the contents of the file in a cell I can now use the litLIB =InStr() function to find the position of a certain substring in the file contents. Inspecting the HTML reports I observe how the number of interest is recorded in the html file:  
<br><b>Successful requests:</b> 6,804 </b>
It seems I have to search for "Successful request:</b> " first. Once I know where this string is I can read the number. I need to read everything until I encounter the </b> string. Then I need to remove the comma so the result is 6804 which I need to convert to a number. The formulas used are:
=InStr(1,$E4,"Successful requests:</b> ")
Note that E4 contains the file contents. The result of the function is the position in the file where this string occurs.
Note that L4 in this formula is the result of the previous InStr. I need to look for the next </b> starting where I found the Successful requests text.
This function was used to get the number string from the file contents. L4 was the position where I found Successful requests:</b>. M4 was the position where I found </b>.
Everything in between in the number 6,804.
This formula was used to first replace the comma with a blank. That results in 6804. This number is displayed as a string in Excel so I do a todouble on the number to get a real number displayed in the cell. The number can be used without problems to create graphs.
The resulting graph nicely shows the successful requests in each file. To finalise the exercise I need to figure out the date each file was created so I get a data point (date, successful requests) that can be plotted.
The file names of the HTML reports are like this: 2005-Dec-19-stats.html.  
I used the following formula to get the date part from the file name:
Note that B4 is the file name and I take all the characters from the left up to 11 positions from the end ("-stats.html" = 11 positions). The resulting text needs to be converted to a date for which I used the litLIB toDate() function:
where C4 is the result of the previous formula, the date string.
In my sheet I now have in the D column the dates of the reports and in the O column the figures for the successful number of requests. The values are easily plotted using the Excel graph wizard.
Back to top
« Last Edit: 02.11.07 at 21:09:08 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1