Implementing a Powerful CSV & Spreadsheet Import Experience by@mikefrederick

Implementing a Powerful CSV & Spreadsheet Import Experience

Read on Terminal Reader
Open TLDR
react to story with heart
react to story with light
react to story with boat
react to story with money
CSV Import experiences are hard, often harder than you think. Create a powerful, customized CSV importer with Flatirons Fuse.
image
Flatirons Development HackerNoon profile picture

Flatirons Development

Flatirons Development is a Rails/Node/React/React Native + UX/UI shop that focuses on startup growth!

TLDR; An in-house CSV/Spreadsheet import tool is not an easy thing to build, and the number of companies that do it well are few and far between. Inefficiencies in data imports are often mitigated by troubleshooting articles or FAQs or handled by support calls. Poor error messages, helpdesk articles and FAQs put the onus on the customer to figure out what went wrong where. Support calls take up time and effort (and as a result, money) to resolve. Your customers are as busy as you and your developers are, and do not have the time to troubleshoot a less-than-user-friendly import experience. Your data importer is often the first experience the customer has with your products and services and a challenge here can cost you many opportunities. A strong CSV importer can be the difference between onboarding and attrition.

Data is the lifeblood of your business, and obtaining it quickly and easily is the goal of any business application. In order to grow as a business, your method of data import needs to be as seamless as possible, allowing your customers to provide you the information you need without delay. A CSV importer is often the solution, sparing customers the pain of manually copying data into your database. Unfortunately, this means you need to develop/build a way to integrate this customer data in as efficient a manner as possible. Or do you?

Developing a CSV importer is a difficult task, one that requires significant investment development time. Best case scenario, it is equivalent to building a feature of your application/site/solution. Worst case scenario, it is like building a separate product altogether. And this investment of time and energy is not guaranteed to be successful.

That’s where a pre-built tool can become a life-saver, particularly if it can be integrated into your existing framework. Flatirons Fuse is just what the doctor ordered...a white-labeled, customizable interface solution that can handle complex data imports, and which integrates seamlessly with your web page or application.

What A Poor Import Experience Looks Like

Building a CSV importer is no easy task, and there is nothing worse than seeing customers struggle with a solution you have invested time and effort into.  We have seen numerous issues derail the customer’s CSV import experience, including, but not limited to the issues below:

Unclear Guidance

External data is not often going to be naturally configured to the requirements of your system. Some level of data manipulation is typically required. But if it is not clear what changes are needed, the import is all but guaranteed to fail. There are so many problems that can prevent an import from succeeding. Clear guidance can solve most of them. For example:

  • What fields are required?
  • What types of data need to go into those fields?
  • How do I get all my existing data into a new template?
  • Is there a size limit to what the importer can handle?
  • Does the importer only accept a particular CSV format? (UTF-8, for example)
  • Does the importer accept spreadsheet formats such as XLS or XLSX?
  • Do columns need to match? In a particular order as well as name?
  • How do I fix my data? For example, does location data need to be input as GPS coordinates, or traditional address information?
  • Can data be updated/fixed during import, or will you have to abort, fix and retry?
  • Is there a template that needs to be followed?
  • What happens if an error occurs with just some of the records? Were the rest imported?

Users, and even developers, rarely think about these things until it is time to import the data. Guidance on the above topics can certainly frame the import process for the client. However, guidance only goes so far - it puts the onus on the customer to interpret the instructions and to ensure the data matches the instructions.

Should the user have to watch a 15-minute tutorial video or read several pages of instructions to perform an import? Should the user have to spend an hour combing through a CSV or spreadsheet file to ensure it matches the instructions provided? If an error occurs, should the user be expected to troubleshoot?  As you can see, even the clearest guidance can still result in support tickets or calls. Unclear guidance can cause no end of grief.

Unintelligible Errors

When the import fails, the user needs to know why. A trial and error approach can result in a great deal of frustration, and unnecessary work, particularly when the issue should be simple to fix. When you are dealing with a lot of records, it can be hard to communicate issues back to the user.

If an import fails, and you receive the following error, would you know what to do next? The below error was received by a user of the thirtybees e-commerce platform, and posted to their forums. As you can see, there is not much to assist the user in this message.

image
Here is another similar situation from Confluence/Jira. In this case, note that the directions suggest reviewing the logs to determine what the problem is, and investigate the error. It also suggests that the data may be in an unstable state.

image
Did the data get corrupted in the import process? The error does not state that the data is definitely corrupt, but the implication that the importer could have ruined your data does not exactly inspire confidence. Do you want to go through the effort of configuring the data in a CSV just to risk losing it in another attempt?

