Magento

I have successfully imported almost 4000 products in 700 categories with the following SQL:

########################### categories ##################################
# categories -> catalog_category_entity
INSERT INTO [prefix]catalog_category_entity (entity_id, entity_type_id, attribute_set_id, parent_id, created_at, updated_at, is_active, path, position)
SELECT categories_id, 3, 0, parent_id, date_added, last_modified, 1, ‘’, sort_order+1 FROM [old_db].categories c ORDER BY categories_id;

# categories -> catalog_category_entity_int
# 32 = anchoring status
# 0=don’t anchor, 1=anchor (show products from categories under this one)
INSERT INTO [prefix]catalog_category_entity_int (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 32, 0, categories_id, 1 FROM [old_db].categories c ORDER BY categories_id;

# categories_description -> catalog_category_entity_text
# 25 = Description, 28 = Meta Description, 29 = Meta Tags
INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 25, 0, categories_id, categories_desc FROM [old_db].categories_description c ORDER BY categories_id;

INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 28, 0, categories_id, categories_name FROM [old_db].categories_description c ORDER BY categories_id;

INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 29, 0, categories_id, categories_name FROM [old_db].categories_description c ORDER BY categories_id;

# categories/categories_description -> catalog_category_entity_varchar
# 22 = name, 24 = URL, 26 = image, 27 = page title, 30 = display mode, 38 = actual url, 39=?, 43=?
INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 22, 0, categories_id, categories_name FROM [old_db].categories_description c ORDER BY categories_id;

INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 24, 0, categories_id,
TRIM( BOTH ‘-’ FROM
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
LOWER(categories_name)
, ‘&’, ‘-’), ‘/’, ‘-’), ‘\\’, ‘-’), ‘,’, ‘-’), ‘“‘, ‘-’), ‘\’’, ‘-’), ‘(’, ‘-’), ‘#’, ‘-’), ‘)’, ‘-’), ‘ ‘, ‘-’)
, ‘--’, ‘-’), ‘--’, ‘-’), ‘--’, ‘-’)
)
FROM [old_db].categories_description c ORDER BY categories_id;

INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 26, 0, categories_id, categories_image FROM [old_db].categories c ORDER BY categories_id;

INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 27, 0, categories_id, categories_name FROM [old_db].categories_description c ORDER BY categories_id;

INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 30, 0, categories_id, ‘PRODUCTS’ FROM [old_db].categories c ORDER BY categories_id;

INSERT INTO [prefix]catalog_category_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 3, 38, 0, categories_id,
TRIM( BOTH ‘-’ FROM
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
LOWER(categories_name)
, ‘&’, ‘-’), ‘/’, ‘-’), ‘\\’, ‘-’), ‘,’, ‘-’), ‘“‘, ‘-’), ‘\’’, ‘-’), ‘(’, ‘-’), ‘#’, ‘-’), ‘)’, ‘-’), ‘ ‘, ‘-’)
, ‘--’, ‘-’), ‘--’, ‘-’), ‘--’, ‘-’)
)
FROM [old_db].categories_description c ORDER BY categories_id;

# fix mapping to default category 2=category you are importing into
# 2 is the default store category
UPDATE [prefix]catalog_category_entity SET parent_id = 2 WHERE parent_id = 0 AND path = ‘’;

# run multiple times depending on nesting of categories
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;
UPDATE [prefix]catalog_category_entity c, [prefix]catalog_category_entity p SET c.path = CONCAT(p.path, ‘/’, c.entity_id) WHERE c.path = ‘’ AND c.parent_id = p.entity_id AND p.path != ‘’;

########################### products -> categories mapping ##################################
# products_to_categories -> catalog_category_product
INSERT INTO [prefix]catalog_category_product (category_id, product_id, `position`)
SELECT categories_id, products_id, 0 FROM [old_db].products_to_categories p ORDER BY categories_id;

########################### products ##################################
# products -> catalog_product_entity
INSERT INTO [prefix]catalog_product_entity (entity_id, entity_type_id, attribute_set_id, type_id, sku, category_ids, created_at, updated_at)
SELECT products_id, 4, 4, ‘simple’, products_model, ‘’, products_date_added, products_last_modified FROM [old_db].products p ORDER BY products_id;

# products -> catalog_product_entity_decimal
# 49=price, 54=weight
INSERT INTO [prefix]catalog_product_entity_decimal (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 49, 0, products_id, products_price FROM [old_db].products p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_decimal (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 54, 0, products_id, products_weight FROM [old_db].products p ORDER BY products_id;

# products -> catalog_product_entity_int
# 69=status, 70=allow gift message, 74=visibility
INSERT INTO [prefix]catalog_product_entity_int (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 69, 0, products_id, 1 FROM [old_db].products p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_int (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 70, 0, products_id, 2 FROM [old_db].products p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_int (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 74, 0, products_id, 4 FROM [old_db].products p ORDER BY products_id;

# products -> catalog_product_entity_text
# 46=desc, 47=short desc, 57=meta keywords
INSERT INTO [prefix]catalog_product_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 46, 0, products_id, products_description FROM [old_db].products_description p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 47, 0, products_id, products_description FROM [old_db].products_description p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_text (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 57, 0, products_id, ‘’ FROM [old_db].products p ORDER BY products_id;

#products -> catalog_product_entity_varchar
# 45=name, 56=meta title, 57=meta description, 59=base image, 60=small image, 61=image, 71=url, 72=url.html
INSERT INTO [prefix]catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 45, 0, products_id, products_name FROM [old_db].products_description p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 56, 0, products_id, products_name FROM [old_db].products_description p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 57, 0, products_id, ‘’ FROM [old_db].products p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 59, 0, products_id, products_image FROM [old_db].products p ORDER BY products_id;
INSERT INTO [prefix]catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 60, 0, products_id, products_image FROM [old_db].products p ORDER BY products_id;
INSERT INTO [prefix]catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 61, 0, products_id, products_image FROM [old_db].products p ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 71, 0, products_id, ‘’ FROM [old_db].products p ORDER BY products_id;
INSERT INTO [prefix]catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value)
SELECT 4, 72, 0, products_id, ‘’ FROM [old_db].products p ORDER BY products_id;

#products -> catalog_product_entity_tier_price
INSERT INTO [prefix]catalog_product_entity_tier_price (entity_id, all_groups, customer_group_id, qty, value, website_id)
SELECT products_id, 1, 0, products_price1_qty, products_price1, 0 FROM [old_db].products p WHERE products_price1 > 0 ORDER BY products_id ASC;
INSERT INTO [prefix]catalog_product_entity_tier_price (entity_id, all_groups, customer_group_id, qty, value, website_id)
SELECT products_id, 1, 0, products_price2_qty, products_price2, 0 FROM [old_db].products p WHERE products_price2 > 0 ORDER BY products_id ASC;
INSERT INTO [prefix]catalog_product_entity_tier_price (entity_id, all_groups, customer_group_id, qty, value, website_id)
SELECT products_id, 1, 0, products_price3_qty, products_price3, 0 FROM [old_db].products p WHERE products_price3 > 0 ORDER BY products_id ASC;
INSERT INTO [prefix]catalog_product_entity_tier_price (entity_id, all_groups, customer_group_id, qty, value, website_id)
SELECT products_id, 1, 0, products_price4_qty, products_price4, 0 FROM [old_db].products p WHERE products_price4 > 0 ORDER BY products_id ASC;
INSERT INTO [prefix]catalog_product_entity_tier_price (entity_id, all_groups, customer_group_id, qty, value, website_id)
SELECT products_id, 1, 0, products_price5_qty, products_price5, 0 FROM [old_db].products p WHERE products_price5 > 0 ORDER BY products_id ASC;
INSERT INTO [prefix]catalog_product_entity_tier_price (entity_id, all_groups, customer_group_id, qty, value, website_id)
SELECT products_id, 1, 0, products_price6_qty, products_price6, 0 FROM [old_db].products p WHERE products_price6 > 0 ORDER BY products_id ASC;
INSERT INTO [prefix]catalog_product_entity_tier_price (entity_id, all_groups, customer_group_id, qty, value, website_id)
SELECT products_id, 1, 0, products_price7_qty, products_price7, 0 FROM [old_db].products p WHERE products_price7 > 0 ORDER BY products_id ASC;
INSERT INTO [prefix]catalog_product_entity_tier_price (entity_id, all_groups, customer_group_id, qty, value, website_id)
SELECT products_id, 1, 0, products_price8_qty, products_price8, 0 FROM [old_db].products p WHERE products_price8 > 0 ORDER BY products_id ASC;

#products -> catalog_product_entity_media_gallery
INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image FROM [old_db].products WHERE products_image IS NOT NULL AND products_image != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_sm_1 FROM [old_db].products WHERE products_image_sm_1 IS NOT NULL AND products_image_sm_1 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_xl_1 FROM [old_db].products WHERE products_image_xl_1 IS NOT NULL AND products_image_xl_1 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_sm_2 FROM [old_db].products WHERE products_image_sm_2 IS NOT NULL AND products_image_sm_2 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_xl_2 FROM [old_db].products WHERE products_image_xl_2 IS NOT NULL AND products_image_xl_2 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_sm_3 FROM [old_db].products WHERE products_image_sm_3 IS NOT NULL AND products_image_sm_3 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_xl_3 FROM [old_db].products WHERE products_image_xl_3 IS NOT NULL AND products_image_xl_3 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_sm_4 FROM [old_db].products WHERE products_image_sm_4 IS NOT NULL AND products_image_sm_4 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_xl_4 FROM [old_db].products WHERE products_image_xl_4 IS NOT NULL AND products_image_xl_4 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_sm_5 FROM [old_db].products WHERE products_image_sm_5 IS NOT NULL AND products_image_sm_5 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_xl_5 FROM [old_db].products WHERE products_image_xl_5 IS NOT NULL AND products_image_xl_5 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_sm_6 FROM [old_db].products WHERE products_image_sm_6 IS NOT NULL AND products_image_sm_6 != ‘’ ORDER BY products_id;

INSERT INTO [prefix]catalog_product_entity_media_gallery (attribute_id, entity_id, value)
SELECT 62, products_id, products_image_xl_6 FROM [old_db].products WHERE products_image_xl_6 IS NOT NULL AND products_image_xl_6 != ‘’ ORDER BY products_id;

#products -> catalog_product_entity_website
INSERT INTO [prefix]catalog_product_website (product_id, website_id)
SELECT products_id, 1 FROM [old_db].products ORDER BY products_id ASC;

You may need to tweak things for your needs.  I am not recommending you to use said SQL either as it is not perfect and may not do the trick for you, and im not liable if you mess something up with this.  Final note, you may need to do some tweaking to core_store_group -> root category to make this work.  I am not sure what I did at the final to make it attach perfectly.

However, unfortunately for me the admin interface can not handle the way this client has built his site because of the categories.  With no attributes, he has over categorized everything and the admin interface needs some attention to the AJAX because it pegs IE 7 at 80 percent CPU usage on a dual core centrino duo for at least 2 mins just to pull up the category edit page.  I was initially thinking this was some sort of missing index but monitoring via top(not exactly the best profiing app) showed mysql was in and out on the request.  Server was AMD X2 w/ 4gb RAM.  PHP actually was crashing with a 1GB memory limit, had to up it to 2GB just to pull the page.

Something in the javascript is looping through repeatedly is my guess, OR maybe there are some css expressions or something dangerous in there.  I think this should be addressed first before adding new big features.  It will make or break magento in the long haul with stores over 3000 products.

I am excited to see what magento has to bring to the table, maybe my SQL import caused a problem in the admin panel.  I think had I noticed that my memory limit was prolly crashing the importer, I could have got the importer to complete this without writing this SQL, but this issue will be forcing me to use other solutions at this time.  I think this can be resolved fairly easily though in time.

.. Edit: percent CPU instead of &#xCP;U

Message board slightly mangled the code (converted quotes).. proper syntax: