Excel to SQL — Bridging the Gap Between Business Analysts and Developers
The Problem
Business analysts and developers work with data in different ways. By and large, analysts work with Excel spreadsheets output from company applications, from Power BI reports, or from CSV extracts provided by developers. The developers themselves tend to be more comfortable accessing raw data via SQL queries.
The difficulty with this divergence is that it’s often the business analysts who decide that selected data records need to be updated, and it’s then the job of the developer to perform that update.
The problem then becomes how to update a SQL database from an Excel file that was manually created or edited by an analyst. The developer has a few options:
- She can train the analysts to produce a file formatted in just the right way and write a common script to parse that file (not practical when different data is being updated in different ways).
- She can take a crash advanced course in Excel to try and work out how to extract SQL from rows of data (possible for simple INSERTS, but not for complex scripts).
- She can write one-off custom code to produce the SQL script required. Different code for each update.
For anything more complex than a simple INSERT script that mirrors the Excel file, the developer is likely to take the more time consuming and costly option of writing custom code.
I’ve encountered this problem repeatedly in many different companies over the past 10 years. Small eCommerce operations, large multi-national financial services companies, and the SMEs in between. Even large government agencies are not exempt.
Existing Solutions
Google “Excel to SQL” and you’ll find a host of free and commercial solutions to this problem. But none of them are robust solutions capable of solving genuine business problems and use cases. They work if you’re playing with student or hobby projects, when all you want to do is take a CSV file and create one INSERT statement per row.
But that kind of simplicity rarely exists outside the classroom. Here’s an example of a more common use case, where business analysts worked with user data from an Enterprise App that was undergoing a major overhaul. An update script was required for each user that met certain criteria.
- An INSERT record to a separate table for dormant users, followed by an UPDATE to a core user table.
- An UPDATE to connect each user with a newly created companies table, if the user email exists and if the company name exists in the database’s new Company table.
- A bulk update of roles assigned to a user — again, only if that user’s email address exists. The bulk update might include a DELETE followed by a series of INSERTS for the user.
Individually, there’s nothing overly complex here. The complexity lies in generating a single SQL script based on the Excel file that contains the changes required. The script generator would need to read each row, check the contents of specific columns for certain values, and generate one of many scripts based on what it finds, inserting values from other columns into the script automatically.
This is far beyond the scope of any “Excel to SQL” SaaS app you’re likely to find from a Google search — which leaves the developer with custom code, every time.
I first encountered this problem years ago and went looking online for a solution. It seemed to me that if I was having this problem, so too were other developers. I was expecting to find an off-the-shelf app or library that allowed me to open an Excel file, tweak a few parameters, enter some SQL and click a button to generate my script.
I was mistaken. No app. No .NET library. Not even an over priced SaaS product that would help me.
My Solution — Gryphon SQL
This is the problem that I’m trying to fix with Gryphon SQL [1]. It functions as a text replacement tool that combines Excel file parsing, conditional checks on row and column data, and smart SQL-aware string replacement. At its core is the ability of developers to generate a custom SQL script based on their own SQL template of one or many queries.
It can handle the above User data example with relative ease, generating a series of scripts to UPDATE dormant records, DELETE and then UPDATE roles for a user, and UPDATE user records to apply company IDs based on the supplied string values of “Email Address” and “Company Name”.
It’s early days for Gryphon SQL. With Version 1 I was very much scratching my own itch, building a tool that met my own unique requirements and use cases. Version 2 will be a more fully featured app, ready for prime time use by developers everywhere.
Oh, and a website. That would be good too. 🙂
To hear more from me, you can follow me on Twitter or on LinkedIn.
Originally published at http://darrendevitt.com on May 29, 2020.