As you can see, there is a prevalent expectation with many in-house importers that the user should investigate, then troubleshoot the issue. There is also a sense that the issue is the customer's error, not a problem with the user experience of the website. If this was your first experience importing a spreadsheet or CSV for a product, would you continue, or would you look for a solution that worked better? One in which the directions and the error messages both were clear and upfront?

Data Transformation Issues

Transforming data from one system to another is a hard exercise. It is highly likely that whatever system you are exporting data from has different field names or data structures. If a user can’t easily transform data into a format that fits your systems schema, they are bound to be frustrated.

For example, let’s say you export data from a system that has an “Address” field, but in your own system you split address fields into Street Address, City, State, Zip Code, Latitude, and Longitude. This is a complex situation to deal with. You will likely have to use an external API to geocode addresses from the old system so that you can transform and map the data to the columns in your own database.

Data Mapping Issues

There are numerous challenges to mapping fields in a CSV or spreadsheet for import. Quite commonly, the limitations of the importer can be the biggest challenge. For example, let’s look at an example of a product with their own importer. EmailOctopus is an email campaign product competing with products such as Mailchimp. They offer a fairly strong import solution considering it is not their core strength. However, you’ll notice quite quickly that the fields they recognize are quite limited. For example, after creating a simple test CSV and importing it, only one field is recognized, the rest are set to ignore.

image
You’ll note that the headers in the CSV are fairly standard. The Name field provides a first and last name, and from the prompt on the second column, we can surmise that they wish these to be split into separate fields. Zip Code is not recognized, nor is Company. It does allow you to map these fields to acceptable fields within the system. Company, for example, should prompt with Company Name as an option, and it does not. In fact, it offers the same options for all three, without any consideration of the content of the fields or the header name.

image
The user should not have to work too hard to fit their data into your organization’s parameters. New columns should not have to be created for commonly used columns, particularly those that do likely have an on-site equivalent, such as Company Name for Company. This can produce additional work within the product itself if what amounts to duplicate columns need to be cleaned up.

Mapping columns from an old system to your own should be an easy, simple experience.

Product, Development, and Sales Resource Constraints

Creating a great CSV import experience typically isn’t the focus of your product. Furthermore, sales teams often need expensive developers to sidetrack themselves from important features in order to onboard customers. Getting sales-related data into a system is critical to company growth and minimizing the sales cycle.

How Flatirons Fuse Provides a Seamless CSV Import Experience

Now that we’ve outlined some of the key problems that can result in a poor import experience, let’s look at a solution, shall we? Flatirons Fuse offers a solution designed specifically to help businesses stick to their core strengths, to focus on improving their product, their service, and their expertise. Let’s look at how we help and frame our capabilities in relation to the problems mentioned above:

Clear Guidance

A clean, simple, and direct interface is key to a positive user experience. Potential customers should not need to navigate between documentation and your onboarding site to get through the import process. Our interface provides the critical information necessary to get their data imported into your system by default.

image
  1. As you can see, the customer is immediately provided the file types supported by our importer.
  2. Required headers are clearly identified, as well as optional headers. This guidance ensures the customer knows exactly what is needed. The information here is customized based on templates that the client can manage and create for whatever the system might be. For example, the required headers for a web-based financial application might be very different from a CRM. But from the importer's perspective, it is clear, simple and easy.
  3. Once you know the file requirements, a simple drag and drop interface and an option to browse for your files are the de facto standard. The directions are simple, the process just as simple.

Mapping Imported Data

Once your customer’s file is uploaded, the process remains just as clear and simple. The next step is simply to identify and confirm that there’s a header row so that we can perform column mapping.

image
Finally, we can verify the results. You may have noticed above that while the system accepts Address as a field, the user uploaded a file that provides Location instead. This is where manual column mapping comes into play. Note that while it says that we matched columns correctly, we have a duplicate in the results.

image
Our AI-powered column matching correctly mapped all columns, with the exception that Email Address was mapped to Address.

So, we have a duplicate column, and a missing column…start over, right? Not at all. Simply click the X on the second Email Address column in Your Columns. In the drop-down, select Location from the headers available in your CSV file. An easy fix, and we are good to go.

image
Click Continue, and you’ll see the results of this minor change. As you can see, the information is now associated with the approved headers/columns for the target system, even with a mid-stream correction.

Now we see that there is another small issue. Our validations have automatically flagged one of the emails provided as not being formatted correctly (missing the @ symbol).

image
Simply click in the box, and correct the error.

image
With clear direction throughout, AI helping to match headers (even if not always correct), and the capacity to make necessary changes even while uploading, the customer will have no problem importing the data to your system. All that remains is to submit the data.

