次の方法で共有

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

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