Skip to main content

Move products from Zen Cart to Magento

This python script will connect to a Zen Cart mysql database, grab all the existing products and create a Magento import csv file.

text/x-python create_product_export.py — 4 KB

File contents

import MySQLdb

def connect_to_mysql():
    db = 'zen'
    host = 'localhost'
    user = 'root'
    password = ''
    return MySQLdb.Connection(db=db, host=host, user=user, passwd=password)

def get_products():
    column_list = ['sku', 'price', 'weight', 'name', 'description', 'qty']
    def package_results(res):
        new_results = []
        
        for product in res:
            new = {}
            
            for i in range(0, len(column_list)):
                new[column_list[i]] = product[i]
                
            new_results.append(new)
    
        return new_results
    
    conn = connect_to_mysql()
    
    cursor = conn.cursor()
    sql = """SELECT products.products_model as sku, 
                    products.products_price as price, 
                    products.products_weight as weight,
                    descriptions.products_name as name,
                    descriptions.products_description as description,
                    products.products_quantity as qty
             FROM zen.products products
             INNER JOIN zen.products_description descriptions 
             ON products.products_id = descriptions.products_id"""
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    return package_results(results)
    
    
csv_columns = {
    'store' : 'admin', 
    'websites' : 'base', 
    'attribute_set' : 'Default', 
    'type' : 'simple', 
    'sku' : '', 
    'category_ids' : '', 
    'has_options' : 0,
    'name' : '',
    'meta_title' : '', 
    'meta_description' : '', 
    'image' : '',
    'small_image' : '', 
    'thumbnail' : '', 
    #parse name to get url key and path...  path ends in .html
    'url_key' : '', 
    'url_path' : '',
    #
    'custom_design' : '', 
    'options_container' : 'Block after Info Column', 
    'required_options' : '',
    'image_label' : '', 
    'gift_message_available' : 'Use config', 
    'price' : 0, 
    'weight' : 0,
    'description' : '', 
    'short_description' : '', 
    'meta_keyword' : '',
    'custom_layout_update' : '',
    'status' : "Enabled", 
    'tax_class_id' : "Taxable Goods",
    'visibility' : 'Catalog, Search', 
    'enable_googlecheckout' : "Yes", 
    'qty' : 10,
    'min_qty' : 0, 
    'use_config_min_qty' : 1, 
    'is_qty_decimal' : 0,
    'backorders' : 0, 
    'use_config_backorders' : 1, 
    'min_sale_qty' : 1,
    'use_config_min_sale_qty' : 1, 
    'max_sale_qty' : 0, 
    'use_config_max_sale_qty' : 1, 
    'is_in_stock' : 1, 
    'low_stock_date' : '',
    'notify_stock_qty' : '', 
    'use_config_notify_stock_qty' : 1,
    'manage_stock' : 0,	
    'use_config_manage_stock' : 1,
    'stock_status_changed_automatically' : 0, 
    'product_name' : '',
    'store_id' : 0, 
    'product_type_id' : 'simple', 
    'product_status_changed' : '',
    'product_changed_websites' : '', 
    'special_price' : '', 
    'special_from_date' : '', 
    'small_image_label' : ''
}
csv_columns_ordered_list = [
    'store', 'websites', 'attribute_set', 'type', 'sku', 'category_ids', 
    'has_options', 'name', 'meta_title', 
    'meta_description', 'image', 'small_image', 'thumbnail', 
    'url_key', 'url_path', 'custom_design', 'options_container', 
    'required_options', 'image_label', 'gift_message_available', 
    'price', 'weight', 'description', 'short_description', 
    'meta_keyword', 'custom_layout_update', 'status', 
    'tax_class_id', 'visibility', 'enable_googlecheckout', 
    'qty', 'min_qty', 'use_config_min_qty', 'is_qty_decimal',
    'backorders', 'use_config_backorders', 'min_sale_qty',
    'use_config_min_sale_qty', 'max_sale_qty', 'use_config_max_sale_qty', 
    'is_in_stock','low_stock_date', 'notify_stock_qty', 
    'use_config_notify_stock_qty', 'manage_stock', 'use_config_manage_stock',
    'stock_status_changed_automatically', 'product_name', 'store_id', 
    'product_type_id', 'product_status_changed', 'product_changed_websites', 'special_price', 
    'special_from_date', 'small_image_label'
]
def generate_nice_url(value):
    value = value.replace(' ', '-').replace('/', '-').replace('\\', '-')
    value = value.lower().replace('--', '-').replace('--', '-').replace("#", "")
    value = value.replace('"', '').replace("'", '').replace(',', '').replace(".", '-')
    return value.replace("!", '').replace("&", "and")
    
import csv
products = get_products()
csv_file = csv.writer(open('import_products.csv', 'w'), delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)

csv_file.writerow(csv_columns_ordered_list)

for product in products:
    row = csv_columns.copy()
    
    for column, value in product.items():
        row[column] = value
    
    name = row['name']
    row['product_name'] = name # for some reason this is stored twice...
    
    generated_url = generate_nice_url(name)
    row['url_key'] = generated_url
    row['url_path'] = generated_url + '.html'
    
    writable_row = []
    for key in csv_columns_ordered_list:
        writable_row.append(row[key])
 
    csv_file.writerow(writable_row)