Fixing Backend Import Errors

If there is an issue in importing the data, our validation system will kick into gear, based on criteria provided by you, our client. Error messages can be customized to provide the info the customer (the importer) requires to resolve whatever the issue may be. The below error message is simply a test message to illustrate the function:
image
Upon receipt of the error, simply click OK, and the problem will be highlighted in the results area. As you can see, the system detected the error and highlighted it clearly for the user. In this case, we are told the entry is a duplicate value. We know exactly what the problem is, and have a good idea of how to fix it.

image
So we change the entry, or delete it (if it was a true duplicate), and we’re ready to try again.

image
Looks like a success to me. Your customers will have clear instructions to set up the import process, clear guidance throughout the process, plus true validations to help deal with any errors that come up. All without the need to spend countless hours developing an importer that may not meet these requirements, resulting in frustrations for both customers and support.

image

The Bright Side - A Free and Powerful CSV Importer Exists

Why call out the bright side when the preceding content is pretty bright to begin with? Well, because there is no dark side, of course. In case it isn’t clear, the side we will now cover is the client-side - how easy Flatirons Fuse is to configure, our white-label and customization options, and that it is free. Not only free to try, but also free forever, providing your organization doesn’t exceed 10000 records per month. So when we say you get more than you pay for, we clearly mean it.

Getting Started for Free

Fuse has a tier that is free for life. So you want to get started, or at least see what the fuss is about. Simply click Sign in from our website.

image

This will take you to our Login/Registration page. Sign in with your email address or Github account if you are registered, or click Sign Up if not.

image
Fill in the form, or register via github (in which case your account details will be filled in from Github).

image
Once registered and logged in, we make it easy to get started immediately, by creating your first template. Our Get Started wizard gets you underway immediately. Simply click Create Template and you are underway.

image

Creating the Template

Upon clicking Create Template, you are prompted to provide a name for the template. This should reflect its purpose. You can create multiple templates for multiple applications/sites if necessary. But we’ll start with just one.

image
Next, we’ll need to add some columns. Here you can see how easy it is to add columns. Make your selections and remember to select whether the columns are required or not.

image
Note that there is a great deal of flexibility in the type of data that can be included, such as boolean (yes or no) values, string (characters), integers (whole numbers) structured data, and more. Simply select the best fit for your needs for each column. This flexibility is one of the key reasons that Flatirons Fuse can meet the CSV import needs of so many organizations.

Initializations, Data Transformations, and Hooks

Validations and data transformations are not limited to those we provide by default, like email format. Custom validations for your organization can be enforced using initializations and hooks. These additional validations can be applied very simply with a few lines of code. For example, you could implement a function that auto-capitalizes the first letter of a column named City, removing the onus from the user. Or, you can implement a validation that Zip Code must be limited to a grouping of five numbers. The guidance linked above can help you set any number of custom validations, or let us know what you need and we’ll help you set it up.

Testing the Template

That’s all there is to creating a template. It is now ready for testing. We provide the means to test your template within Flatirons Fuse, and our documentation guides you on the steps to do so. Follow the guidance to test the solution on the platform that best fits your use case.

A White-Labelled CSV Importer

A Software as a Service solution, particularly one designed to serve as part of your onboarding process, isn’t much of a solution if it does not look like part of your stack. The idea of our importer is to blend and become a seamless part of your user experience. To that end, our solution is completely white-label and offers numerous customization options to integrate with your branding.

In fact, you can see how to integrate these options in the third step of the Get Started Guidance.
image

Upon selecting customize importer, you will be taken to our customization page, where you can provide your company logo, and specify the color scheme you wish to use to make our importer your own.

image
Within just a few short minutes, you can have a basic template setup, test it, and customize it to fit your product or site.

image

Free is Good

Oh, and did we mention it’s free? Because it is. Our pricing system is clearly outlined on our website, if you want the full details, but the gist of it is that our solution will not cost you a thing until you begin to exceed 10,000 records per month.

Summary

By leveraging our own singular focus on improving your onboarding and CSV/spreadsheet data import process, you can deliver on your full organizational potential without worrying about sideline development efforts, documentation of a problematic process or support calls at the outset of your onboarding process. You can focus on whatever you do best, while we handle what we do best.
react to story with heart
react to story with light
react to story with boat
react to story with money
Flatirons Development HackerNoon profile picture
by Flatirons Development @mikefrederick.Flatirons Development is a Rails/Node/React/React Native + UX/UI shop that focuses on startup growth!
Read my stories
L O A D I N G
. . . comments & more!