Getting a Spreadsheet Into a Database: A Primer for Customer Success and Operations Teams

Every day, thousands of operations and customer success teams are faced with the task of getting data out of spreadsheets and into databases. You'd think it would be a simple process.

But it's a huge pain.

Here's the issue: a core function of your database is to exclude unwanted or inaccurate data. Spreadsheets cannot make any guarantees about the format and contents of the data within.

So it falls to you to review and fix CSV and Excel files by hand before your database can accept them.

Is there a more effective way to prepare spreadsheets before trying to onboard them into a database?

The orderly structure of databases

SQL databases are very selective about the data they will accept. Each field must:

• Match a specific name;
• Match a designated type; and
• Adhere to all field constraints (e.g., is required, is unique, matches a foreign key).

If a data file arrives that doesn't meet all the requirements, your database will refuse it.

One of the great things about spreadsheets is their flexibility. This is also part of the problem. When it comes to Excel and CSV files:

• Fields can be named randomly; and
• Types and constraints are not enforced.

Although some spreadsheet applications like Google Sheets offer "data validation" features, they can be easily bypassed, providing no guarantees about the final form of the data. Consequently, in their raw states, spreadsheets and databases don't make for a harmonious pair.

When you attempt to import a spreadsheet into a database without due preparation, you encounter errors. Nasty errors.

Think, "row 3, column 7: invalid."

These messages were typically written by a database engineer 30 years ago, and they're not exactly helpful. It's up to you to decipher them and then embark on a miserable scavenger hunt to repair the data.

The uncertainty persists until you attempt to submit the data again, as it's not possible to preview whether a database will accept spreadsheet data.

It's like your database has a dress code, and spreadsheets show up in flip flops. As Barney Stinson would say, it's time to "Suit up!"

So, how do you prepare that messy spreadsheet to meet your database's rigorous requirements.

Specifically, you'll need to:

• Align field names to match the database schema;
• Convert values to the appropriate data types (e.g., an integer must be an integer); and
• Enforce any other constraints the database expects.

Just like any comprehensive cleanup, getting your data in order demands significant time and effort, and a strong drive to keep you going.

Many companies manage this problem by appointing a dedicated "data person" to serve as a spreadsheet wizard.

While a popular approach, this solution demands a complex set of skills and often a fair amount of workaround strategies to format data as quickly as possible.

This approach does not scale well as the volume of data files increases and places a heavy burden on a skilled employee, which could lead to job dissatisfaction. Since it would be extremely cumbersome to document all of their micro-hacks and formatting tricks, it's challenging for them to ever hand this task off to anyone else, breeding resentment and frustration.

Some companies choose to build a DIY importer. The trouble with this approach is that it is usually under-resourced and requires producing extensive documentation to paper over any missing features.

Lever, a well-known SaaS talent recruiting platform, took this bumpy road. In order for anyone to actually use the importer, you must provide a dense user guide that includes a series of complicated steps, such as converting dates into UNIX milliseconds.

This route creates more work for the customer success and operations teams, which has to maintain the documentation and still has to manually fix spreadsheets when users get confused and give up.

Fortunately, data file importing software already exists that can get your spreadsheets up to speed in no time. An importer can guarantee that your spreadsheet data meets any specifications before you try to load it into the database, ensuring there are no onboarding errors.

Dromo, for instance, takes all the rules of your database and applies them to any spreadsheet through a user-friendly interface. Dromo is not intimidated by unruly data; it's designed to handle it.

For any operations and customer success teams weary of wrestling with spreadsheets, Dromo can help you reclaim your time and energy. Say goodbye to the endless spreadsheet cleanup and focus on your core responsibilities.