Create an ASP page, inserting the ASP code below at the very start of the page. You will need to create a database table to hold the results (see below) and enter a valid connection string in the variable indicated.

Insert the HTML code below between the <body> tags of your ASP page. It simply outputs the data extracted to the page so you can check it is working as it should.

Use the SQL code to create queries in your database which aggregate the data you have extracted.

Please note the following:

  • No warranties or guarantees are offered for this code. It has been tested and used by us, but your system settings, eBay changes, and other factors may stop it working as it should.
  • You use this code at your own risk. We are not responsible for any damage or complaint arising from its use.
  • You may freely use and adapt this code for any purpose, as you see fit. Please credit this site if you do so.

ASP Code

  'First you will need a database table with the structure below, 
  'or equivalent
  'CREATE TABLE [tblEbayCategories] (
  '[CatID] [int] IDENTITY (1, 1) NOT NULL ,
  '[CatNumber] [int] NOT NULL ,
  '[TopLevelName] [nvarchar] (50) NOT NULL ,
  '[CatName] [nvarchar] (50) NOT NULL ,
  '[ItemCount] [int] NOT NULL )
  Dim strDBConnection, objXMLHttp, strURL, strResponse, reAllCats
  Dim reLevel1Cats, reLevel2Cats, Match, Matches, strOutPut
  Dim strTopLevelCat, strSQL, con, strCatName

  'Set the variable strDBConnection to connect
  'to your database

  'Get page of first-level and second level 
  'category data from eBay
  Set objXMLHttp = Server.CreateObject("MSXML2.ServerXMLHTTP.4.0")
  strURL = ""
  objXMLHttp.Open "GET", strURL, False
  strResponse = objXMLHttp.ResponseText
  Set objXMLHttp = Nothing

  'Set up regular expression objects
  'The reLevel1Cats object matches top-level categories
  'The "$1" placeholder matches the Category Name specifically
  Set reLevel1Cats = New RegExp
  reLevel1Cats.Global = True
  reLevel1Cats.IgnoreCase = True
  reLevel1Cats.Pattern = "<a href=""http:\/\/page" & _
    "s\.ebay\.com\/\w{1,20}\-index\.html\?from=R12""" & _
  'The reLevel2Cats object matches 2nd-level categories
  'The following placeholders are used:
  '$1 = Category Number
  '$2 = Category Name
  '$3 = Item Count
  Set reLevel2Cats = New RegExp
  reLevel2Cats.Global = True
  reLevel2Cats.IgnoreCase = True
  reLevel2Cats.Pattern = "<a href=""http:\/\/listings\.ebay\." & _
    "com\/aw\/plistings\/list\/category(\d{2,5})\/index\.html" & _
    "\?from=R12"">([^<]*)<\/a><\/FONT><font size = ""1""> \((" & _
    "\d{1,10})\)" & _
  'The reAllCats object adds the first two regular expressions
  'with an OR operator - it matches both first-level and second-level
  Set reAllCats = New RegExp
  reAllCats.Global = True
  reAllCats.IgnoreCase = True
  reAllCats.Pattern = reLevel1Cats.Pattern & "|" & reLevel2Cats.Pattern
  'Initialise the database connection
  Set con = Server.CreateObject("ADODB.Connection")
  con.Open strDBConnection
  'Uncomment the next two lines below to clear the 
  'database table before adding data
  'strSQL = "TRUNCATE TABLE tblEbayCategories; "
  'con.Execute strSQL
  'Execute the regular expression which matches both first-level 
  'and second-level categories then iterate through the resulting
  'collection of matches, checking whether each one is a 
  'first-level or second-level category
  'If it is a first-level category, store the name in the 
  'strTopLevelCat variable and build an HTML table including the
  'category name as a header
  'If it is a second-level category, add the data in the 
  'placeholders to the HTML table and execute a SQL query to add
  'it to the database
  Set Matches = reAllCats.Execute(strResponse) 
    For Each Match in Matches
      If reLevel2Cats.Test(Match.Value) = True Then '2nd-level category
        strOutput = strOutPut & reLevel2Cats.Replace(Match.Value, & _
          "<tr> <td>$1</td> <td><img src='images/spacer.gif' " & _
          "height='1' width='10'></td> <td>$2</td> <td>$3</td" & _
          "> <td>items</td> </tr>" & vbNewLine)
        'Lines below escape apostrophes for SQL query
        strCatName = reLevel2Cats.Replace(Match.Value, "$2")
        strCatName = Replace(strCatName, "'", "''")
        strSQL = reLevel2Cats.Replace(Match.Value, "INSERT INTO " & _
          "tblEbayCategories (CatNumber, TopLevelName, CatName, " & _
          "ItemCount) VALUES ($1,'" & _
          Replace(strTopLevelCat, "'", "''") & "','" & _ 
          strCatName &"',$3);") & vbNewLine
        con.Execute strSQL
      Else: 'First-level category
        strTopLevelCat = reLevel1Cats.Replace(Match.Value, "$1")
        strOutput = strOutput & "<tr><td colspan='5'>&nbsp;" & _
          "</td></tr><tr><td colspan='3'><h2>" & strTopLevelCat & _
          "</h2></td></tr>" & vbNewLine
      End If
  Set con = Nothing


  <%Response.Write("<table>" & strOutPut & "</table>")%>

SQL Code

This counts the number of items in each first-level category:

	SELECT TopLevelName, SUM(ItemCount) AS TotalItems
	FROM  tblEbayCategories
	GROUP BY TopLevelName

This returns the top 20 most popular second-level categories:

	SELECT TOP 20 TopLevelName, CatName, SUM(ItemCount) AS TotalItems
	FROM  tblEbayCategories
	GROUP BY CatName, TopLevelName

Efficient Era
xSellco 1