What is a Microsoft MVP? Calvin's List updated for the new century

Article
12/02/2005

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

ENDFOR

ENDWITH

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

\<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

Comments

Anonymous
December 05, 2005
This is great, Calvin! I ran the query and got instant results (the kind managers like to see in status reports). Thanks for making it available to the rest of us. :-)
Anonymous
December 05, 2005
Calvin,

I love it that MS managers will find this VFP demo useful :) They will ask for more.

Alex
Anonymous
March 07, 2006
Brilliant!! I tried it and enjoyed it.
Now I feel I should blog more : )
Anonymous
March 11, 2006
As I mentioned before, I'm not a big fan of blog stats. I really do seem to break a lot of the obvious,...
Anonymous
July 20, 2006
It takes a lot of work to create the blog posts and code samples that I put in my blog, and I was curious...
Anonymous
March 12, 2007
I was quoted in this Computerworld article about the MVP summit, which is happening this week in Seattle
Anonymous
May 06, 2007
http://tnij.org/camerass <a href="http://tnij.org/camerass">photo camera</a> [url=http://tnij.org/camerass/]best cameras[/url]
Anonymous
July 16, 2007
Suppose I have a table of test results with at least 4 columns: ID (unique integer) SuiteName (name of
Anonymous
July 16, 2007
Suppose I have a table of test results with at least 4 columns: ID (unique integer) SuiteName (name of
Anonymous
May 29, 2009
PingBack from http://paidsurveyshub.info/story.php?title=calvin-hsia-s-weblog-what-is-a-microsoft-mvp-calvin-s-list-updated

Partager via

What is a Microsoft MVP? Calvin's List updated for the new century

Comments

Ressources supplémentaires