May 5, 2023 at 4:15 pm
In today's episode of "Products that Should Exist (and maybe do)":
Have you come across a solution (preferably a web-based portal) that allows an end user to upload an Excel file, digests the data, identifies tabular data, walks the user through column mapping to structured data, performs data validation, highlights invalid data, allows editing of the bad data, and finally allows clean data to be pushed into the structured database? (Basically user-initiated injest of Excel files into structured data.)
The use case here is accepting tabular data from multiple clients that have the data in their own Excel workbooks. This data needs to be cleaned and imported into SQL. We want the client (where the user is often non-technical) to be responsible for getting their data cleaned up prior to import into our SQL. "BYO Excel into SQL"
Any thoughts?
May 5, 2023 at 8:58 pm
Maybe something like Monarch? I used a product called Monarch decades ago that sounds like this tool.
There are some industries/groups of customers with more discipline than others, and some data is more standardized than others.. But my experience has been that end users can come up with infinite ways of providing bad data, particularly with Excel. Excel compounds the problems with its proclivity for turning numeric-looking strings into numbers (dropping leading zeroes), turning date-like strings into dates, turning dates into integers, etc.
The only scalable way I've found to minimize import problems with importing Excel data is to provide the end-users with a locked template that defines columns, column names, formatting, etc., & perhaps macros to validate data... and convert to/save as csv
Even this can be stymied by confusion or refusal to follow a standard, or hesitation on the part of the company to annoy customers by requiring them to change their process.
May 16, 2023 at 11:31 am
Thanks for the reply and for the reminder about Monarch: I remember that from years ago. It turns out it is still around: https://web.altair.com/monarch-free-trial
Also, there is an open-source product in this space: https://openrefine.org
For the time being I am doing something like the locked template approach you suggested, but using https://smartsheet.com which gives a fairly flexible and friendly way to submit data, and supports comments, attachments, forms, update requests, API calls and webhooks.
I still think it would be nice if an end-user-focused data submission web portal for self-service use by non-technical users existed...perhaps based on OpenRefine.
If users can be made to use provided templates that meets my requirements...but not all users (clients, vendors, etc.) can be made to comply. Furthermore this potentially pushes burden and risk of error in transferring data to the user...which I guess is better than being on me, but may be less desirable overall then allowing the user to submit their own Excel file without requiring copying of data to the spreadsheet template.
Thanks again for the reply!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply