Import a customers list from WooCommerce to Shopify

When migrating from a WooCommerce site to Shopify, it can be useful to import the list of clients from the old site to be able to communicate with them or just for them to find their personal informations in the new site (email, address, etc.).

If it's possible to use one of many WooCommerce specific plugins to export the customers list, I often find them slow and not very clear with what kind of transformation they apply on the customers informations when serializing them to CSV.

Exporting WooCommerce customers from a database dump

The solution I most often resort to is to export the customers data straight from a recent database dump from the WordPress site. This allows me to export the whole dataset at once thus not wasting a PHP thread on the server and allowing me to experiment with different ways of serializing the data locally.

Here is the simplified approach:

1. Get a .sql export of the MySql database via a the sqldump command or from the admin interface of the website's hosting
2. Import the database in a local environment
3. Running some SQL commands against this database to export the data

Import WooCommerce data in Shoify using Node.js

Whenever I have to write a quick script using IO, writing files or interacting with databases, I tend to use Node.js. We could also write it in bash, Python or any other fitting language.

Here is the commented script I use to export customers data from the WordPress database imported in my local environment, transform this data them generate a CSV file ready to be imported via the Shopify admin interface.

const mysql = require('mysql2/promise');
const fs = require('fs');
const csv = require('fast-csv');

// TODO: Update these settings with your own config
const dbConfig = {
  host: 'localhost',
  user: 'database_user',
  password: 'database_password',
  database: 'database_name',
};

// TODO: Update these settings with your own config
const prefix = 'wp_';
const csvFilePath = 'shopify_customers.csv';

// Query to fetch WooCommerce customers
const query = `
  SELECT
    u.user_email AS email,
    um1.meta_value AS first_name,
    um2.meta_value AS last_name,
    um3.meta_value AS address1,
    um4.meta_value AS address2,
    um5.meta_value AS city,
    um6.meta_value AS province,
    um7.meta_value AS country,
    um8.meta_value AS zip,
    um9.meta_value AS phone
  FROM ${prefix}users u
  LEFT JOIN ${prefix}usermeta um1 ON u.ID = um1.user_id AND um1.meta_key = 'billing_first_name'
  LEFT JOIN ${prefix}usermeta um2 ON u.ID = um2.user_id AND um2.meta_key = 'billing_last_name'
  LEFT JOIN ${prefix}usermeta um3 ON u.ID = um3.user_id AND um3.meta_key = 'billing_address_1'
  LEFT JOIN ${prefix}usermeta um4 ON u.ID = um4.user_id AND um4.meta_key = 'billing_address_2'
  LEFT JOIN ${prefix}usermeta um5 ON u.ID = um5.user_id AND um5.meta_key = 'billing_city'
  LEFT JOIN ${prefix}usermeta um6 ON u.ID = um6.user_id AND um6.meta_key = 'billing_state'
  LEFT JOIN ${prefix}usermeta um7 ON u.ID = um7.user_id AND um7.meta_key = 'billing_country'
  LEFT JOIN ${prefix}usermeta um8 ON u.ID = um8.user_id AND um8.meta_key = 'billing_postcode'
  LEFT JOIN ${prefix}usermeta um9 ON u.ID = um9.user_id AND um9.meta_key = 'billing_phone'
  WHERE u.ID IN (
    SELECT user_id
    FROM ${prefix}usermeta
    WHERE meta_key = '${prefix}capabilities'
      AND meta_value LIKE '%customer%'
  );
`;

async function exportCustomersToCSV() {
  try {
    const connection = await mysql.createConnection(dbConfig);
    const [rows] = await connection.execute(query);
    await connection.end();

    const headers = [
      'First Name',
      'Last Name',
      'Email',
      'Accepts Email Marketing',
      'Default Address Company',
      'Default Address Address1',
      'Default Address Address2',
      'Default Address City',
      'Default Address Province Code',
      'Default Address Country Code',
      'Default Address Zip',
      'Default Address Phone',
      'Phone',
      'Accepts SMS Marketing',
      'Tags',
      'Note',
      'Tax Exempt',
    ];

    // Map the data to match Shopify's expected format
    const formattedRows = rows.map((row) => ({
      'First Name': row.first_name,
      'Last Name': row.last_name,
      Email: row.email,
      'Accepts Email Marketing': 'no', // Default to 'no', customize if necessary
      'Default Address Company': '', // Assuming no company data, can be modified if needed
      'Default Address Address1': row.address1,
      'Default Address Address2': row.address2,
      'Default Address City': row.city,
      'Default Address Province Code': row.province, // Assuming the state code is already in the correct format
      'Default Address Country Code': row.country, // Assuming the country code is already in the correct format
      'Default Address Zip': row.zip,
      'Default Address Phone': row.phone,
      Phone: row.phone,
      'Accepts SMS Marketing': 'no', // Default to 'no', customize if necessary
      Tags: '', // Customize if necessary
      Note: '', // Customize if necessary
      'Tax Exempt': 'no', // Default to 'no', customize if necessary
    }));

    // Create a writable stream for the CSV file
    const ws = fs.createWriteStream(csvFilePath);

    // Write the CSV file
    csv.write(formattedRows, { headers }).pipe(ws);

    console.log(`Customers have been exported to ${csvFilePath}`);
  } catch (error) {
    console.error('Error exporting customers:', error);
  }
}

// Run the function
exportCustomersToCSV();

Once ran, it generates a shopify_customers.csv which contains every customers with their personal informations. All left to do is to import it on Shopify via the admin interface.