Exporting in a format that's fit for importing products

output.csv

product_export.sql

-- copy (
SELECT
  e.name as producer,
  v.sku,
  p.name,
  v.display_name,
  t.name as category,
  p.description,
  v.unit_value as units,
  case
    when p.variant_unit = 'weight' and p.variant_unit_scale = 1 then 'g'
    when p.variant_unit = 'weight' and p.variant_unit_scale = 1000 then 'kg'
    when p.variant_unit = 'weight' and p.variant_unit_scale = 1000000 then 'T'
    when p.variant_unit = 'weight' and p.variant_unit_scale = 28.35 then 'oz'
    when p.variant_unit = 'weight' and p.variant_unit_scale = 453.6 then 'lb'
    when p.variant_unit = 'volume' and p.variant_unit_scale = 0.001 then 'mL'
    when p.variant_unit = 'volume' and p.variant_unit_scale = 1 then 'L'
    when p.variant_unit = 'volume' and p.variant_unit_scale = 1000 then 'kL'
    else 'items'
  end as unit_type, 
  p.variant_unit_name,
  pr.amount as price,
  s.count_on_hand as on_hand,
  p.available_on,
  CASE
    WHEN s.backorderable=TRUE THEN 1
    ELSE 0
  END AS on_demand,
  sc.name as shipping_category,
  tc.name as tax_category
from spree_variants as v
left join spree_products as p on v.product_id = p.id
left join enterprises as e on p.supplier_id=e.id
left join spree_taxons as t on p.primary_taxon_id= t.id
left join spree_stock_items as s on v.id = s.variant_id
left join spree_prices as pr on v.id = pr.variant_id
left join spree_shipping_categories as sc on p.shipping_category_id = sc.id
left join spree_tax_categories as tc on p.tax_category_id = tc.id
where p.supplier_id in (4,2,3)
  and v.is_master = false
  and v.deleted_at is null
-- ) to '/tmp/output.csv' with CSV HEADER;