Run your own MMO
Run your own Strategy MMO! Easy to customize, addictive, and great for building a community of regular visitors!

 

Importing from CSV

In Category OpenCart Modifications
Article published 13/10/2012 11:31:57am
 
Importing from CSV

In this article we'll examine importing your products, categories, and manufacturers from a CSV file into OpenCart using MySQL. The process is surprisingly simple, and can be performed quickly and painlessly with simple MySQL statements.This article assumes you have some basic knowledge of MySQL and OpenCart in general.


  1. Temporary tables
  2. Importing CSV data to MySQL table
  3. Importing new manufacturers
  4. Importing new Categories
  5. Importing new products
  6. The example CSV we'll be using (download)

The Temporary Tables

Whilst MySQL has great support for temporary tables, we will actually be using permanent ones.  This will allow us to review imported information through the MySQL client to see what's happening, find problems, etc.  Our table structure will be built as follows in the opencart database:

CREATE TABLE `import_products`(
	sku varchar(100) NOT NULL,
	price decimal(18,2) DEFAULT 0.00 NOT NULL,
	stock int unsigned DEFAULT 0 NOT NULL,
	name varchar(255) NOT NULL,
	description text,
	category_name varchar(100) NOT NULL,
	weight decimal(12,3) DEFAULT 0.0 NOT NULL,
	manufacturer_name varchar(100) NOT NULL,
	is_imported tinyint(1) DEFAULT 0 NOT NULL,
	id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE `import_manufacturers`(
	name varchar(100) NOT NULL,
	is_imported tinyint(1) DEFAULT 0 NOT NULL,
	id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE `import_categories`(
	name varchar(100) NOT NULL,
	is_imported tinyint(1) DEFAULT 0 NOT NULL,
	id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY);

Each line in the CSV should have the category name and the Manufacturer name of the product.  This import will assume you are only using one-level deep categories (you will need to adapt the script for more complex category structures).  The product table has a column for each column of information we wish to import from the CSV file.  We have also added two additional columns; an ID column to keep track of each row with a unique ID, and an is_imported flag to keep track of products (and categories and manufacturers) that already exist in our shop.We also create a table for the manufacturers and for the categories we'll discern from the product information. This is to make the MySQL statements clearer to understand, and to help avoid some MySQL pitfalls with nested SELECT statements.This MySQL code should be executed on your database which contains your opencart installation in order to create the tables.  Once completed, this should not need to be done again!

Importing CSV data to MySQL table

Now begins the script itself!  We'll start by removing any data from the last time we ran the script (as this script will be run often to keep available product lists up to date) and making sure the indexes we will use exist:

DELETE FROM `import_products`;
DELETE FROM `import_manufacturers`;
DELETE FROM `import_categories`;
ALTER TABLE `product` ADD INDEX `sku` (`sku`);
ALTER TABLE `import_products` ADD INDEX `sku` (`sku`);

This removes any existing data in the table, allowing us to continue afresh:

LOAD DATA INFILE 'product_list.csv' INTO TABLE `import_products`
	FIELDS TERMINATED BY ','
	LINES TERMINATED BY '\r\n'
	IGNORE 1 LINES
	(sku, price, stock, @dummy, name, description, category_name, manufacturer_name, weight);

Look confusing?  It's not too bad!  Let's break this down...LOAD DATA INFILE 'product_list.csv'Tells MySQL to load the data from the file 'product_list.csv'.  This is the CSV file we want to load our product data from...INTO TABLE `import_products`...Is the table into which we want MySQL to load the information from this file.FIELDS TERMINATED BY ','Tells MySQL that each of the columns on each line of the file will be seperated by a comma.  Depending on your datasource, it may use pipes (|), semicolons (;), tabs (\t) or other characters.LINES TERMINATED BY '\r\n'So that MySQL knows where each line finishes and to begin parsing the next row, we need to determine which end of line character(s) are used.  In our example we'll be using '\r\n' (commonly used by files created on or for windows platforms).IGNORE 1 LINESThis tells MySQL to ignore the first line it fetches.  We do this because in our example the first line is simply the column headers, so it is not product data.  If you do not have a line with column headers, you can leave this bit out, or you can increase the number to make it ignore other comment or header lines in the CSV file.(sku, price, stock, @dummy, name, description, category_name, manufacturer_name, weight);Finally, these are columns into which we'll import the corresponding columns from the CSV file.  You'll notice one of these isn't a column name, it's instead @dummy (a user-created variable, hence the @ symbol at the beginning).  In our data source we have an empty column after the stock level which we don't want to import, so we just assign its value to a variable that we don't use!  You can do this to any other columns you wish to skip in this way...

Importing new Manufacturers

Firstly, we need to select all of the different manufacturers our products use and move them to our import_manufacturers table.  We can do this as follows:

INSERT INTO `import_manufacturers` (name) SELECT DISTINCT(manufacturer_name) FROM `import_products`;

We are inserting just the name of each unique (distinct) manufacturer we find in the import_products table.  We can now compare this table with our OpenCart information to see which (if any) manufacturers already exist:

UPDATE `import_manufacturers` AS t1
	JOIN `manufacturer` AS t2 ON t1.name=t2.name
	SET t1.is_imported=1;

We compare the names from our import_manufacturers table with those from the manufacturer table (part of the opencart default tables), and any that have a match we flag the import_manufacturers is_imported value as 1.  We can now use this information to import all new manufacturers into opencart!

INSERT INTO `manufacturer` (name, image, sort_order)
	SELECT name, '', 0 FROM `import_manufacturers` WHERE is_imported=0;

The is_imported flag now lets us find all of the manufacturers which don't already exist in OpenCart and create them!  We select their name, and we also select an empty string (the single quotes) and the value 0 for the manufacturers image and sort_order respectively (as in our example we do not have this information).

INSERT IGNORE INTO `manufacturer_to_store` (manufacturer_id, store_id)
	SELECT manufacturer_id, 0 FROM `manufacturer`;

To make sure our manufacturers show up in our store, we now copy all of the new manufacturer IDs (the old ones are ignored, as they would be duplicates) along with the default store ID (0) from the manufacturer table.

Importing new Categories

It's slightly more tricky to import the new categories as we have a bit more to keep track of.  The method we'll be using to keep track of new categories will be to add an import_id column to the category table and remove it once we're done:

ALTER TABLE `category` ADD COLUMN import_id BIGINT UNSIGNED DEFAULT 0 NOT NULL;

This adds our temporary column; we'll now flag the categories that already exist and import the new ones:

INSERT INTO `import_categories` (name)
	SELECT DISTINCT(category_name) FROM `import_products`;
UPDATE `import_categories` AS t1
	JOIN `category_description` AS t2 ON t1.name=t2.name
	SET t1.is_imported=1;
INSERT INTO `category` (image, top, `column`, sort_order, `status`, date_added, date_modified, import_id)
	SELECT '', 1, 1, 1, 1, NOW(), NOW(), id FROM `import_categories` WHERE is_imported=0;

This gives us the basic data for each new category; a blank string for the image, each is considered a top-level (parent) category, we give each 1 side column, and the same sort_order (1).  The date_added and date_modified are the time the script ran (NOW()), and we set the import_id as the ID of the manufacturer in the import_manufacturers table.Open Cart uses a seperate table for all of the text information (of which we'll only be importing the name), which we'll now import with the help of the import_id:

INSERT INTO `category_description` (category_id, language_id, name, description, meta_description, meta_keyword)
	SELECT t1.category_id, 1, t2.name, '', '', '' FROM `category` AS t1
	JOIN `import_categories` AS t2 ON t1.import_id=t2.id WHERE t2.is_imported=0;

This helps us to find every newly created category id and insert the corresponding categories into the category_description table.  We also use the default lanugage id (1) for each row and a blank string (the single quotes) for the description, meta_description, and meta_keywords.  Finally, we can tie each new category to our store as we did with the new manufacturers, and then remove that temporary column:

INSERT IGNORE INTO `category_to_store` (category_id, store_id)
	SELECT category_id, 0 FROM `category`;
ALTER TABLE `category` DROP COLUMN import_id;

Importing new Products

Phew! Almost there!  We've just got to get those products into the store now.  Once again, we'll flag existing products. We'll use the SKU, as this should be individual for each product:

UPDATE `import_products` AS t1 JOIN `product` AS t2 ON t1.sku=t2.sku SET t1.is_imported=1;

This allows us to see, once again, which are our new products, and import them accordingly:

INSERT INTO `product` (sku, quantity, stock_status_id, manufacturer_id, shipping, price, tax_class_id, date_available, weight, weight_class_id, status, date_added, date_modified)
	SELECT t1.sku, t1.stock, 7, IF(ISNULL(t2.manufacturer_id), 0, t2.manufacturer_id), 1, t1.price, 9, NOW(), t1.weight, 1, 1, NOW(), NOW() FROM `import_products` AS t1
	LEFT JOIN `manufacturer` AS t2 ON t1.manufacturer_name=t2.name WHERE t1.is_imported=0;

Our new products are created with the help of a lookup to the manufacturer table to find the manufacturer ID (if the product has a valid manufacturer); otherwise they are given the manufacturer_id 0. We must then enter the name and description for our products into the product_description table, and link the products to the store and their categories:

INSERT INTO `product_description` (product_id, language_id, name, description, meta_description, meta_keyword)
	SELECT t2.product_id, 1, t1.name, t1.description, '', '' FROM `import_products` AS t1
	JOIN `product` AS t2 ON t1.sku=t2.sku WHERE t1.is_imported=0;
INSERT IGNORE INTO `product_to_store` (product_id, store_id) SELECT product_id, 0 FROM `product`;
INSERT INTO `product_to_category` (product_id, category_id) SELECT t2.product_id, t3.category_id FROM `import_products` AS t1
	JOIN `product` AS t2
	JOIN `category_description` AS t3 ON t1.sku=t2.sku AND t1.category_name=t3.name WHERE t1.is_imported=0;

The product_to_category table uses the product_id value we get from the imported products in the product table, and we obtain the category_id by matching the products category names against the corresponding name in the category_description table.

Import complete!

This concludes the first part of importing products to OpenCart from a CSV file.  The code will allow you to import manufacturers, categories, and products to your store.  The full MySQL code is as follows (it doesn't look like so much now, does it?):

DELETE FROM `import_products`;
DELETE FROM `import_manufacturers`;
DELETE FROM `import_categories`;
ALTER TABLE `product` ADD INDEX `sku` (`sku`);
ALTER TABLE `import_products` ADD INDEX `sku` (`sku`);
LOAD DATA INFILE 'product_list.csv' INTO TABLE `import_products`
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (sku, price, stock, @dummy, name, description, category_name, manufacturer_name, weight);
INSERT INTO `import_manufacturers` (name) SELECT DISTINCT(manufacturer_name) FROM `import_products`;
UPDATE `import_manufacturers` AS t1 JOIN `manufacturer` AS t2 ON t1.name=t2.name SET t1.is_imported=1;
INSERT INTO `manufacturer` (name, image, sort_order) SELECT name, '', 0 FROM `import_manufacturers` WHERE is_imported=0;
INSERT IGNORE INTO `manufacturer_to_store` (manufacturer_id, store_id) SELECT manufacturer_id, 0 FROM `manufacturer`;
ALTER TABLE `category` ADD COLUMN import_id BIGINT UNSIGNED DEFAULT 0 NOT NULL;
INSERT INTO `import_categories` (name) SELECT DISTINCT(category_name) FROM `import_products`;
UPDATE `import_categories` AS t1 JOIN `category_description` AS t2 ON t1.name=t2.name SET t1.is_imported=1;
INSERT INTO `category` (image, top, `column`, sort_order, `status`, date_added, date_modified, import_id)
	SELECT '', 1, 1, 1, 1, NOW(), NOW(), id FROM `import_categories` WHERE is_imported=0;
INSERT INTO `category_description` (category_id, language_id, name, description, meta_description, meta_keyword)
	SELECT t1.category_id, 1, t2.name, '', '', '' FROM `category` AS t1
	JOIN `import_categories` AS t2 ON t1.import_id=t2.id WHERE t2.is_imported=0;
INSERT IGNORE INTO `category_to_store` (category_id, store_id) SELECT category_id, 0 FROM `category`;
ALTER TABLE `category` DROP COLUMN import_id;UPDATE `import_products` AS t1 JOIN `product` AS t2 ON t1.sku=t2.sku SET t1.is_imported=1;
INSERT INTO `product` (sku, quantity, stock_status_id, manufacturer_id, shipping, price, tax_class_id, date_available, weight, weight_class_id, status, date_added, date_modified)
	SELECT t1.sku, t1.stock, 7, IF(ISNULL(t2.manufacturer_id), 0, t2.manufacturer_id), 1, t1.price, 9, NOW(), t1.weight, 1, 1, NOW(), NOW() FROM `import_products` AS t1
	LEFT JOIN `manufacturer` AS t2 ON t1.manufacturer_name=t2.name WHERE t1.is_imported=0;
INSERT INTO `product_description` (product_id, language_id, name, description, meta_description, meta_keyword)
	SELECT t2.product_id, 1, t1.name, t1.description, '', '' FROM `import_products` AS t1 JOIN `product` AS t2 ON t1.sku=t2.sku WHERE t1.is_imported=0;
INSERT IGNORE INTO `product_to_store` (product_id, store_id)
	SELECT product_id, 0 FROM `product`;
INSERT INTO `product_to_category` (product_id, category_id)
	SELECT t2.product_id, t3.category_id FROM `import_products` AS t1
	JOIN `product` AS t2 JOIN `category_description` AS t3 ON t1.sku=t2.sku AND t1.category_name=t3.name WHERE t1.is_imported=0;