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