Post Featured Image

Add Items to the Cart with CSV Files

This blog post is relevant for SuiteCommerce sites, and SuiteCommerce Advanced sites running Aconcagua or newer. While we use the extensibility API a few times in the code, an experienced developer could replicate the functionality without using it.

There was an interesting question I got asked a little while ago: is it possible to add items to the cart using a CSV file?

I said yes, and there was some interesting discussion about the best way to do this. In short, it's relatively simple using modern HTML APIs, a CSV parsing JavaScript library, and the extensibility API.

Adding a mechanism to your site so that customers can add items to the cart using a prepared CSV file may be preferable for some customers who are keen to shortcut the the ordering process. In this scenario, I am thinking in particular of B2B sites whose customers don't care for a browsing/shopping based experience, and just want to get down to the business of ordering their items. For them, producing a simple spreadsheet (or, more specifically, a text file of comma-separated values) of their desired items is what they want to do.

In order to enable this feature, I am going to make use of a number of interesting technologies available to use:

  • The file input type to allow a user to select a file to use
  • The file web API to give our JavaScript access to a user-selected file
  • PapaParse, an open source JavaScript library for parsing CSV files
  • The cart component of the extensibility API to add new views and add items to the cart

Let's take a look at these functionalities as part of a tutorial.

Basic Ideas

So, generally speaking, what's going to happen?

  1. The user visits the shopping cart page, which has the quick add functionality on it
  2. They click a button and select a file from their computer
  3. We validate the file and then process it
  4. After processing it, we build up an item data object that our application can understand
  5. Once the mapping is complete, we pass it to the extensibility API, which has a method to add items to the cart using a data object

Assuming everything goes well, the cart will update and the page will re-render to show the new items in the cart, like this:

Just a note before we begin: this blog post is about demonstrating the functionality of SuiteCommerce as well as giving you ideas about how to achieve certain things. While the functionality I've created to do this works reasonably well, it has not been through QA nor approved as an official NetSuite extension.

Finally, because of the technology we are using, this functionality is only compatible with newer browsers — in other words, if you still have users using Internet Explorer 9 or older, this won't work for them.

Basic Setup

if you want to follow along, you'll need to create a new extension using the wizard. Otherwise, you can just jump to the end and download my files and go from there. It's up to you.

Set up a new extension using your extension developer tools. When prompted, set the values as follows:

  • Extension Fantasy Name — Quick Add CSV
  • Extension Name — QuickAddCSV
  • Vendor Name — SteveGoldberg
  • Version Number — 1.0.0
  • Description — Processes CSV files to quickly add items to the cart
  • Supports — SuiteCommerce Online
  • Module Name — QuickAddCSV
  • Applies To — Shopping
  • Using — (none)

Yes, I'm setting the Using to none (or rather, I am deselecting them all) because this will speed up development for us. It will mean no dummy files are generated, which, as seasoned commerce developers, we don't need.

I'm also not going to go through the process of slowly building up the module as I think we have a lot to talk about, and you can follow one of my other tutorials for doing this (like my blog on adding a new field to the registration form). Instead, I'll just give you the files and talk through how we achieved certain parts.

The Entry Point File

The JavaScript entry point file, SteveGoldberg.QuickAddCSV.QuickAddCSV.js, for the module is pretty simple:

define('SteveGoldberg.QuickAddCSV.QuickAddCSV'
, [
    'SteveGoldberg.QuickAddCSV.QuickAddCSV.View'
  ]
, function
  (
    QuickAddCSVView
  )
{
  'use strict';

  return {
    mountToApp: function mountToApp (container)
    {
      var Cart = container.getComponent('Cart');

      Cart.addChildView('QuickAddView', function ()
      {
        return new QuickAddCSVView
        ({
          container: container
        })
      })
    }
  }
});

This is uncontroversial: we're going to add a new view to the cart page, specifically the quick add section.

PapaParse

Before we get to the view, let's talk about how we're going to parse the users' CSV files.

I'm going to use a third-party library called PapaParse. Why? Because parsing CSV files is more than just splitting values by commas and we should take it seriously. There are a number of different libraries on the internet, but PapaParse is highly performant, covers edge cases, etc. Plus this is a tutorial, so, you know, choose your own if you like.

While the file is AMD-compatible, it suffers from an issue that I highlight in my blog post on adding a third-party library to an extension: it returns an anonymous module, which SCA's AMD libraries do not like. We need to, therefore, modify the file so that it is named.

If you download the JS file for PapaParse and put it in your JavaScript folder, you'll see the below the licence, there is some AMD statements that we would expect to see:

