CSV import as a process is broken. Messy customer data, edge cases, encoding formats, error messages, non-technical users: importing data into applications is a huge pain! Ingesting data has been long neglected as a software product experience, leading to customer frustration and wasted engineering cycles rebuilding what those users already expect to have. It’s a major distraction for product teams focused on building core differentiating features.
We’re going to look at the problems with turning messy spreadsheets into structured product data, how it’s typically addressed, and how Flatfile Portal solves the technical and user experience challenges inherent in CSV import.
If you’re a software developer and have built a CSV parser before, you know how frustrating it is to dedicate valuable engineering sprints to just one component of the customer onboarding. Building an entire CSV importer means addressing user experience and technical edge cases that result from involving humans in a highly technical ETL process. Trying to bake in more advanced functionality such as data normalization, column-matching, or even refining the interface itself results in developers building an entirely new product before the first one is even finished!
Investing engineering sprints to maintain an outdated data importer, or worse, building a CSV importer from scratch is now a pain of the past. Today, we’d like to show Flatfile Portal, which allows developer and product teams the ability to revamp their entire data import flows not in weeks or months, but in minutes. Did we mention you’d save thousands of dollars in development costs?
Common Problems with CSV Import Experiences
Importing CSV data is often one of the first interactions users have with a software application, especially “empty box” products. Unfortunately, there are too many ways that this data import experience can cause customer frustration, or worse, churn.
For users, an inefficient importer experience will cause them to question the value of the product itself.
“If the app can’t import my data easily now, what’s going to happen once my data is finally uploaded?”
Your customers shouldn’t have to battle with these kinds of CSV import errors (Source: Flatfile)Digsy.ai shared their experience with handling data imports for their real-estate CRM product prior to integrating Flatfile Portal. Not only was the team strained on resources from building and maintaining a proprietary data importer, but Digsy’s engineers also spent ten or more hours per new user cleaning up and formatting incoming customer data. Occasionally, these users would churn, rendering all that work fruitless.
Thankfully, there’s an out-the-box CSV importer that can do all this on its own, and with just a few lines of code.
Introducing: The elegant import button for web apps
An animation of an import completed with Flatfile Portal. (Source: Flatfile)
Let’s look at issues associated with traditional CSV import experiences and how Flatfile Portal addresses them.
Issue 1: Unclear Guidance
Users tend to struggle with CSV imports, and usually have questions before the CSV is even uploaded. Here are questions users may ask during an import:
- Can I upload XLS, XLSX, or XML files?
- What is UTF-8 encoding?
- What if my file is 9.7 MB?
- Is it a problem if my file has special characters in column headers?
- What happens if my spreadsheet columns don’t match the required fields?
- How do I fix my data? Do I need to save a duplicate CSV and upload that file instead?
Unless your users spend a lot of time exporting and importing spreadsheets, they’re not going to think about these situations until the moment they import their data.
It shouldn’t be up to your users to read through intimidating data import documentation or watch a 15-minute tutorial on how to import spreadsheets into your product. Surprisingly, developers aren’t spared either! Although engineers understand the complexities of importing spreadsheets into an advanced system (like Microsoft Azure), there is still an exhaustive amount of content they need to ingest before their first import even happens.
A highly technical product like Microsoft Azure attempts to reasonably present developers with extensive documentation for importing user data. (Source: Microsoft Azure)Your product experience should make it simple to import CSV data without requiring users to become data scientists. The same goes for the technical understanding required to build a CSV importer to begin with, a goal we’ve dedicated reaching with Flatfile Portal.
Here’s an example of a simplified CSV import solution for a CRM from Portal, created in less than an hour complete with complex data validation.
- Import their data using a CSV or XLS.
- Match their spreadsheet columns in the next step, (if Portal’s 95% fuzzy-matching doesn’t catch it during an import.)
- Click “Continue” to begin their data upload.
There’s no need to overwhelm the user with warnings about file encoding, incorrect date formats, or what fields are required. Portal solely focuses on importing CSV data from the user and making it a delightful experience during this first data onboarding touchpoint. Mapping columns and validating data will be completed at a later stage once the importer has matched the spreadsheet header columns using human-in-the-loop machine learning.
Portal’s JS configuration allows data models to be replicated in minutes instead of weeks. The label matches a CSVs column name, and the key is how you’d like the imported data to be saved in the JSON output. Portal also provides powerful validation options to work with any data model requirement and supports regex, data normalization, and server callbacks for those unique validation use-cases.
With Flatfile Portal, you and your users won’t have to worry about things like file sizes or encoding formats causing problems during import. Portal helps you manage imported data via the browser or through a server-side process, enabling you to split and upload large CSV and Excel files without dropping imported data.
A Flatfile demo that shows how you can reliably split and import data from multiple customer files. (Source: Flatfile)Flatfile allows users to import CSV data from multiple files intuitively, without dropping data or doing manual splitting. In this demo, users are allowed to import spreadsheets containing three different sets of data. Not only will this help make their files more manageable, but it’ll make it much easier for your product team to ingest and organize customer data on your backend into a consistent structure.
Customers shouldn’t have to think here. CSV importers should be designed — error messages and all — to make data onboarding a quick and painless experience for users.
Issue 2: Inefficient CSV Column-Mapping
The next source of frustration often appears when poor column-matching functionality is in place.
As an example, let’s say that a Mailchimp user wants to import as many contact details into the email marketing software as they can. After all, it might be useful to have their business title or phone number on hand for future list segmentation.
However, initializing the import results in some data being skipped or dropped entirely:
This is how Mailchimp’s CSV field-mapping system displays unmapped data. (Source: Mailchimp)The app doesn’t recognize three of the four spreadsheet columns in our file. In order to keep the unmatched column data, the user has to go through each field and manually assign a matching label that Mailchimp accepts.
This is the case with many products that ingest user data, especially CRMs. Not all data will be used during an import, however, the decision to allow custom field submissions, in this case, should be left to the user; rather than removing their data prematurely.
We know what you’re thinking: “Why not just provide a pre-built spreadsheet template?” However, this is hardly a solution to the problem and will only create more work and frustration for your users. Especially those that bring in thousands of rows of data, or have 40 column headers (yes, we’ve seen it!)
The problem here lies within the customer data onboarding experience. Optimizing CSV import features within a product has been a difficult and expensive project to take on for product and engineering teams. That’s where Flatfile’s machine-learning, auto column-matching solution comes in handy.
Portal automatically learns which incoming fields are matched to which columns from each of your users. This results in a ‘human in the loop’ machine-learning experience that is truly unique to your product’s data model. Portal will automatically match imported CSV columns to your data model based on user inputs and consistently learns over time.
The importer also caches column assignments regardless of session, so a user that uploads ten CSV files in a day will get most if not all of their columns matched automatically.
ClickUp, powered by Flatfile Portal, has leveraged human-in-the-loop for its productivity web app.
The main data import modal for users that want to import tasks and projects into ClickUp. (Source: ClickUp, powered by Flatfile Portal)Portal is designed to help users of all technical expertise. Instructions are clearly provided as to what the user can upload, and the fields required. In addition, Portal’s manual data entry feature allows users to preview what sort of data can be imported into productivity software. This helps users preserve as much data as possible, rather than realize too late that the data importer didn’t recognize their columns and dropped the data out without any notice.
Configuration flags extend Portal’s functionality even further. For example, the allowCustom flag specifies whether you’d like users to add custom columns during the matching step. Using ClickUp as an example, one could add a column for “Billable”? As a boolean field to track whether a task is billable. Allowing users to place columns on the fly results in a shift of control not seen with legacy CSV importers – shaped by a product’s unique data model requirements.
Portal’s column matching step:
Portal asks users to identify column names for accurate mapping. (Source: ClickUp)This step asks users to indicate where their column names live. This way, the importer can more effectively match them with its own or add labels if they’re missing. Some CSV data may only contain values rather than column headers.
Next, users get a chance to confirm or reject Portal’s column matching suggestions:
Portal uses a machine-learning column-matching system to automatically map users’ imported data. (Source: ClickUp)
On the left, users will find the columns and values they’ve imported. The white tab to the right provides them with automated column matching within ClickUp’s data model. So, “Task” will become “Task name”, “Assignee” will become “Task assignee(s)”, “Status” remains as is and so on.
If one of the labels in the CSV doesn’t have any match at all, the importer calls attention to it like this:
Flatfile Portal calls attention to labels or CSV values that don’t have an exact match in a product. (Source: ClickUp)
In this example, the Priority response of “High” was detected. But “Medium” was not. However, there’s no need for the user to guess what the correct replacement should be. The importer provides relevant options like “Urgent”, “Normal” and “Low” to replace it with. No need to re-import their CSV, or change the cell value prior to importing the data.
Once all spreadsheet mappings have been resolved, the user can easily “Confirm Mapping” or discard the column altogether if it’s proven unnecessary.
Users get a chance to confirm CSV labels and clean up their spreadsheet results before importing their data. (Source: ClickUp)Finally, users get a chance to review any errors detected with their data:
Validation errors detected in ClickUp’s data import appear in red. (Source: ClickUp)In this ClickUp example, “Task Name” is triggered with the “isRequired: true” flag which requires users to submit data for that particular column. Whenever any validation fails in Portal, you have full control over how error messages are displayed to the user – all inline within the importer.
The “Only show rows with problems” toggle in the left-hand corner makes error rows easy to spot and address quickly.
This keeps users from having to:
- Review the original CSV file and fix errors before re-importing again.
- Import the data and do the cleanup afterward in the app.
How do you set up this system of column-mapping and error detection? Portal does most of the work for engineers.
Here’s a JS snippet you might use to customize Portal for a basic contact list:
It’s not easy building a CSV importer in-house. Integrating Flatfile Portal allows you to focus on building differentiating core features unique to your product’s experience, knowing that the CSV import component is taken care of, and optimized.
Build a robust data importer with Flatfile Portal
One of the reasons we build SaaS products is so customers can effectively manage their businesses without the costly overhead of outsourcing to a third party, or the costly practice of trying to build everything themselves.
Importing data doesn’t need to be the reason for customer frustration or churn during customer data onboarding. It’s easy to see how users can be frustrated with the inconveniences of common CSV import errors. Take advantage of a tool like Flatfile Portal whose sole product focus is designing faster and more seamless customer data onboarding experiences for your customers, partners, and vendors.
what if my data is a complete mess? names have typos, dates are in different formats, some fields have “do not touch this” backticks in them, some text fields are not quoted despite containing all kinds of whitespace?
so, any real life handtyped/spreadsheet-as-a-database dataset?
@zakius: Bonilla here from the Flatfile team. The messier the data, the better . We actually created a feature called Data Hooks which allows complex validation to be configured. This automatically cleans / formats data on user import. Super flexible functionality that can be molded to really any validation use-case.
Hope it’s okay to post a link, here’s a CodeSandbox example with hooks enabled on email validation: https://qzvpe.csb.app/ and the backend config: https://codesandbox.io/s/qzvpe?file=/src/index.js
Feel free to shoot us a message through our site if you have any other questions!