-
-
Save liutauras/b75d2467897c43ab7791913446c308e4 to your computer and use it in GitHub Desktop.
OpenCart to Shopify - export Products in Shopify CSV format
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| Handle | |
| Title = oc_product_description.name | |
| Body (HTML) = oc_product_description.description | |
| Vendor = oc_product.manufacturer_id | |
| Type | |
| Tags = oc_product_description.tag | |
| Published = oc_product.status | |
| Option1 Name | |
| Option1 Value | |
| Option2 Name | |
| Option2 Value | |
| Option3 Name | |
| Option3 Value | |
| Variant SKU = oc_product.model -- because not all products have SKUs but all products have Models because model was required | |
| Variant Grams = oc_product.weight | |
| Variant Inventory Tracker = shopify | |
| Variant Inventory Qty = oc_product.quantity | |
| Variant Inventory Policy | |
| Variant Fulfillment Service = manual | |
| Variant Price = oc_product.price | |
| Variant Compare At Price | |
| Variant Requires Shipping = TRUE | |
| Variant Taxable = TRUE = oc_product.tax_class_id=9 | |
| Variant Barcode | |
| Image Src = oc_product.image | |
| Image Position | |
| Image Alt Text = oc_product_description.name | |
| Gift Card | |
| Google Shopping / MPN | |
| Google Shopping / Age Group | |
| Google Shopping / Gender | |
| Google Shopping / Google Product Category | |
| SEO Title = oc_product_description.meta_title | |
| SEO Description = oc_product_description.meta_description | |
| Google Shopping / AdWords Grouping | |
| Google Shopping / AdWords Labels | |
| Google Shopping / Condition | |
| Google Shopping / Custom Product | |
| Google Shopping / Custom Label 0 | |
| Google Shopping / Custom Label 1 | |
| Google Shopping / Custom Label 2 | |
| Google Shopping / Custom Label 3 | |
| Google Shopping / Custom Label 4 | |
| Variant Image = oc_product.image | |
| Variant Weight Unit = BLANK -- leave this empty cz only Grams can be imported via CSV. "Shopify will always import and export weight in grams, even if you specify a different unit." | |
| Variant Tax Code | |
| */ | |
| -- This assumes you have no Product options/varients | |
| SELECT | |
| -- handle is not required, it'll be automatically generated from the Title | |
| REPLACE(oc_product_description.name, '"', '"') AS 'Title', -- convert escaped HTML back to HTML | |
| REPLACE(REPLACE(REPLACE(REPLACE(oc_product_description.description, '<', '<'), '>', '>'), '&', '&'), '"', '"') AS 'Body (HTML)', -- convert escaped HTML back to HTML | |
| oc_manufacturer.name AS 'Vendor', | |
| oc_product_description.tag AS 'Tags', | |
| -- Published | |
| CASE oc_product.status -- more on CASE: http://www.4guysfromrolla.com/webtech/102704-1.shtml | |
| WHEN 1 THEN 'TRUE' | |
| WHEN 0 THEN 'FALSE' | |
| END AS 'Published', | |
| oc_product.model AS 'Variant SKU', | |
| oc_product.weight AS 'Variant Grams', | |
| -- Variant Inventory Tracker: shopify | |
| CASE oc_product.shipping | |
| WHEN 1 THEN 'shopify' -- default: Shopify tracks product inventory | |
| END AS 'Variant Inventory Tracker', | |
| oc_product.quantity AS 'Variant Inventory Qty', | |
| oc_product.price AS 'Variant Price', | |
| -- Shipping | |
| CASE oc_product.shipping | |
| WHEN 0 THEN 'FALSE' | |
| WHEN 1 THEN 'TRUE' | |
| END AS 'Variant Requires Shipping', | |
| -- Tax | |
| CASE oc_product.tax_class_id | |
| WHEN 9 THEN 'TRUE' -- 9 means Taxable Goods | |
| WHEN 10 THEN 'FALSE' -- 10 means Downloadable Products | |
| WHEN 'NULL' THEN 'TRUE' -- Default to TRUE if NULL | |
| END AS 'Variant Taxable', | |
| -- Image | |
| CONCAT('https://cakebox.me/image/', oc_product.image) AS 'Image Src', | |
| oc_product_description.name AS 'Image Alt Text' | |
| /* | |
| SEO | |
| -- oc_product_description.meta_title AS 'SEO Title', -- leave empty, will copy Product Title | |
| -- oc_product_description.meta_description AS 'SEO Description', -- leave empty, will copy Product Description | |
| Since the SEO we have right now on the OpenCart site sucks, there is no point in trasnferring it. | |
| On the current site, Meta Description just has a copy of Meta Title. It is better to leave it empty so that Shopify picks Meta Description | |
| from the Product Description instead of having a copy of Meta Title | |
| */ | |
| FROM oc_product | |
| -- Get Title, Body (HTML) from `oc_product_description` | |
| INNER JOIN oc_product_description | |
| ON oc_product.product_id=oc_product_description.product_id | |
| -- Get Vendor `from oc_manufacturer` | |
| LEFT JOIN oc_manufacturer -- LEFT JOIN because INNER only gets common rows i.e. the total rows would be equal to total amount of manufacturers | |
| ON oc_product.manufacturer_id=oc_manufacturer.manufacturer_id | |
| WHERE oc_product.image IS NOT NULL; -- this was needed because a POS extension had added LOADS of empty products |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment