World is now on Opti ID! Learn more

Matt Pallatt
Apr 7, 2025
  2
(0 votes)

Category Management - Going old school & trying not to break anything.

You wait a hour for a bus and then 3 come at once.

The same thing happened to me recently where multiple clients with ageing websites (Opti 11) and a new content strategy wanted to see all of their content and categories mapped out so that they could update them outside of the singlular management experience that Optimizely provides. 

We looked around for options and needless to say that whilst there were some viable solutions for Opti 12, our clients options were limited to zero with Opti 11. 

So, I set around creating a solution to allow them to better, one off, manage their categories using .... Excel!

That's right, I fired up SQL Management Studio, and VBA for Excel, and I now share with you what has been so helpful for us.

Use this at your own risk.

Get content and category data out of Opti

Temporary tables and the messages view to the rescue...

 
DROP TABLE IF EXISTS #pages
DROP TABLE IF EXISTS #categories
 
DECLARE @website varchar(256) = 'www.erm.com'
DECLARE @i int = 0
DECLARE @j int = 0
DECLARE @n int = 0 
DECLARE @m int = 0
DECLARE @catName varchar(100)
DECLARE @catNames varchar(max)
DECLARE @catIDs varchar(max)
 
CREATE TABLE #pages(pkID int identity(1,1), pageID int, linkurl varchar(500), PageName varchar(1000)) INSERT INTO #pages (pageID, linkurl, PageName) SELECT pkid, CONCAT(REPLACE(linkurl,'~/',CONCAT('"https://',@website,'/')),'"'), Name FROM tblContent INNER JOIN tblContentLanguage ON pkID = fkContentID WHERE linkurl is not null AND fkLanguageBranchID = 1 ORDER BY pkID 
CREATE TABLE #categories(pkID int identity(1,1), catID int, catName varchar(100)); --INSERT INTO #categories(catID, catName) SELECT pkid, CONCAT('"',CategoryName,'"') from tblCategory ORDER BY pkID
 
