What is a Microsoft MVP? Calvin's List updated for the new century
In this definition of Microsoft MVP on the Wikipedia is a description of a list that I maintained that helped me to earn the Microsoft Most Vocal Person award over a dozen years ago. I plugged in my modem and dialed up CompuServe daily to download foxforum messages (typically something like 800 per day: pretty slow on a 1200 baud modem<g>), parsed them into a table and tabulated statistics using Fox.
Internally at Microsoft there is a web site with some Excel files that contain monthly blog statistics. Each file contains page hit information for each blog on https://blogs.msdn.com and https://blogs.technet.com. I found it a little cumbersome to use, so I wrote a little program to parse the XL files into a table and allow the user to query the info on an internal web site. The parsing code looks for certain columns on certain sheets and puts it all into a table.
The query displays blog hits over several months and shows
If you’re on the MS Net you can query it here. If not, this code shows how easy it is to create a web site on which users can query Excel data.
So now my “list” is updated for the new century.
PROCEDURE BlogStats(request, response,server,p1) && VisualFoxpro program to parse XL files into table and query over web. Calvin Hsia
IF VARTYPE(request)!='O' && if we're not being queried over web
*Parse XLS files, looking for stat columns. Some xls don't have the right columns
cmonths="" && Make a string "JanFebMar.."
FOR i = 1 TO 12
cmonths=cmonths+PADR(CMONTH(CTOD(TRANSFORM(i)+"/1/2005")),3)
ENDFOR
cBlogDir="d:\fox90\test\BlogStats"
n=ADIR(aa,cBlogDir+"\*.xls","",1) && get the XLS files into an array
CREATE TABLE BlogStats (blog c(25),date d, rss i, web i, pos i, btype c(1))
FOR nXLS = 1 TO n
cFileName=cBlogDir+"\"+aa[nXLS,1]
LOCAL oxl as excel.application
oxl = CREATEOBJECT("excel.application")
oxl.Workbooks.Open(cFileName)
oxl.Left=0
oxl.Width = 800
*oxl.Visible=1
FOR nSheet = 1 TO oxl.Sheets.Count
cSheetName=UPPER(oxl.Sheets(nSheet).name)
IF cSheetName $ "MSDN TECHNET"
oxl.Sheets(nSheet).Select
nStartRow = 0
IF ISNULL(oxl.Cells(1,1).Value)
nStartRow=1
ENDIF
* now find columns Blog, RSS, Web
nRow= 1 + nStartRow
nColBlog=0
nColRss =0
nColWeb =0
FOR nCol = 1 TO 5
sCell=oxl.Cells(nRow,nCol).Value
IF VARTYPE(sCell)='C' AND UPPER(GETWORDNUM(sCell,1))$ "RSS BLOG WEB "
sCell=GETWORDNUM(sCell,1) && some col hdrs say "RSS VIEW"
nCol&sCell = nCol
ENDIF
ENDFOR
IF nColBlog>0 AND nColRss >0 AND nColWeb >0 && we need these 3 columns
nMon = (AT(UPPER(LEFT(JUSTSTEM(cFileName),3)),UPPER(cMonths))+2)/3
dDate = GOMONTH(CTOD(TRANSFORM(nMon)+"/1/2005"),1)-1 && last day of month
?JUSTSTEM(cFileName), cSheetName,dDate
nStartRow = nStartRow+1 && skip col headers
WITH oxl
FOR nRow = 1 + nStartRow TO 4e6
cBlog = .Cells(nRow,nColBlog).Value
IF !ISNULL(cBlog)
cBlog=LOWER(cBlog)
nRss = INT(.Cells(nRow,nColRss).Value)
nWeb = INT(.Cells(nRow,nColWeb).Value)
IF MOD(nRow,50)=0
?SPACE(10),cBlog, nRss, nWeb
ENDIF
INSERT INTO BlogStats VALUES (cBlog, dDate, nRss, nWeb, nRow - nStartRow, cSheetName)
IF nRow > 16000 OR nRss + nWeb < 100 && filter out <100 hits
EXIT
ENDIF
ENDIF
ENDFOR
ENDWITH
ENDIF
ENDIF
ENDFOR
oxl.Quit
ENDFOR
ALTER table BlogStats ADD COLUMN total i
REPLACE ALL total WITH rss + web && calculate total
INDEX on blog TAG blog
BROWSE LAST nowait
RETURN
ENDIF
TRY && Let's serve up some HTML
DECLARE integer MessageBeep in win32api integer
messagebeep(128)
cBlogName=""
fCheckDistinct = .t.
IF request.QueryString("mode").count>0
cBlogName = LOWER(request.QueryString("mode").item)
fCheckDistinct = .f.
ENDIF
IF request.form("blogname").count>0
cBlogName=LOWER(request.form("blogname").item)
ENDIF
PPATH=LOWER(SET("path"))
cWho=request.ServerVariables("REMOTE_USER").item+":"+request.ServerVariables("REMOTE_ADDR").item
IF request.ServerVariables("HTTP_REFERER").count=0
cRefer=""
ELSE
cRefer=request.ServerVariables("HTTP_REFERER").item
ENDIF
INSERT INTO LOG (data,who,when,counter,refer) VALUES ("BStat "+cBlogName,cWho,DATETIME(),RECCOUNT("log"),cRefer)
SET CENTURY OFF && display only "05" not "2005"
IF !FILE(PPATH+"log.dbf")
CREATE TABLE (PPATH+"log.dbf") (data c(100), who c(40),when t, counter i, refer c(160))
use
ENDIF
IF !USED("log")
USE (PPATH+"log.dbf") IN 0 ALIAS log
ENDIF
SET TEXTMERGE ON TO memvar cRes
\I was browsing <a href="https://team/sites/blogsites/Metrics%20and%20Marketing%20Data/Forms/AllItems.aspx">the MSDN/TechNet Blog Stats</a>
\\ which are in monthly Excel spreadsheets. I wrote a crude 200 line program <a href=default.asp?Page=link&file=blogstats.htm>(source code) </a>
\\to parse the data into a table and allow the user to query it over the web. The Language Integrated Query code is using SQL SELECT to
\\get HTML results into a table.
\\Position is the rank: 1 means highest total for the month (Excel row # minus 2).
\You can see your position change over the months.
\Some blogs have been abandoned, but they still have RSS subscribers. <p>
\Enter a name or partial name like "a" or "OldNewThing" or a number from 1 to 20<p>
\<form method = "post" action = "blogstats.asp">
\<p> Blog name: <input type = "text" name="blogname" value="<<cBlogName>>">
\<input type="submit" value = "Get Stats" ><p><p>
cPict="999,999,999"
IF !EMPTY(cBlogName)
IF BETWEEN(VAL(cBlogName),1,20)
SELECT * FROM blogstats WHERE pos < VAL(cBlogName) ;
ORDER BY btype,pos INTO CURSOR foo
CursorToHTML("Blog Statistics for rank 1 to "+TRANSFORM(VAL(cBlogName)) )
ELSE
IF fCheckDistinct
SELECT distinct blog,btype FROM blogstats WHERE blog=cBlogName INTO CURSOR distblog
nTally = _tally
IF nTally = 1
cBlogName = blog && if partial match, then get complete word
ENDIF
ELSE
nTally=1
ENDIF
IF ntally =0
\No stats found for "<<cBlogName>>"
ELSE
IF ntally > 1
SELECT '<a href=blogstats.asp?mode='+blog+'>'+blog+' </a>' as blog,;
IIF(btype='M',"MSDN","Technet") as MSDNTech;
FROM distblog INTO CURSOR result
CursorToHTML('Number of blogs matching "'+cBlogName+'" = '+TRANSFORM(_tally))
ELSE
*SELECT btype,date,COUNT(*) FROM blogstats GROUP BY 1,2 INTO TABLE BlogSum
SELECT LEFT(CMONTH(bs.date),3)+" 2005" as date, ;
TRANSFORM(rss,cPict) as Rss,;
TRANSFORM(web,cPict) as Web,;
TRANSFORM(total,cPict) as Total,;
TRANSFORM(pos,"9999")+"/"+TRANSFORM(bm.cnt,cPict)+"= %"+TRANSFORM(INT(100*pos/bm.cnt)) as Position, ;
IIF(bs.btype='M',"MSDN","Technet") as MSDNTech;
FROM blogstats bs INNER JOIN blogsum bm ON bs.btype+DTOC(bs.date,1) =bm.btype+DTOC(bm.date,1);
WHERE blog == cBlogName ORDER BY MSDNTech,bs.date INTO CURSOR foo
CursorToHTML('Blog Statistics for <a href=https://'+IIF(blogstats.btype='M',"blogs.msdn.com/","blogs.technet.com/") +cBlogName+'>'+cBlogName+'</a>')
ENDIF
ENDIF
ENDIF
ENDIF
\<p><p><p><p><font size = 1><a href="https://blogs.msdn.com/calvin_hsia">Calvin's Blog</a><p>
\Generated by Visual Foxpro <<TRANSFORM(DATETIME())>></font>
SET TEXTMERGE to
CATCH TO oError
SET TEXTMERGE TO memvar cres
SET TEXTMERGE on
\Err: <<oError.errorno>>:<<oError.details>>:<<oError.Message>>
\Line:<<oError.LineNo>>
SET TEXTMERGE to
INSERT INTO LOG (who,data,when) VALUES ;
(request.servervariables("REMOTE_USER").item+":"+request.servervariables("REMOTE_ADDR").item,;
cres, DATETIME())
FINALLY
SET TEXTMERGE to
ENDTRY
response.write(cres)
RETURN ""
PROCEDURE CursorToHTML(cTitle as String)
LOCAL i
\<h3><<cTitle>></h3>
\<p><Table frame=box><tr>
FOR i = 1 TO FCOUNT()
\<th width=90><<PROPER(FIELD(i))>></th>
ENDFOR
\</tr>
SCAN
\<tr>
FOR i = 1 TO FCOUNT()
\<td><<TRANSFORM(EVALUATE(FIELD(i)))>></td>
ENDFOR
\</tr>
ENDSCAN
\</table>
RETURN
#if 1=0
*Entire contents of blogstats.asp: (for more details how this works: https://blogs.msdn.com/calvin_hsia/archive/2004/06/18/159550.aspx
<%
if isempty(session("ox")) then
set session("ox") = server.CreateObject("t1.c1")
end if
set ox = session("ox")
ox.mydocmd("set path to " + request.servervariables("APPL_PHYSICAL_PATH"))
ox.myeval "BlogStats(p2,p3,p4,this)",request,response,server
ox.MyDoCmd("clea prog")
%>
#endif