Suggestions For Converting Data

I have a products file in csv format including their category, subcat, and brand name.

Now I want to take that file and split it up into 2 tables in Access. One table will contain the product data only: name, description, price, etc, etc - with a primary key (of course) and a category number. The 2nd table will be the categories for each.
The formatting of the categories table needs to be:
Code:Cat ID | Category Name | Parent ID 1 Tools 0 2 Hammer 1 3 Pliers 1 4 Hardware 0 5 Nails 4

Does that make sense?
My real problem with this is finding a suitable number format for the categories. I imported the file as a table, then I run a query on it to get the ID's for each of the category, subcat, and brand, but of course in the above table format I'll end up with duplicates.
Also, how would I then assign the final number to the product itself?