WITH RecursiveCatQ AS (
    SELECT pkid, fkParentID, CategoryName, CAST(pkid AS VARCHAR(MAX)) AS recursivePath
    FROM tblCategory
    WHERE fkParentID IS NULL
 
    UNION ALL
 
    SELECT t.pkid, t.fkParentID, t.CategoryName, CAST(r.recursivePath + '-' + CAST(t.pkid AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS HierarchyPath
    FROM tblCategory t
    INNER JOIN RecursiveCatQ r
    ON t.fkParentID = r.pkid
)
 
INSERT INTO #categories (catID, catName)
SELECT pkid, CONCAT('"',CategoryName,'"')
FROM RecursiveCatQ
ORDER BY recursivePath;
 
SET @n = (SELECT COUNT(*) FROM #categories)
SET @catNames = ',,'
SET @catIDs = ',,'
 
WHILE @i <= @n
BEGIN
    SET @i = @i + 1
SET @catNames = CONCAT(@catnames,',',(SELECT TOP 1 catName FROM #categories WHERE pkID = @i))
SET @catIDs = CONCAT(@catIDs,',',(SELECT TOP 1 catID FROM #categories WHERE pkID = @i))
END
 
PRINT @catIDs
PRINT @catnames
 
SET @i = 0
SET @m = (SELECT COUNT(*) FROM #pages)
SET @n = (SELECT COUNT(*) FROM #categories)
 
DECLARE @pageCatRow varchar(1000)
DECLARE @boolPageCat int 
DECLARE @pID int
DECLARE @cID int
 
WHILE @i < @m -- pages / rows
BEGIN
SET @i = @i + 1;
SET @j = 0
SET @pageCatRow = ''
 
SET @pageCatRow = (SELECT top 1 CONCAT(pageID,',',linkurl,',"' ,PageName,'"') FROM #pages WHERE pkID = @i)
 
WHILE @j < @n -- categories / columns
BEGIN
SET @j = @j + 1
SET @pid = (SELECT pageID FROM #pages where pkID = @i)
SET @cid = (SELECT catID FROM #categories where pkid = @j) 
SET @boolPageCat = (SELECT COUNT(*) FROM tblContentCategory WHERE fkContentID= @pid AND fkCategoryID = @cid)
SET @pagecatrow = CONCAT(@pageCatRow,',',@boolPageCat)
END
PRINT @pageCatRow
 
END
 
DROP TABLE #pages
DROP TABLE #categories


This produces something that looks like this...

Give it a copy and drop it into Excel - you might have to do your own formatting to allow your categorisers to better see what's happening - but from there, they can add 1s and 0s to the content and categories that they want to.

Push content and category data back into Opti

Once everything has been updated, take to Developer mode in Excel and you can use this...

Sub MakeMeSomeSQL()

    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    For i = 1 To lastRow
    
        For j = 4 To lastCol
        
            If ws.Cells(i, j).Value = "1" Then
            
            Debug.Print ("INSERT INTO tblWorkContentCategory (fkWorkContentID,fkCategoryID,CategoryType,ScopeName) VALUES ((SELECT TOP 1 pkID FROM tblWorkContent WHERE fkContentID = " & ws.Cells(i, 1) & " ORDER BY pkID DESC)," & Left(ws.Cells(1, j), InStr(ws.Cells(1, j), "-") - 1) & ",0,0)")
            Debug.Print ("INSERT INTO tblContentCategory (fkContentID, fkCategoryID, CategoryType, fkLanguageBranchID, ScopeName) VALUES (" & ws.Cells(i, 1) & "," & Left(ws.Cells(1, j), InStr(ws.Cells(1, j), "-") - 1) & ",0,2,0)")
            
            End If
        
        Next j
    
    Next i

End Sub

You'll want to ensure that your language branch is set, but the above will create your own SQL to reinsert content and category data, which will look a bit like...

Just make sure to remove all the existing data first before you run the SQL that's created by the above...

DELETE FROM tblContentCategory WHERE fkContentID IN (SELECT pkID FROM tblContent WHERE fkParentID = 329) -- only delete the data from pages you'll be reimporting

DELETE FROM tblWorkContentCategory WHERE fkWorkContentID IN (SELECT pkID FROM tblWorkContent WHERE fkContentID IN (SELECT pkID FROM tblContent WHERE fkParentID = 329)) -- only delete the data from pages you'll be reimporting

As I say, use at your own risk, but if you have hundreds of pages, and tons of categories, the automation here has saved us and clients DAYS if not weeks.

 

Apr 07, 2025

Comments

Please login to comment.
Latest blogs
Make Global Assets Site- and Language-Aware at Indexing Time

I had a support case the other day with a question around search on global assets on a multisite. This is the result of that investigation. This co...

dada | Jun 26, 2025

The remote server returned an error: (400) Bad Request – when configuring Azure Storage for an older Optimizely CMS site

How to fix a strange issue that occurred when I moved editor-uploaded files for some old Optimizely CMS 11 solutions to Azure Storage.

Tomas Hensrud Gulla | Jun 26, 2025 |

Enable Opal AI for your Optimizely products

Learn how to enable Opal AI, and meet your infinite workforce.

Tomas Hensrud Gulla | Jun 25, 2025 |

Deploying to Optimizely Frontend Hosting: A Practical Guide

Optimizely Frontend Hosting is a cloud-based solution for deploying headless frontend applications - currently supporting only Next.js projects. It...

Szymon Uryga | Jun 25, 2025

World on Opti ID

We're excited to announce that world.optimizely.com is now integrated with Opti ID! What does this mean for you? New Users:  You can now log in wit...

Patrick Lam | Jun 22, 2025

Avoid Scandinavian Letters in File Names in Optimizely CMS

Discover how Scandinavian letters in file names can break media in Optimizely CMS—and learn a simple code fix to automatically sanitize uploads for...

Henning Sjørbotten | Jun 19, 2025 |