Bulk Import of Product / Inventory

Continuing the discussion from Bulk import of product:


There is a lot of discussion on this thread. I have now started the process of capturing our current collective thinking in a document… boldly titled Data Import SRS (software requirements specification).

In progress:

  • Assumptions
  • Requirements
  • Use case

Still to do:

  • User Interface Mocks

@MyriamBoure @oeoeaio @Kirsten @NickWeir @CynthiaReynolds @sreeharsha @Oliver @sstead @tschumilas @danielle
Please take a look, comment and suggest changes! Would be great to continue getting your input this specification.

Use cases:

  • Create: as an enterprise, I have all my existing products in a spreadsheet. It would be much easier to get started by formatting and uploading my spreadsheet than creating each individually
    • assume i am happy to come back and add images later
    • create relevant ‘properties’ first
    • assume the producer profiles are created, with E2Es ready
  • Update Prices: As a wholesaler, I have many products and the prices change regularly. It is much easier to make these changes in a spreadsheet and import them all at once
    • may include products from multiple producers
    • my price changes may need to be picked up by others
      [NB: this may not be the actual wholesaler but another user managing on their behalf]
  • Update Inventory: As an Enterprise, the availability of my products changes week to week, I want to be able to upload CSV and have p/v that aren’t included removed from my inventory
    • my changes to product availability may need to be shared with other enterprises
  • Potential Buying Group integration (SS) As a buying group, I can receive an availability report from my supplying hub, make some modifications (to pack sizes, price etc), and then upload this into my inventory.
    • This feature may not be needed if a more ‘built in’ buying group solution is provided.
    • This is analogous to the current LettuceShare process used by buying groups
    • As a starting point, these products may not be linked to the actual producer profiles, instead allocated as dummy products to the buying group.

Issues / Questions:

  • are we uploading into p/v values; those in inventory lists or both? depends on separation of products / inventory
  • if we separate p/v and inventory, then the initial ‘profile / product info’ and later ‘inventory info’ may need to be imported separately?
  • working mostly with inventory lists would mean that p/v not included in the csv could simply be set to hidden (no need to delete or change values)
  • will need processes for dealing with
    • invalid data
    • non-existent producers

@sstead @oeoeaio have I missed use cases? have made a wiki page so you can just add them if i have

@Kirsten @lin_d_hop

Lynne, thanks for inviting me to comment here.

I could see us use this feature in two different ways.

  1. updates weekly prices from local food and veg wholesaler - bulk edit works for this too and not a major problem to do it this way
  2. import wholefoods wholesaler catalogue - this is something we wouldn’t attempt without a bulk upload and also, you may need to check if the system would cope with it. The full list contains 5000 line items. No problem to restrict this to a much lower number. I’m not even sure it would work, because of certain minimum order rules, so you wouldn’t want to develop this just for us.

I think either way it would be good to be able to download the current list of produce from a certain producer/supplier first. I would imagine in some cases it will be easier to update that with new prices, because everything will already be in the format required to re-import, including any variables and product properties. In other cases like 2) above there would be no choice but to format the obtained price list from the supplier’s format to the OFN format every time.

1 Like

i am working with several potential new UK users who would benefit from this feature to get started. Kirsten’s spec above would cover what I have seen so far in terms of user needs.

Thank you @Kirsten for those spec, I think:

  • if you import a product list in the “product” page, it means modifying the product catalog
  • if you import a product list in the “inventory” page it means modifying the product inventory list, but then all the products need to exist already in the product pages (so you should get a report with the products that couldn’t be added to your inventory as they didn’t exist in the product catalog, so then you can check them first or have the option to add them directly from the report). The same kind of report could happen if a producer doesn’t exist.
    I think it’s ok to treat them seperatly, as a hub manager can just replace the price / quantity of the excel file from the producer/wholesaler before importing it to inventory…

Just a question, is this feature already in the backlog for development or does it need to be funded / find a developer to do it? We have a buying group in France who really need that feature (to populate the producer catalog), they are looking some Ruby dev in their network who would like to get involved in the project (as they have no funds to contribute with), but I would like to be able to tell them if that feature is already in the pipeline or not.

This feature is not yet in the pipeline to my knowledge.

It is something the UK want and it high on our priority list. We will be looking to contribute to the estimates and development of this feature.

A challenge here is that the main utility in data import would be to take supplier files as is, rather than requiring that supplier fit our formats.

Mailchimp have functionality similar to this. They allow a user to import a spreadsheet, then allow the user to define the mapping of fields from within their interface. For our purposes, we would want to be able to save the details of how a suppliers’ spreadsheet maps to our tables so that future spreadsheets from this supplier could be imported quickly and easily.

Perhaps this functionality might be implemented in phases such like…:
Phase 1: Use Case ‘Create’ (specified above) to allow new products to be created from a specified file format.
Phase 2: Use case ‘Update’ to enable price, availability and inventory to be updated from specific file format
Phase 3: Improved interface allowing supplier spreadsheets to be mapped and mapping saved for reuse.
Phase 4: Enable ‘Create’ with files in different formats, including producer-product mappings such that we can import databases from existing hub enterprises easily.

