Flatfile’s Data Onboarding Platform: The Elegant Way to Import Spreadsheet Data

No one wants to spend hours formatting data in Excel. Flatfile is on a mission to fix that.

B2B data migrations are a pain

Migrating B2B data is typically an inefficient, costly, and outdated process. Non-technical customers are tasked with formatting messy CSV templates, while even the most senior engineers are doomed to building and maintaining a custom data parser, or wasting hours wrangling data in Excel. Both are shortfalls of a larger problem associated with Data Onboarding.

Nearly every software engineer has experienced the pain of building a custom data importer, and usually not for the first time. Building a data import solution takes development efforts away from improving core, differentiating product features. Ultimately, this results in a high operational cost, while funneling customers through a subpar data onboarding experience. Enter Flatfile.

The definitive B2B data migration solution

Flatfile offers a turnkey data importer that can be embedded into any application, and provides an intuitive data onboarding experience for customers, partners, and vendors. This powerful data onboarding platform is easy to implement, and provides an exceptional user experience for your customers.

In this article, we’ll go over some pain points of the traditional way to import data into your application, and we’ll explore how using Flatfile could improve your workflow.

Flatfile solves all pain points associated with data migrating, validating, and onboarding.

Background

Building your own data importer can be a challenge for many reasons. It not only requires a lot of engineering work to do it right, but also means engineers need to dedicate precious development time to maintaining the importer. In addition to building your own custom data importer, FAQs, documentation, and lengthy instructional videos often need to be created to help users understand how to import their data. But why should your users have to waste time learning how to get data into your application? This is a problem better solved on a product-level. Another route is forcing your users to only import files that comply with prebuilt CSV templates – but why should your users have to figure out how to morph their data into your template’s format? 

Making your customers struggle with how to onboard their data into your platform – before they even get to see the kind of magic your app does – is a poor user experience. Especially in the case where a spreadsheet import fails, it’s hard to correctly inform the user what might have gone wrong. After all, file imports can fail for various reasons such as special characters, large file sizes, incorrect formats, connection timeouts, etc. 

The traditional way of importing data isn’t an enjoyable process for customers who have to prepare their own files for upload or for the developers who have to build and maintain a data importer. Additionally, it can be tiresome for customer service representatives who end up handling their users’ data import related questions and issues.

Flatfile Portal solves all pain points associated with data migrating, validating, and onboarding.

Data Onboarding with Flatfile 

Flatfile’s elegant data import wizard ingests, structures, and validates messy customer spreadsheet data. It guides non-technical users through an intuitive import experience and provides confidence to users importing their own data. 

“It’s simple to set up and many customers are able to get started in as little as a day: a massive improvement over 3-4 engineering sprints [for building a custom data-import solution] with continuous maintenance tacked on” (Source).

Auto-mapping spreadsheet column headers

Flatfile makes the data migration process more efficient for your customers by learning how their data should be mapped over time, and can provide intelligent recommendations. This means virtually no time wasted on manually mapping and assigning column headers when importing CSV files.

Flatfile automatically maps header columns to match a specified data model. This results in higher quality data in your backend, which means less manual data wrangling for your engineers.

Data Hooks for complex data validation

With Flatfile, you have access to powerful features such as internationalizing your user’s data to automatically translate content to their chosen language, data hooks that enable in-line data validation and transformation, and webhooks that let you pull data directly into your own database.

Flatfile’s Data Hooks enable virtually any data to be transformed during import with limitless use-cases. Have a customer that’s importing data containing zip codes from the northeastern US? Data Hooks can be built to ensure that imported zip codes contain leading zeroes, a common spreadsheet error.

Flatfile Data Hooks allow for any data to be transformed during import. Zip codes, US/UK dates, product SKUs, can all be formatted correctly, freeing your team from having to manually wrangle data.

Flatfile provides a production-ready platform, leagues ahead of solutions commonly built in-house. While you could probably dedicate a small team of engineers to work on a custom data importer, such a project would be quite expensive, time-consuming to maintain, and in the end it still wouldn’t be as good as what Flatfile has to offer out of the box.

Let’s take a look at an example.

Building a CSV importer in minutes with Flatfile

For this example, we’re going to use ReactJs to quickly build a data importer that doesn’t allow duplicate emails to be uploaded from a CSV file. The CSV file will contain multiple formatting errors, which Flatfile will format and return as structured JSON. The structured result can then be pushed to any API, endpoint, or database.

Prerequisites:

  • You must have create-react-app installed
  • You have to obtain a license key by creating an account (it’s free for 14 days)
  1. Open up your terminal and create a new react project (you can name it anything).

> npx create-react-app flatfile-demo

  1. Import FlatfileButton
  2. npm install @flatfile/react

import { FlatfileButton } from “@flatfile/react”;

  1. Replace the <p> and <a> tags in App.js with
<FlatfileButton

         licenseKey={LICENSE_KEY} // your Flatfile license key

         customer={{ userId: "12345" }}

         settings={{

           type: "Contact",

           fields: [

             { label: "Full Name", key: "name" },

             {

               label: "Email",

               key: "email",

               validators: [

                 { validate: "unique" }

               ]

             }

           ]

         }}

         onData={async (results) => {

           return "Done!";

         }}

       >

         Import Contacts

</FlatfileButton>

 

  • LICENSE_KEY: the license key you obtain from logging in to your Flatfile account
  • {validate: “unique”}: ensures there aren’t any duplicate email addresses in the uploaded file
  1. Run npm start in your terminal and you should see this:

  1. Click the “Import Contacts” button

  1. Drag and drop or click “Upload data from file” to upload a file. I used this CSV file:

  • As you can see, there are several issues with this file
    • Category names are weird (“Fullname” is one word, “EMAIL” is all caps)
    • There is extra whitespace for some of the names
    • The last two emails are duplicates

      8. On the next screen, click “Yes” when asked “Does this row contain column names?”

      9. You should see a warning saying 20% of rows fail validation for the duplicate emails. Confirm mapping and click Review.

    10. Here you’ll see the duplicate emails clearly highlighted. You can either fix them here directly or discard these rows from the upload.

    11. Hit “Continue” after fixing the issue, and that’s it! You get to choose however you want to handle the data. 

Conclusion

Importing spreadsheet data to an application is a common, but frustrating process. Formatting issues, and validation problems ultimately create a frustrating data onboarding experience for your users. Rather than use precious development cycles building an in-house spreadsheet importer, give Flatfile a try.

For more information, check out https://flatfile.com.

Shoaib Shaikh

Toronto, Canada

Shoaib studied Computer Science (with a focus in Artificial Intelligence) at the University of Toronto. His interests are in Software Architecture, Automation, and Fintech.

Software Weekly

Software Weekly

Subscribe to Software Weekly, a curated weekly newsletter featuring the best and newest from the software engineering community.