Design and Implementation of CSV/Excel Upload for SaaS
kalzumeus.com
kalzumeus.com
That's really cool of you, patio11! Most open source stuff I've worked on... I'm happy if I get a "thanks!" from time to time. Sure, it has other benefits, and I wouldn't be where I am today without open source, but handing out some actual cash is very classy.
I wonder if more companies would really consider doing this, though - handing over money that you don't have to is not something I've seen a lot of. I've worked for people who don't even want to let the world they're using various bits of open source software, let alone contribute back anything.
Also, there is a concern that money can really change the dynamics of a community, but by and large, I'd rather see a lot more money funneled to open source than there currently is. For instance:
And that just covers the very specific case of giving an existing project money, and does not cover things like Jetbrains' program that gives free licenses for open source work, Google's Summer of Code program, and various organizations contributing code back to an OSS project.
The pro offering is probably not quite where it needs to be, but to have zero interest at all is pretty discouraging.
My business offers in consultancy-based software development so we are not bound by my state's sales taxes, I was just curious if Michigan has a software-specific law.
- one or more titles on top ("Patient Database", "current as of Jan 5, 2014", etc)
- data in a bunch of different sheets.
- multiple types of "stuff" all packed into one sheet, maybe separated by a few blank rows or maybe a summary pivot table pasted in the top-left corner.
It's easy to handle that if you are pre-processing the spreadsheets yourself, but getting to where non-programmers can prep a spreadsheet for uploading seems hard. How are they supposed to know that column headers belong on the first row, that each row of data should have the same "kind" of thing, etc.?
Anyway, good luck and thank you for the great writeup!
Never tried it myself, but it sounds like a decent solution to the problem of wacky formatting.
I built a system to validate their uploads and let them fix their errors on a website, and all that hassle went away.
(I still gave them the template so they'd know what it was supposed to look like, I just didn't rely on that alone.)
Nobody is "the bad guy", but creators and late adopters have very different understandings of what OSS is for, why it exists, and what to expect.
https://forge.ocamlcore.org/projects/csv/
Actually some of that is not automatable, so this cannot by its nature be a complete solution, but with heuristics -- ugh -- it worked. Months and months of effort to get it to work, and we didn't even have the general public as customers, just a small set of B2B companies.
AR is HIPAA compliant, which implies that there is (medically) sensitive information hitting your servers. Why is it not an issue for you and your support agents to actually see that data yourselves (as you would when manually fixing CSV errors)? If your seeing this data doesn't violate the letter of HIPAA, surely the ethical impetus behind the act would prevent you from doing so?
HIPAA does however have an awful lot to say about what can and cannot be done with this data, how it must be handled, who it can and cannot be divulged to, and so on. For example, when and where it must be encrypted, how its use must be audited, etc.
It is in some ways like PCI compliance. All parties handling sensitive medical/financial data on your behalf have to follow certain secure practices, or risk facing steep fines and legal action.
Just kidding. It isn't actually that bad. Appointment Reminder is a "Business Associate" of Happy Teeth Dental. I'm it's HIPAA compliance officer, attend a yearly training session, have been threatened with the most severe of sanctions if I misused patient data, see only the data required for my job, and have my name and access rights recorded in a spreadsheet ready to be audited (along with my access logs). That's probably half of the list. Clearly HIPAA can't completely ban non-doctors from seeing medical data or the entire medical sector grinds to a halt, right?
With regards to support agents, some people at the company are approved for access and some are not. The system enforces access rights, naturally.
I'm glad to see this has come so far ahead. My offer to contribute to a kickstarter still stands !
I'd love to see a service that takes in a user's mangled spreadsheet and some regex validation for each column and spits back perfect JSON-formatted data, walking the user through corrections along the way (ie: intelligently guessing column mappings, highlighting malformatted cells, column joins, down/up/title casing, and string substitutions). Something that could be integrated in a line or two of javascript would be fantastically valuable (especially if that "$100,000 in engineering time" heuristic is accurate).
Would I have paid, oh, $500 a month for this? Heck yes. I would have paid it for it on day #1 and continued paying for it for each of the last 4 years, and it would still be cheap at the price.
I really want this to exist, though. Maybe it could be open sourced and survive on your proposed enterprise licenses. Hmm...
I've thought about making an updated version, maybe as a hosted service, if I can come up with something different enough so my old employer can't claim ownership. I did a little test marketing a while back and it didn't go that well, but maybe it just needs more experimentation.
Currently, it's sitting on my local machine collecting bitrot. Thinking about open sourcing it..