@Kirsten @MyriamBoure @danielle @oeoeaio I know these phases need to be fleshed out a little more but how does that sound as the beginnings of a plan? If so I’d be happy to flesh out the spec of Phase 1 in more detail so we could look at an estimate of an estimate?

I don’t fully understand the details in this @lin_d_hop but I just want to cast a ‘vote’ to say that in Canada this feature is high on our list. We have no funding now, but are writing proposals and doing other fundraising - and if any of that pans out, we would contribute to this.

I like Lynne’s suggestion. Start with something where the user has to conform to OFN format and later a field mapping feature would be great.

@lin_d_hop could that be of any help? https://github.com/joshmcarthur/spree-import-products. @Othmar shared that with us in France.

I like your plan Lynne and would appreciate you speccing phase 1 when @danielle confirms that the Aus team are OK with your plan. thanks all - this will be great

Hi all, I think @Kirsten will be in touch about bulk import, she’s had many many thoughts about how to do this over the years and is best placed to respond :slight_smile:

Fleshing out the details for phase 1:
Under an additional tab within Products -> Import Products
Compulsory fields:

  • Supplier (chosen from a drop down)
  • Product Name
  • Product Category (allow default)
  • Product Price
  • Stock level (allow default)
  • Tax Category (allow default)
    Optional Fields
  • Description
  • Units
  • Unit value
  • Display unit value as
  • Shipping Category
  • Image upload

How do we demand a user conforms to our product category?

  • We could give the option for an import to place all files that don’t match our categories to a specific category for the user to update later
    How do variants work?
  • A product being added from the specific file in which the name matches an existing product in the file is created under a variant of the product.
    Does this override existing products?
    In the case of removing items not in the input file, there could be a checkbox that asks if u want to wipe and reload all from this producer. Perhaps an option to also leave items missing from file alone or to set qty to 0.
    Option 1- delete all products from producer inventory and rewrite
    Option 2 - add and update
    a) if missing set qty to 0
    b) if missing do nothing.
    Is there a way to upload images?
    Phase one does not include image import. Images will be manually added later.
    How to handle default values?
    Before the upload default options can be selected for ‘stock level’, ‘category’ and ‘tax category’.
    What if some rows fail to import?
    The user will be able to see a list of failed imports. They will then have the opportunity to update the source file before continuing.
    Do we need to have the opportunity to check data before loading into the database?
    Eg should we allow the user to view the data on screen before updates to the database are made? I think the answer should be yes.

@Kirsten It would be great to hear your thoughts and flesh this out from your perspective. We have a crew of devs now and it would be great for us to get fleshing out bigger features as well. The team is fast getting through smaller bugs :slight_smile:

Looks great @lin_d_hop - I have a question about units. Would they have to be the same on the source spreadsheet and the shop in OFN? In Canada we are of course in metric units - but since some of our large suppliers (the very ones we would want to use a bulk import for) import product from the US, often their spreadsheets and large lists are in US metrics (lbs, ounces…). So - I’m assuming an OFN user would have to either change these on the source spreadsheet first, or we would leave those fields blank to be calculated and filled in after upload?

I think there would be many situations where the price and quantity details would have to be entered manually after uploading a list actually. For example - a wholesaler would have the price for a case (24 x 3 lb bags of apples for example) and the OFN user (for most hubs anyway) would enter the price for 1 3 lb bag. So what would happen here? (Maybe I’m missing something - I’m still new to this :slight_smile:)

My thoughts are:

Categories - Either what Lynne said or reject anything that doesn’t match.

Variants/existing products - I’d probably override and existing products and replace them with the new one. Would that be simpler? Then any variants could be added by checking the import file for product names that are the same but have different weights/units. If the name is identical, the second item will be a variant of the first. But if that’s complicated, maybe they can just be imported as separate products.

How can we check for existing products that are NOT in the import file. This may mean a product has been discontinued by the supplier or is simply not currently in season and needs to be taken off the OFN shop. The way I can see myself use bulk imports would necessitate an option to remove anything which isn’t on the import file.

In the case of removing items not in the input file, there could be a checkbox that asks if u want to wipe and reload all from this producer. Perhaps an option to also leave items missing from file alone or to set qty to 0.

Another set of cases to note is permissions and relationships to inventory vs product list.
Case 1- user is owner/manager of producer. User can choose to update product list, inventory or both.
Case 2- user is updating a product list for a producer they do not own/manage. User can only update their inventory.

hello everyone :slight_smile: here’s some thoughts from us, greatly informed by analysis from @sstead :slight_smile: and chats with @oeoeaio

NB. The top post in this thread is a wiki - I wonder if is worth editing so that the summary of where things are at sits at the top? @lin_d_hop

###Amending Products vs Amending Inventory
discussion in aus that soon we’ll need to shift the management of fluctuating product fields like price, on hand, on demand, SKU etc out of Products and into the Inventory interface. In this scenario, the products list will only store static information about products, including producer, name, category, properties, unit size?, description, image. see [here]

