This article is part of our Product Feature series. Click here to read the first article.
The Problem: Not immediately seeing incoming data schema
Importing data is hard. We wish we could avoid the huge upfront setup and opportunity costs, but it’s a crucial process to arrive at actionable business insights. After a technical team lists and collects required data sources — which is a task of its own — they have to carefully create a pipeline for importing data that is bug-free. And oh boy are there many possible import errors, here’s just a few:
- Flat files that exceed your pipeline’s file size limit. A common solution is to split the flat file manually which holds back the entire business team.
- Mapping (Schema) errors. Maybe a file you receive changes a column title from SalesData to salesData. Or the POS platform you download data from decides to add an extra blank column. Either scenario fires up aggressive import errors. Frustratingly, any change to the pipeline calls for a back and forth with your data team which paralyzes crucial insight creation.
- Data type validation. Any data pipeline that even touches a large Excel file is prone to unexpected data type errors. You have to ensure that you never change the formatting of a cell because that could have effects on the export data type. And the problem persists out of Excel with limitations on accepted data formats and converting types.
Were any of the above errors (or some of the hundred we left out) to occur, you would not be able to create your reports, dashboards, or ad-hoc analyses. All of your downstream destinations (analytics, BI tools, etc) sit idle expecting data. Instead of arriving at conclusions, you’re stuck waiting for the data team to fix the error with a quick script while time slips away.
If only you had a no-code, visual tool to accurately map your columns!
The Solution: Column Mapper
After creating a Dropbase Pipeline, every additional data import is guaranteed to fit your original schema thanks to Column Mapper. Column Mapper is a tool we built after listening to problems our users faced while importing data. They wanted a no-code visual tool to easily map columns of incoming data before adding it to their database — so we made it. Because of Column Mapper, you’ll be able to:
- Map incoming columns yourself without bothering the data team — saving everybody precious time
- Prevent data type invalidation before it occurs thanks to helpful error messages
And of course, you can continue not worrying about file sizes courtesy of Dropbase’s scalability. Dropbase supports csv files of up to 1.5 GB. Column Mapper empowers you to add to your existing data pipelines so you can focus on other, more exciting tasks. If you would like to check out Column Mapper and Dropbase’s other features, sign up here.
Let’s see some use cases for Column Mapper. In this first video, we show a scenario where we’re updating the product list of our online store. The file we’re uploading matches the original schema exactly and appending the new data is a click away.
In the above video, we drop in a csv to a pre-existing table. Column Mapper automatically runs on the file and matches each column in the new file to a column with the same name in the table. In our table, we initially dropped columns “Variant Inventory Tracker” and “Variant Inventory Qty” and Column Mapper intelligently drops the columns. All you have to do is click Load to Database.
Column Mapper intelligently picks steps to match incoming data with the table’s original schema.
Things fall apart sometimes. Working with flat files, especially excel files, sometimes adds empty columns and adds invisible spaces to column names. Luckily, Column Mapper can detect the following errors:
- columns that have switched places with another→ Column Mapper will automatically place them in the right order.
- extra empty and non-empty columns → A suggestion will pop up to delete or rename these columns.
- missing columns → A suggestion will pop up to create a new column.
In the following file, we’ve switched around columns and added extra empty columns. Let’s see how Column Mapper works with it:
From the above video you can see that it’s really easy to fix data ingestion issues with Column Mapper. The columns that are switched around in the csv file are automatically placed in the right position. A column that was incorrectly labelled “Handlers” is renamed to “Handle”. Extra columns are suggested to be deleted as well. All these steps are suggested for you to take. They only take effect once you load them to database. You can even process a step, not like it’s result, and delete the step to revert back. There’s no coding required to slightly change your input file making it fast and easy.
Column Mapper, in conclusion
Column Mapper solves data ingestion issues for you, your team members, or any data senders who might be sending data through Dropzone. It eliminates the need of having to split large files, or creating multiple copies of your file when you’re trying to upload them to your data pipeline. The best part is that you can do all of the little edits you’d need for data imports without any coding. No need to wait for your data/technical team to fix your in-house pipeline.
If you’d like to try out Column Mapper or any of our other features, sign up for a trial here.
Go back to ← Product Feature 02: Database Manager
Go next to → Product Feature 04: Dropmail
