Walkthroughs

All examples assume Datashift has been installed and rake/thor commands initialised, and we are in the root directory of a Rails project

Writing to Excel

Code snippet to demonstrate quickly formatting and writing an Excel spreadsheet directly from within Ruby code, perhaps in a thor task.

Add to bundle

gem 'datashift'

Ruby makes it easy to reopen an existing class and add some formatting methods, here we want to dump out some associated data in a nice way.

Later we will configure datashift to use these methods for writing to certain columns

So first locally extend the main Product class, with some reporting methods to make data readable.

Product.class_eval do

 def bundle_name
     bundle.try(:name)
 end

 def taxons
     taxons.collect(&:title).compact.join("\n")
 end

 def product_parameters
     product_parameters_databases.collect(&:status).compact.join("\n")
 end

end

Now we define a small configuration block, keyed on class we are exporting, the nodes defines the Rails attributes or methods to call to populate the column.

You can use presentation field to set a custom header, different to the field/method name.

yaml= <<EOS
data_flow_schema:
 Product:
   nodes:
     - id:
     - status:
        presentation: "Product Status"
    - bundle_name:
        presentation: "Bundle"
     - available:
        presentation: "Available Y/N"
     - volume:
     - taxons:
     - product_parameters
     - name:
     - description: 
     - sku
EOS

we can configure the exporter, and run the export to a file

products = Products.not_available

data_flow_schema = DataShift::DataFlowSchema.new.tap { |dfs| dfs.prepare_from_string(yaml) }
 
DataShift::ExcelExporter.new.tap do |d|
  d.data_flow_schema = data_flow_schema
  d.export(File.join('tmp', 'products_for_removal.xls'), products)
  puts "Report of Products found written to #{File.join('tmp', 'products_for_removal.xls')}"
end

Example - Spree Import from Suppliers data sheets

We will now walk through a real example where we import a suppliers product list into Spree.

A Spree product has a number of associations, such as Properties, Taxons and Variants. To fully describe a product, we will need to capture these during the Import, which for this walk through will be a single manufacturer's Lighting products. These will need to be assigned to Taxons, such as Internal Lighting, External Lighting, and be assigned to one or more of a set of common Properties such as Wattage, etc.

First we'll generate a full template, with all associations, for Spree::Product, so we know exactly what data we can set during the upload, we'll store the templates and seed import spreadsheets under db/datashift but the location not important.

bundle exec thor help datashift:generate:excel
bundle exec thor datashift:generate:excel --model Spree::Product --result  db/datashift/templates/product.xls --assoc

We can also generate a sample of just the Product fields, to get a feel for the minimum information required to get a Product into Spree

bundle exec thor datashift:generate:excel --model Spree::Product --result  db/datashift/templates/product_raw.xls

The first association we'll deal with are Properties, looking at the full template we can see 2 related fields, product_properties and properties. We can generate a template for both, so we can have a look at what fields Spree expects.

thor datashift:generate:excel --model Spree::Property --result  db/datashift/templates/property.xls
thor datashift:generate:excel --model Spree::ProductProperty --result  db/datashift/templates/product_property.xls

If we open up property.xls in Excel/Open Office we can see that a Spree property contains these fields

id name presentation created_at updated_at

And a Spree product_property can contain these fields

id value product_id property_id created_at updated_at

Now we know that each Property requires a name, and allows us to specify a snippet of text or even an image to be presented to visitors, when they view a Product assigned that property. For the import we won't need to specify id, created_at or updated_at so we can delete those columns.

When it's assigned to a Product, we can assign a product specific value, so for example we can assign an actual value per Product for wattage.

Form the Suppliers spreadsheet we can extract a number of Properties for our Products, defining a unique name and what text we want our customers to see, for example,

> name	                presentation
> supplier_order_code	Order Code
> cat_ref	        Catalogue Ref.
> wattage	        Wattage
> cap	                CAP
> avg_rated_life_hours	AVERAGE RATED LIFE (hours)
> dimension_cm	        DIMENSION  (CM)
> dimension_mm	        DIMENSION  (MM)
> pack_size	        PACK/BOX QTY
> idc	                IDC
> catalogue_page	Catalogue Page

We upload this to our Spree development database :

bundle exec thor datashift:import:excel --model=Spree::Property --input=db/datashift/templates/property.xls

Datashift writes to the active record logger, so we can check the development.log and see that 10 rwos were added, which we can confirm by visiting

http://localhost:3000/admin/properties

Now our properties are in we can edit our suppliers Product spreadsheet, changing the headings to enable datashift to add the right property to the product_properties for each Product.

The datashift Spree product loader has a specific over ride for product_properties that enable us to choose property and set the product speciifc value all in one go (within single cell) using the syntax

property name:value

So for example to assign the wattage property to a product with a value of '8W' specify wattage:8W

Now we can load our whole set of Products

bundle exec thor datashift:spree:products -i db/datashift/MegamanFozz20111115_load.xls