Database template

[Bruceper]

Have you ever wanted to build your own database using excel so you can import it into your IndexU?

With this Excel file you can now build your data using the examples and headers shown.

How to use

Once your categories and links are complete in the Excel file save the excel file. Never forget to save first or you could lose all your changes.

Now choose either your links or categories sheet and go to File - Save As and choose Tab delimited database. You may receive a notice that Excel can only save the current sheet and that's fine, that's all that you want.

Now choose the other sheet and save the same way as above with a different name.

Now open each file in your favorite text editor and remove the header data.

NOTE - Failure to remove the header data WILL cause problems with your database and will create empty or incorrect categories and links.

After the header data has been removed you can now import normally through the IndexU Admin Panel.

If you have any questions feel free to ask.

Spreadsheet updated to include complete instructions on use

This file has moved locations

[FSGDAG]

This is GREAT Bruce!!! Thanks for the hard work

[Bruceper]

I've been using this, without the comments of course, for months. It makes life simple and quick.

[spin]

very nice and what I have been looking for..again thanks

[mypcteks]

Ok so category_id is the main category Id I understand that, so if I want subcategories or 2nd level cats what do i do like this:


categori_id parent_id name

1 0 testcat1
2 1 testsubcatof_testcat1
3 2 testsub2catof_testsubcat1


if that makes any sense each category regardless of whether it is a main parent cat or subcat is assigned from 1 to the last cat right?

Can someone give an example of how it would be ?

Thanks

[Bruceper]

if the parent ID is 0 then it's a top level category, if the parent ID is any other number then that category is a child of the parent, otherwise known as a subcategory.

You cannot have a child without a parent, translated that means you cannot have a subcategory if the category (parent id) does not exist.

[inrated]

Hi Bruce,

This blank xls sheet is great and does make life more simple for us directory addicts.

Can you just show an an example of how to get some 2 categories deep or 3 deep? Do we have to add another column, or do we create another sheet and work with sub-categories?

Also, when uploading the links tab delimited file, all goes well but the date shown is Dec 31, 1969.

I have also added some extra fields in the link xls sheet like, zip, address, telephone (created the extra custom fields in the admin section, but when uploading the links file, none of the fileds I created show up on the directory listing), but I do see them populated in the custom area in the admin section.

Thanks

[Bruceper]

You need to enter in a date to get it to not show Dec 31 1969. Use today's date, but make sure that the column for "new" is set to 0

Here's an explanation of how to do deep categories

cat parent catname
1 0 catone
2 1 cattwo
3 2 catthree
4 3 catfour

catone is a top level category, cattwo is a subcat of catone, catthree is a subcat of cattwo and finally catfour is a subcat of catthree

To use custom fields you MUST add them in the admin panel before you upload your database or it won't work at all. Adding them afterwards will kill the fields you added so you will need to reupload your database.

[inrated]

You need to enter in a date to get it to not show Dec 31 1969. Use today's date, but make sure that the column for "new" is set to 0

Here's an explanation of how to do deep categories

cat parent catname
1 0 catone
2 1 cattwo
3 2 catthree
4 3 catfour

catone is a top level category, cattwo is a subcat of catone, catthree is a subcat of cattwo and finally catfour is a subcat of catthree

To use custom fields you MUST add them in the admin panel before you upload your database or it won't work at all. Adding them afterwards will kill the fields you added so you will need to reupload your database.

---------------------
Thanks Bruce for your reply,

I have tried, attempted to get sub-categories listed, but no success.
The exact same way how you have explained. \

ex: Accounting is already been created and uploaded along with every other top level category, so now I am attempting to load sub-categories into accounting. example are below, of how i enter the data, and then save them as usual tab delimited, delete the unwanted header then upload. I update all category paths, and links, still nothing appears.

cat parent catname
1 0 Accounting
2 1 Firms


or have also tried like this (below) to get many sub-categories in Accounting

cat parent catname
1 0 Accounting
2 1 Firms
3 1 Tax Services
4 1 CPA's

[Bruceper]

In your example, Accounting is a top level category and Firms, Tax Services and CPA's are all subcategories of Accounting. The way you have it is proper.

Remember that if you have existing categories you'll have to change the category numbers around. If "Accounting" is not already on your database then you'll need to find the next highest number that's not being used.

So if the next category number that is available is 4301 then that would also be the parent for Firms, CPA's and Tax Services.

[badboy]

Excellent work. Thanks mate

[torambus]

Here's an explanation of how to do deep categories

cat parent catname
1 0 catone
2 1 cattwo
3 2 catthree
4 3 catfour

catone is a top level category, cattwo is a subcat of catone, catthree is a subcat of cattwo and finally catfour is a subcat of catthree

Does this mean I have to make different sheet for each of subcategories?

[Bruceper]

no, all categories on one sheet

[torambus]

no, all categories on one sheet

I tried below list on my site http://www.dallascountyguide.com but am unable to get deep category. Can anybody help? I need only Agriculture and Animal & Pet as main category. parent 2 are sub of Agriculture and Animal & Pet and parent 3 are sub of parent 2.

cat parent name
1 0 Agriculture
2 1 Equipment
3 1 Farms
4 2 Animal
5 2 Dairy
6 2 Flowers & Shrubs
7 2 Fruits & Vegetables
8 2 Organic
9 2 Poultry & Eggs
10 2 Ranch & Stable
11 1 Feed & Farm Supplies
12 1 Merchandise
13 2 Water
14 0 Animal & Pet
15 1 Animal Shelters
16 1 Birds
17 1 Dogs
18 2 Assistance Dogs
19 2 Breeding
20 2 Kennels
21 2 Training
22 1 Fish
23 1 Horses
24 2 Horse Related
25 2 Merchandise
26 2 Riding Lessons
27 2 Show
28 2 Training
29 1 Merchandise
30 2 Pet Products
31 1 Pet Services
32 2 Adoption
33 2 Animal Protection
34 2 Clubs
35 2 Pet Medical Care
36 2 Support
37 2 Training

[Bruceper]

The following sets up your two top categories.

1 0 Agriculture
2 0 Animal & Pet

Now add the subcategories for Agriculture

3 1 Equipment
4 1 Farms
5 1 Feed   Farm Supplies
6 1 Merchandise
7 1 Animal Shelters
8 1 Birds
9 1 Dogs
10 1 Fish
11 1 Horses
12 1 Merchandise
13 1 Pet Services

And now the subcategories for Animal & Pet

14 2 Animal
15 2 Dairy
16 2 Flowers   Shrubs
17 2 Fruits   Vegetables
18 2 Organic
19 2 Poultry   Eggs
20 2 Ranch   Stable
21 2 Water
22 2 Assistance Dogs
23 2 Breeding
24 2 Kennels
25 2 Training
26 2 Horse
27 2 Merchandise
28 2 Riding
29 2 Show
30 2 Training
31 2 Pet Products
32 2 Adoption
33 2 Animal Protection
34 2 Clubs
35 2 Pet Medical Care
36 2 Support
37 2 Training


Put it all together and it's done. And I've replaced your & with  . You should use   for SEO reasons and because & is not valid HTML

Now lets say you want to add a few subcategories for Training which is category 37. You would add it like this

38 37 Horse Training
39 37 Dog Training
40 37 Wife Training
41 37 Potty Training

The first number is the category number and it is unique. The second number is the parent ID. If the parentID is 0 then it is a top level category. If the parentID is a number then you will make a subcategory.