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 http://blogs.msdn.com and http://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=”http://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=http://’+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=”http://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: http://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 (10)

  1. Jeanie says:

    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. :-)

  2. Alex Sosa says:

    Calvin,

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

    Alex

  3. Michiko says:

    Brilliant!! I tried it and enjoyed it.

    Now I feel I should blog more : )

  4. As I mentioned before, I’m not a big fan of blog stats. I really do seem to break a lot of the obvious,…

  5. It takes a lot of work to create the blog posts and code samples that I put in my blog, and I was curious…

  6. I was quoted in this Computerworld article about the MVP summit, which is happening this week in Seattle

  7. Suppose I have a table of test results with at least 4 columns: ID (unique integer) SuiteName (name of

  8. Suppose I have a table of test results with at least 4 columns: ID (unique integer) SuiteName (name of