How to Convert Shopify Product CSV Exports into Clean SQL Statements

#shopify #sql #databasemigration #datacleaning

Moving catalog data out of Shopify and into an internal relational database sounds like a simple task. You export your product catalog from the Shopify admin, download the CSV file, and load it into a script to generate SQL statements. Within minutes, however, your database client throws syntax errors, transactions abort, and your staging tables end up filled with corrupted HTML and orphan variants.

The reason is straightforward: Shopify product exports are not normalized relational tables. They are denormalized spreadsheets designed around a custom multi-row hierarchy. When you feed that structure directly into generic SQL conversion tools or raw string concatenation scripts, the process breaks.

The Traps Inside Shopify Product Export CSVs

Before you can convert Shopify exports into clean INSERT statements, you need to understand why standard parsers fail:

Sparse Variant Rows: A product with five variants spans five rows. The first row contains the handle, title, description HTML, vendor, and tags. The subsequent four rows leave the title, body HTML, and vendor blank, populated only with variant SKUs, prices, and options.
Unescaped Single Quotes in Rich Text: The Body (HTML) column contains real customer-facing copy. Words like men's, don't, or raw JavaScript/CSS attributes contain unescaped single quotes that instantly terminate SQL string literals.
Ambiguous NULLs vs. Blank Strings: Empty spreadsheet cells often get exported as empty strings (''). If your target database column is a numeric type (like inventory_quantity or price), inserting '' results in an invalid type cast error instead of inserting NULL.
Array and Comma Formatting: The Tags column exports as a comma-separated string wrapped in quotes. Basic line-by-line converters fail to properly normalize tags into arrays or relational join tables.

Anatomy of a Broken SQL INSERT Statement

Consider what happens when you use basic string interpolation on a Shopify export row. If the description contains single quotes or the variant row is missing parent attributes, you end up with SQL syntax errors:

-- FAILED: Unescaped single quote breaks the query string
INSERT INTO staging_shopify_products (
    handle, title, body_html, variant_sku, price
) VALUES (
    'classic-leather-jacket',
    'Men's Classic Leather Jacket',
    '<p>It's crafted from 100% full-grain leather.</p>',
    'JKT-BLK-M',
    149.99
);

-- FAILED: Blank string passed into an integer column on a variant row
INSERT INTO staging_shopify_products (
    handle, title, body_html, variant_sku, price, inventory_qty
) VALUES (
    'classic-leather-jacket',
    '',
    '',
    'JKT-BLK-L',
    149.99,
    '' -- Error: invalid input syntax for integer: ""
);

In PostgreSQL, MySQL, and SQLite, escaping rules require doubling the single quote ('') or escaping with backslashes. Single quotes inside string literals must be handled before query execution.

The Data Transformation Pipeline

A robust migration separates this process into two logical stages: data normalization and SQL generation.

1. Normalizing Hierarchical Rows in Python

Because parent metadata (title, body, vendor) only lives in row 1 of a variant group, use forward-filling to propagate parent attributes across all variant rows:

import pandas as pd
import numpy as np

df = pd.read_csv('shopify_export.csv')

# Forward fill parent metadata based on product handle
fields_to_fill = ['Title', 'Body (HTML)', 'Vendor', 'Type', 'Tags']
df[fields_to_fill] = df.groupby('Handle')[fields_to_fill].ffill()

# Standardize empty values as explicit None/NULL
df = df.replace({np.nan: None, '': None})

2. Generating Safe SQL Literals

Ensure that string fields escape quotes and numeric types handle nullability:

def format_sql_value(value, is_numeric=False):
    if value is None:
        return 'NULL'
    if is_numeric:
        return str(value)
    # Escape single quotes by doubling them
    clean_str = str(value).replace("'", "''")
    return f"'{clean_str}'"

Architecting the SQL Target: Staging Table vs Relational Model

When loading Shopify data into a production database, load the clean statements into an intermediate staging table first. From there, run set-based SQL queries to populate your normalized schema.

-- Step 1: Create a staging table matching your cleaned export schema
CREATE TABLE staging_shopify_import (
    handle VARCHAR(255),
    title VARCHAR(500),
    body_html TEXT,
    vendor VARCHAR(255),
    variant_sku VARCHAR(100),
    variant_inventory_qty INT,
    variant_price DECIMAL(10, 2)
);

-- Step 2: Batch INSERT into staging
INSERT INTO staging_shopify_import (
    handle, title, body_html, vendor, variant_sku, variant_inventory_qty, variant_price
) VALUES 
(
    'classic-leather-jacket',
    'Men''s Classic Leather Jacket',
    '<p>It''s crafted from 100% full-grain leather.</p>',
    'Heritage Goods',
    'JKT-BLK-M',
    14,
    149.99
),
(
    'classic-leather-jacket',
    'Men''s Classic Leather Jacket',
    '<p>It''s crafted from 100% full-grain leather.</p>',
    'Heritage Goods',
    'JKT-BLK-L',
    8,
    149.99
);

-- Step 3: Populate relational products and variants from staging
INSERT INTO products (handle, title, description, vendor)
SELECT DISTINCT handle, title, body_html, vendor
FROM staging_shopify_import;

INSERT INTO product_variants (product_id, sku, price, stock)
SELECT p.id, s.variant_sku, s.variant_price, s.variant_inventory_qty
FROM staging_shopify_import s
JOIN products p ON p.handle = s.handle;

Key Migration Checklist

Deduplicate SKUs: Shopify permits duplicate SKUs under specific conditions, but relational databases enforce unique constraints. Check for duplicate SKUs prior to running final insertions.
Standardize Empty Values: Ensure empty compare-at prices or weight fields are written as NULL instead of empty strings or zeros.
Preserve UTF-8 Encoding: Ensure your extraction pipeline preserves UTF-8 to prevent corrupting currency symbols or international accents.
Chunk Batch Inserts: Group statements into chunks of 500 to 1,000 rows per query block to stay within engine payload boundaries.

DEV Community