(function(root, factory)
{
    /* globals define */
    if (typeof define === 'function' && define.amd)
    {
        // AMD. Register as an anonymous module.
        define([], factory);
    }
    else if (typeof module === 'object' && typeof exports !== 'undefined')
    {
        // Node. Does not work with strict CommonJS, but
        // only CommonJS-like environments that support module.exports,
        // like Node.
        module.exports = factory();
    }
    else
    {
        // Browser globals (root is window)
        root.Papa = factory();
    }
    // in strict mode we cannot access arguments.callee, so we need a named reference to
    // stringify the factory method for the blob worker
    // eslint-disable-next-line func-name
}(this, function moduleFactory()

We need to replace this, with a define statement that explicitly names the file, so our AMD libraries understand. Change the above lines to this:

define('PapaParse', [], function ()

Yup, just one line. All we want is to register it and give it a name.

The View File

OK, this file is quite large and does all the work. As I said, I'm going to give you the whole file but the methods are ordered in the order they're called, so it's easy to talk about them.

Here's SteveGoldberg.QuickAddCSV.QuickAddCSV.View.js:

define('SteveGoldberg.QuickAddCSV.QuickAddCSV.View'
, [
    'Backbone'
  , 'underscore'
  , 'PapaParse'
  , 'stevegoldberg_quickaddcsv_quickaddcsv.tpl'
  ]
, function
  (
    Backbone
  , _
  , Papa // I'm setting this to 'Papa' rather than 'PapaParse' because that's what they call it in their docs
  , stevegoldberg_quickaddcsv_quickaddcsv_tpl
  )
{
  'use strict';

  return Backbone.View.extend({
    template: stevegoldberg_quickaddcsv_quickaddcsv_tpl

  , initialize: function initialize ()
    {
      this.application = this.options.container;
      this.Cart = this.application.getComponent('Cart');
    }

  , events:
    {
      'click [data-action="csvupload"]': 'parseCSV' // when the button is clicked, it will begin the process
    , 'click #csvinput': 'hideError' // if there are errors from previous attempts, clicking on the file input will clear them
    }

  , parseCSV: function parseCSV ()
    {
      var self = this; // once we are deep in callbacks, we will need this variable to access this file's particular context
      var fileInput = document.getElementById('csvinput'); // essentially how we access the file API for the selected file

      if (fileInput.files.length === 1 && fileInput.files[0].type === 'text/csv') // check that there is one (and only one) file and that its type is a CSV file
      {
        Papa.parse(fileInput.files[0], // call PapaParse
        {
          complete: function (parsedLines) // when it's done parsing, do this
          {
            if (parsedLines.data.length < 50) // for performance reasons, we don't recommend having more than 50 items in the cart at once, so this is a basic check
            {
              self.processLines(parsedLines); // after this we need to build a map of the data and then send it to the extensibility API
            }
            else
            {
              self.showError(_.translate('Please do not add more than 50 items to your cart'))
            }

            // you could also add a check for any errors parsing the file and then report those back to the user, but there
          }
        , header: true
        });
      }

      else if (fileInput.files.length === 0) // if they click submit without selecting a file
      {
        self.showError(_.translate('Please select a file to parse'));
      }

      else if (fileInput.files[0].type !== 'text/csv') // if they select a file that isn't a CSV
      {
        self.showError(_.translate('Please select a valid CSV file'));
      }
    }

  , processLines: function processLines (parsedLines)
    {
      // Once you have the data, you need to structure it in a way that the API will understand, this means mapping the parsed data from PapaParse into line objects that the API wants
      // Strictly speaking, you only need the internalid and quantity to add an item to cart, but if its a matrix option you also need to provide the correct matrix options too
      // If you're implementing this, then you'll need to look at how you're accepting data from your shoppers and then work out the correct map
      var results = _.map(parsedLines.data, function (line) // _.map is a useful method for transforming existing arrays into new ones with the structure, keys and values we want
      {
        var newline = {}; // each item is an object
        newline.item = {internalid: line.internalid};
        newline.quantity = parseInt(line.quantity, 10); // quantity is returned as a string from the parsed CSV but must be of integer type
        newline.options = []; // item options are an array of objects

        _.each(line, function (val, key)
        {
          if (key.substr(0,7) === 'custcol') // we're gonna loop over each one and see if the first seven characters starting 'custcol', which is what custom transaction item options start with
          {
            newline.options.push(
            {
              cartOptionId: key // eg custcol_gen_color
            , value: {internalid: val} // eg {internalid: 7}
            });
          }
        });

        return newline
      });

      this.addLines(results) // once we have the array in the format we want, we can send it to the extensibility API
    }

  , addLines: function addLines (lines)
    {
      this.Cart.addLines({lines: lines}) // calls the cart component's addLines() method using the array we just created – note that the method isn't just passed an array, it's passed an object with the lines as a value to a property called "lines"
    }
  })
})

There is a lot going on here, plus I have included copious notes for those who are interested. But let's go through this slowly.

Basic Setup

This a normal Backbone view file.

The first thing you might notice is that when we add PapaParse as a dependency, we name it Papa: this is a style choice because their code and documentation prefers this as the keyword. If we didn't create a named AMD module for them, the name returned would be have been Papa. Strictly speaking, within the confines of your files, you can name your dependencies however you want, but for consistency with their code and documentation, I would recommend keeping it as Papa.

We return a template, as standard, which we'll get to in a minute. We all have the initialize() method that sets up the class to work with the cart component.

Then we have the events.

When the user selects their file, we need to bind clicking the 'upload' button with the start of the processing of the file. We also are going to be using validation and error messages in our file, so we're doing what we're did in the aforementioned terms and conditions tutorial: binding clicking on the input field with clearing error messages (ie if an error has been returned, and they click on the input to address the issue, we can be reasonably sure they have seen the message and are going to rectify the mistake).

Access and Parse the Data

The idea is that the user selects the file and then we parse it. But, before we can parse the data, we need to access the data! I want to look at one of the core technologies we're going to be using: the file API.

The input type we're going to use in our template is the file input. That's the one where when you click on it, it pops up a file explorer for your computer, and then you select a file (it also lets you drag a file onto it). While you might normally have this input as part of form, it is not strictly necessary, as the file API lets us use JavaScript to access the file(s) the user has selected.

There aren't a lot of exciting bells and whistles in this API, but the crucial thing is that we can access the file the user selects without the user having to upload the file to our server. Yes, the access happens locally, entirely in the user's browser. This, by itself, is a massive boon to performance (everything is done on their device), security (they don't send anything over the internet), and any storage concerns (our servers never touch the file). We don't need the CSV file per se, we just need its data.

To access the file, you just need to do something like the following, and then access its contents by working over the values of the files array:

var fileInput = document.getElementById('csvinput');

You'll see this in action in the parseCSV() method, which we call when the user clicks the (incorrectly named!) Upload button. The first thing we do is check the length of the file array — for this tutorial, I am limiting it to one file (there is an optional parameter for the file input which allows multiple files, which we are omitting) — and make sure that there is one (and only one) file, and that it is a CSV file. One of the handy things about it is that it does recognize the file extension, so that can give us a little bit of an opportunity for validation (eg, if they try to upload an Excel spreadsheet or PDF).

Once we pass the validation and go down the happy path, we call PapaParse's parse() method. Now, there's plenty of configurable options for this method, and there's plenty of stuff you can do with this, but let me pluck out the two crucial things we've got here:

  1. A success callback that is passed an object of the parsed lines of data (along with any errors and meta information) so that we can decide what to do next (in our case, we're going to move on to the data mapping stage)
  2. An option to use header values, which means that row values in the CSV file will be created as objects, using the values of the top row as the keys

Anyway, once the parsing process finishes, we go to the success callback (complete). We do another validation check, here is a NetSuite-specific one: the number of lines. One of the current limitations for SuiteCommerce relates to the number of lines a shopper can/should have in their cart at once. While there is technically no hard limit, we have noticed performance problems at around 50 lines — so, for that reason, I have put this check in to prevent shoppers from trying to add large numbers of items at once. (To be clear, the suggested limit is not just adding to the cart at once, but being active in the cart in total too.) If you don't want to have the 50 item limit, that's fine, but I would certainly set one: we wouldn't want someone to try adding, say, 500 or 5000 items!

Then, to round out this method, we have a number of other validation checks. We check that the user has selected a file (ie length is 0) or if the type of file selected is not a CSV. In all these error cases, no parsing happens and, instead, we bubble up an error message using the error messaging systems we have built into the custom base Backbone view.

Process the Parsed Data (Create a Map)

If you were to log the parsed data to your developer console, you'd get an object with a number of properties. The most important one is the data property, which is where we have, well... our data.

However, you can't just send this to the extensibility API as-is and expect it to work. No, we need to reformat the data.

We're going to use the cart component's addLines() method like this:

Cart.addLines({lines: [...]})

But the lines array can't just be any array of data. It needs to formatted in a specific way. For that, we will therefore need to map the existing data array to a new array.

Before I talk about the code I wrote, it's worth reiterating something I put in the code comments: I can't tell you exactly how to do this. How you do this depends on your site's data and how you expect your customers to complete the CSV files. In other words, I am basing this off a very simple CSV file I wrote for my site; if you accept CSV files from your customers with different headers or data, you will need to do extra transformation steps to get the data into the right format.

So, what I can tell you are the minimums and how it all works on a basic level.

Each value in the lines array must be an object. This object must have, at least, an internal ID value (in the form of "item": {"internalid": "1234"}). You should also pass it a quantity but if no quantity is provided, it will default to 1.

Now, here's the interesting part: matrix child items. If you use these on your site, there are two ways you can pass data about which item combination you want to add:

  1. You can pass the internal ID of the child item
  2. You can pass the internal ID of the parent item and and an array of options (as objects) that define what specific child item should be added

I hope that's clear. In other words, you can't just add the parent item: you must in some way specify which child item you want added.

If that's confusing, allow me to show an example that I have for my site:

{
  "item": {"internalid": "8059"}
, "quantity": 2
, "options":
  [
    {
      "cartOptionId": "custcol_gen_color"
    , "value": {"internalid": "5"}
    }
  , {
      "cartOptionId": "custcol_gen_size"
    , "value": {"internalid": "5"}
    }
  ]
}

So, what does this mean? Well, it means that you probably want to get your customers to format their CSV files so they look like something this:

internalidquantitycustcol_gen_colorcustcol_gen_sizecustcol_notarealoption
8059255 
80441851
80631 4banana

Each row is a line item that they want added to the cart. After specifying the ID and quantity, they must also specify any relevant custom transaction item fields. Note that if a particular field is not relevant, it can be left blank. I have also put an obvious mistake on one of the lines, to test what happens when bad data is included in the CSV.

There is a question that I know some of you are thinking right now: this is all well and good, but how exactly do we expect customers to get the internal IDs and custom transaction field names and values for the products they want? And it is a good question. The answer is: that's up to you. I can only tell you what the API expects in terms of data and structure, it is up to you how that is provided to it. You could publish this information on product pages, in a (printed/digital) catalog or price list, or on the invoices you provide.

There are alternative, of course, you could accept the data in the CSV that customer expects, and then write some sort of service that performs a look-up in NetSuite, or perhaps has results built into it, so it's an extra step in the map. For example, if you use SKUs, then you might want to write some service that performs a search on those SKUs and returns the internal IDs.

For matrix options, the internal IDs of options always match the IDs of the values in the list record they depend upon. You could always fetch (or just store in your JS) a copy of those values and then look up the internal ID in them. For example, you could have you color list and then do a search for the customer's provided value (eg "red") and then return the internal ID for that.

Anyway: the mapping process itself. When it is passed parsed data, it starts to create the map using the _.map() method. Now, you don't technically need to use Underscore for this but the method we're using makes things a little bit easier: it takes an existing array of data, passes it into your prescribed iterator function, and then outputs a newly formed array. The iterator goes over each value in the array (ie each line) and transforms it (so, you could just write a loop instead).

If you take a look at the function, you can see that for each line we're creating a new object and then setting the properties to match the format of the example line object I showed you above. Perhaps the only really interesting thing about this is how we determine whether a field is a custom transaction item option: we do a check on the key strings and see if the first 7 characters are "custcol", which is the reserved namespace for those custom option fields.

When it reaches the end of the iterator function, we return the new line (entry) for the array, and the process repeats until every value in the original array has been processed; then map() returns the new, fully transformed array.

Add the Lines to the Cart

As our array is now fully formed, all that's really left to do is add it to the cart. There isn't really much here to say or do! The extensibility API makes this process super easy to do: once you have the array of objects, you pass it to the cart component's addlines() method, which only accepts an object in the form of {lines: [...]}.

Note that there is an alternative method for adding to the cart: addLine(). It requires data in a similar format, but is used for adding only a single line at a time (note, however, that addLines() will also accept arrays with one entry in them). The benefit of using addLines() (as opposed to constantly calling addLine()) is that it will do it all in one call to the server, and the NetSuite code will process the items all at once, which is much more performant.

Also, a note for you folk who are running sites that don't have the extensibility API (pre-Aconcagua): this is one of the areas where you will need to change your code for it to run on your site. Instead of using the cart component's method to add lines, you will need to use the LiveOrder.Model.getInstance().addLines([...]). It behaves in exactly the same way, except you don't need to pass it an object where the items array is a value; instead, just pass it the array directly. (Remember to add the live order model as a dependency.)

One final note: failures. What happens if the lines the user submitted in their CSV doesn't match a valid item in your inventory? Well, it obviously won't be added to your cart. If you test out my sample data, it'll probably fail on your site because you don't have the same inventory as my test site. What happens? Well, it'll still make the call to NetSuite, but nothing will be added to your cart. If all the items submitted are incorrect, then it will return an error to the frontend ("Failed to add to cart"); however, if at least one was successful, then it won't return an error and all valid items will be added to the cart. It's all a little awkward, I guess, as we can't return an error to the user and say something like, "The following items were added successfully, but these ones weren't...".

You could write some functionality that stores the state of the cart before the call is made, and then diffs the updated cart using the CSV data as a guide. It would also be nice to show specifically which items were added in some sort of confirmation. That could be a fun project.

Don't forget that PapaParse also records data about parsing failures by attaching failed rows to the errors array, so you could write some code that checks those for entries and then returns information and advice about them to the shopper. Again, another fun project should you want to go down that route.

Add a Template

In the Templates directory, I have stevegoldberg_quickaddcsv_quickaddcsv.tpl, which has the following in it:

<div class="csv-quick-add-box">
    <div class="csv-quick-add-box-left">
        <label>Or upload a CSV file</label>
            <input class="csv-quick-add-box-left-input" type="file" id="csvinput" name="csvinput">
        </label>
        <div data-type="alert-placeholder"></div>
        <p>See our <a href="#">help pages</a> for more information on using CSVs.</p>
    </div>
    <div class="csv-quick-add-box-right">
        <label>&nbsp;</label>
        <div class="csv-quick-add-box-right-main">
            <button class="csv-quick-add-box-button" data-action="csvupload" id="csvupload" name="csvupload">Upload</button>
        </div>
    </div>
</div>

You should see now how the choices we made in the view file match up to the template now. We have the identifiers in the events object for our events in the template. Other than that, there isn't too much going on here. I've added in a error placeholder so that if the errors we coded in get triggered, they'll be shown there. I also added some generic text explaining that customers can read more about CSV imports in the help section; I've not created this page or anything, but it's a prompt for you to do it, should you want to.

As for the structure of the HTML, I've used the existing quick add template as a template because, if we use the same structure, it'll be easier for us to reuse existing Sass when it comes to styling.

Add a Sass File

Speaking of which, in Sass, I have _stevegoldberg-quickaddcsv-quickaddcsv.scss, which is quite simply this:

.csv-quick-add-box {
    @extend .quick-add-box;
}

.csv-quick-add-box-left {
    @extend .quick-add-box-left;
}

.csv-quick-add-box-left-input {
    background: #FFF;
}

.csv-quick-add-box-right {
    @extend .quick-add-box-right;
}

.csv-quick-add-box-right-main {
    @extend .quick-add-box-right-main;
}

.csv-quick-add-box-button {
    @extend .quick-add-box-button;
}

As you can see, I'm just extending the classes of the existing quick add functionality; in the case of the input field, I've had to style it white because the default stylesheet in my browser sets the background to initial, which computes into transparency.

Save and Test

And that's all the code. To test this, I would recommend generating a number of CSV files with different data in them. I'd have a 'happy path' CSV which contains a number of different products with different internal IDs, quantities, and item option fields (names and values). I'd also have files that test out each of the validation rules we put in place:

  • CSVs with over 50 lines in them
  • CSVs with junk values in some of the fields
  • CSVs with unusual characters in them (do CSVs/PapaParse support emoji 🤔?)
  • Malformed CSVs
  • Non-CSVs (including those with Excel file extensions)

Remember, your job in testing is to try to break the code (and then fix it) before your users get a chance to, so get creative.

Save, deploy, and activate.

Final Thoughts

This was a fun thought experiment based on a conversation with a customer. Just to reiterate: this isn't a complete solution, this a demonstration for our own edification. It shows how you can plug new functionality into existing functionality. We looked at how the addLines() method works, which I can see is not obvious from its code and documentation. We also looked the file API, which is built into the HTML standards, and how we can access files on the user's computer, when they choose them, and without having to send them up to the server. I've even willfully labelled the button for this "Upload", even though we know now that this is inaccurate.

If you do decide to pursue this further for your business, remember to get in contact with your customers and try to understand what their needs are and how they might use this functionality. I would also try to build out some of the feedback mechanisms, in particular in regards to items that have failed to add, because the customer needs to know if some of the lines didn't go through.

If you want to download my code, see QuickAddCSV.zip.