There’s been concern that building this product upload/update tool BEFORE making this shift, could mean that the functionality soon becomes redundant (when we move to the system of managing most fluctuating fields in Inventory). But we think we can get around this by:

  • building an update uploader that basically is/works the same on both product and inventory pages BUT
  • ignores / doesn’t write some fields depending on page (and potentially options presented to users) E.G.
  • if used on inventory page it doesn’t attempt to update info in producer, product, description etc
  • if used on product page now (e.g. either for update or create) it creates or overwrites stock, price etc . . but later could require user to have selected an inventory to do this to.

Then if information is moved later, is just a case of adjusting which fields are updated from which page

Main take home point here is that uploader should be able to use the same file from both product and inventory pages as the information is all held together, and then it just uses info appropriate to page/permissions (also as per @lin_d_hop’s cases above)

###Compulsory vs optional fields
Optional fields - Wouldn’t units/unit value be compulsory? And maybe add SKU and group buy and bulk unit size to optional fields.

###Removing / hiding / deleting products
My inclination on this is that you don’t want to delete products using an uploader without a lot of caution, because ‘deleting’ a product will have implications throughout the system, including for anyone managing it in their inventory. So I think that generally, if the ‘Update’ file is being used in:

  • Product page, the stock on-hand should be set to zero and the user notified that XX products will no longer appear in shops until stock is available
  • Inventory page, either/both stock set to zero and product hidden / removed from inventory (@oeoeaio @sstead perhaps stock already zerod when hidden? i don’t know)

Perhaps there is a special case for ‘delete’ products / variants in which you upload a file specifically to do this if someone is trying to clean things out, but doing so by omission in an uploader seems like a bad idea

The risk of assigning products to a generic category is that users may never correct this. Plus if they do correct it- this bug causes minor problems - G#1250. To help people assign their products to the correct category each instance could have a template excel sheet with the category fields in a dropdown. Makes it quick and easy to select the appropriate category for each line (see below)


from Sally

If uploader doesn’t handle images could we create a ‘fewer clicks’ way to add images- currently it’s a 3 click journey to get to the add image screen. Users using the uploader could have 100s of images.

I wonder if this could be handled relatively easily by allowing the user to put all the images in the same folder as the upload file and put the filename for each image in the csv next to the product. Then all they need to do is number them or use skus or whatever and the system can just zoop them in :wink:

If not being done, I wonder about default use of producer logo as product image, so that shop looks slightly more colourful with a lot less work to get started.


Wouldn’t it be better to ignore duplicates rather than create a copy? Could tell user ‘these items haven’t been created as they already exist’ in the upload status report

###Sally responses to @tschumilas issues

  • Theresa, yes the user would need to convert from ounces to grams in the sheet before upload. The good thing about excel is that this can be done relatively quickly and uploaded. Leaving fields blank isn’t really an option as the system should reject incomplete products.

  • Theresa, the second scenario you mention, breaking down a case of apples into 1lb bags is an example of a scenario where multiple inventory lists would be ideal. In the future, there could be a bulk invenotry lists and a retail list, the retail list will divide the bulk sizes/prices into a given fraction. This is future stuff, so yes manual for now, but at least sped up with excel, and an upload updater.

###Mapping supplier spreadsheet categories to OFN in importer
phase1 I think this is best handled by users maintaining a spreadsheet for each supplier where they can drop the suppliers list into sheet 1 and sheet 2 maps them to correct columns for ofn import. Then you save the 2nd sheet as csv and voila. You only need to map the categories once for each supplier when you set up the spreadsheet and then just drop the info in each time. So IMHO the built in category mapping can wait


How can the user denote variants? Same producer and same name could be a duplicate, not a variant? How will the system distinguish? One logic…

  • If producer name, product name, units, value are all same = duplicate
  • If producer name, product name are same BUT the units and/or value are different = variant?
  • But this won’t capture Jam - rasberry jam, strawberry jam, apricot jam as variants.
    Other option?

Thanks @Kirsten. Definitely planning to summarise everyones input into something more cohesive :slight_smile:

The more I delve into speccing this the more I realise that it makes much more sense to re-work products/inventory before progressing with import work.

We can talk about this more next UK dev call with @danielle, but it would be great to get @oeoeaio doing an estimate of an estimate (or maybe he’s thought about it enough that he can just move into estimate) of product/inventory overhaul once SO is out of the stables. UK team can definitely help with the implementation and could fund the work (figures dependent).

1 Like

ok, yes and . .

I’m not sure about that - the high level of demand for product upload and the likely tangly complexity of the product/inventory overhaul suggest to me that the best strategy is actually to get moving on the upload while maintaining this attention to possible future adaptation. If we wait to get the overhaul done before attending to the uploader I’m concerned it’ll be way too long.

Open to @oeoeaio, @sstead and @danielle disagreeing, but we’ve thought that we need a good day or two blocked out to even think through the ramifications of what we think might need to happen, let alone estimate it. I’m hoping that we might be able to find this time early next year, once standing orders is out there!

I think it is feasible to build the uploader and amend it later, and in the process of doing so will actually be informing the thinking around overhaul.