Thought I'd share my labours.. I didn't appreciate NopAdmin's XLS import format, so I made up my own.
I was given a large category set which were nested records in XLS format.. (excuse the poor diagram)
**Every item has its own row and is nested under its parent category.**
Column A | Column B | Column C | Column D
Books
fiction
sci-fi
horror
alien
fantasy
non-fiction
comics
Games
PC
action
puzzle
Console
sports
sim
etc. etc.. (these are 4 levels deep, which is all the script can handle without editing yourself, it's easy enough when you read it).
I needed to just get them all in the DB with some default properties so they could edit the details (priceranges, description etc.) themselves.
Here's the process:
1. I used the import wizard to get this XLS file into a table (tmpcategories) on the SQL box, which looks exactly the same, but with an ID column (auto generated) and 'null' in the fields with no data. I then added an ID field which auto-incremented to assign values to the entries (starting at 1).
2. backup the category and urlrecord* tables.
*(it stores the links targets for your category list)
select * into Category_bak from Category
select * into UrlRecord_bak from UrlRecord
then check that it worked..
select * from Category_bak
select * from UrlRecord_bak
3. run this replacechar function to simplify the urlrecord update later
/****** Object: UserDefinedFunction [dbo].[RemoveSpecialChars] Script Date: 05/01/2013 00:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[RemoveSpecialChars] (@s varchar(256), @r char) returns varchar(256)
with schemabinding
begin
--Author: Christian d'Heureuse, www.source-code.biz
if @s is null
return null
declare @s2 varchar(256)
set @s2 = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
set @s2 = @s2 + char(@c)
else
set @s2 = @s2 + @r
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
end
GO
/****** Object: UserDefinedFunction [dbo].[RemoveSpecialChars] Script Date: 05/01/2013 00:51:46 ******/
4. empty the category and urlrecord tables (use delete, truncate won't work)
delete from Category
delete from UrlRecord
5. import the categories using this script.. (be aware this uses the 'tmpCategory' (backup of Category table) and 'tmpcategories' (imported XLS data) tables, change the name if you need to). It also only handles 4-levels deep, but take a look and edit as required. Please read my comments in the code, you'll probably need to edit some field and table names..
--------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @RowsToProcess int
DECLARE @CurrentRow int
DECLARE @updateRow int --this handles empty rows, just skips them
DECLARE @currentLevel1Id int
DECLARE @currentLevel2Id int
DECLARE @currentLevel3Id int
DECLARE @currentLevel4Id int
declare @parentid int
--this handles empty rows, just skips them
DECLARE @table1 TABLE ([ID] int not null identity(1,1) primary key , [name] varchar(255) not null, [parentid] int null, [currentrow] varchar(255))
INSERT into @table1 ([name],[parentid])
SELECT coalesce(f1,f2,f3,f4),null FROM tmpcategories where coalesce(f1,f2,f3,f4) is not null --or whatever field names you used
SET @RowsToProcess=@@ROWCOUNT
--select * from @table1
SET @CurrentRow=0
set @updaterow = 0
WHILE @updateRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
print 'current row: ' + cast(@currentRow as varchar(10))
-- skip empty row (the IDs aren't being copied directly, so this doesn't matter)
if (SELECT coalesce(f1,f2,f3,f4) FROM tmpcategories where ID = @currentRow) is null
begin
print 'skipping current row: ' + cast(@currentRow as varchar(10))
continue
end
else
begin
set @updateRow = @updateRow + 1
if ( SELECT f1 FROM tmpcategories WHERE ID=@CurrentRow ) is null
begin
if ( SELECT f2 FROM tmpcategories WHERE ID=@CurrentRow ) is null
begin
if ( SELECT f3 FROM tmpcategories WHERE ID=@CurrentRow ) is null
begin
if ( SELECT f4 FROM tmpcategories WHERE ID=@CurrentRow ) is NOT null
begin
print 'level 4'
set @parentid = @currentLevel3Id
--set @currentLevel4Id = @updateRow
end
end
else
begin
print 'level 3'
set @parentid = @currentLevel2Id
set @currentLevel3Id = @updateRow
end
end
else
begin
print 'level 2'
set @parentid = @currentLevel1Id
set @currentLevel2Id = @updateRow
end
end
else
begin
print 'level 1'
set @parentid = 0
set @currentLevel1Id = @updateRow
end
print 'updating current row: ' + cast(@currentRow as varchar(10))
print @parentid
--select * from @table1 where id = @currentRow
update @table1
set parentid = @parentid, currentrow = @currentRow
where id = @updateRow
print 'updated row(s): ' + cast(@@ROWCOUNT as varchar(10))
end
END
--select * from @table1
/* ********************************************************************************************** */
/* COMMENT THE FOLLOWING OUT WHEN TESTING! (uncomment the previous line to view the @table1 data) */
/* ********************************************************************************************** */
SET IDENTITY_INSERT category ON
/* I used defaults values from my backup 'Category' table, you can override these with custom defaults as you wish */
insert into category (Id,Name,Description,CategoryTemplateId,MetaKeywords,MetaDescription,MetaTitle,ParentCategoryId,PictureId,PageSize,AllowCustomersToSelectPageSize,PageSizeOptions,PriceRanges,ShowOnHomePage,HasDiscountsApplied,SubjectToAcl,Published,Deleted,DisplayOrder,CreatedOnUtc,UpdatedOnUtc)
SELECT
t.id, t.Name,
null as Description,CategoryTemplateId,
null as MetaKeywords, null as MetaDescription, null as MetaTitle,
t.ParentId as ParentCategoryId,
PictureId,
8 as PageSize,AllowCustomersToSelectPageSize,PageSizeOptions,
null as PriceRanges,ShowOnHomePage,HasDiscountsApplied,SubjectToAcl,Published,Deleted,
t.id as DisplayOrder,
convert(smalldatetime, getdate(), 120) as CreatedOnUtc,
convert(smalldatetime, getdate(), 120) as UpdatedOnUtc
FROM @table1 t,
(select top 1 * from [Category_bak]) as c
SET IDENTITY_INSERT category OFF
--------------------------------------------------------------------------------------------------------------------------------------------
Your category table should now be populated, but we're not finished!
6. Check for duplicate category names (i.e. under different parent nodes). You should probably change them otherwise your nav will have trouble figuring out which one to highlight.. you can do this in the URLRecord table, if it's easier (only affects the link text).
7. insert URL 'slugs' into the URLRecord table. It replace all special characters with '-', but you can use something else (URL-safe) if you want.
--------------------------------------------------------------------------------------------------------------------------------------------
insert into urlrecord
select c.id as EntityID, 'Category' as EntityName, dbo.RemoveSpecialChars(c.name,'-') as slug, 1 as isActive, 0 as LanguageId
FROM [Category] c
--------------------------------------------------------------------------------------------------------------------------------------------
If you don't do this, your category links won't work! (maybe check for duplicate 'slug' values while you're here)
8. Clear cache via admin section, check your links and then have some tea.
I hope this is of help to someone, it took me a while to figure out the process.
:)