Excel and SQL
datanitro.com
datanitro.com
But most businesses won't realize that/be too cheap to spend the money to save much more money. Although it's much better these days than it was 5 years ago.
Though I know a lot of MI people where most of their job could probably be automated.
I'm surprised that Microsoft has stopped including it in the more basic versions of Office, since it encourages people to seek better solutions who otherwise might stay completely in the Microsoft ecosystem and eventually move on to MS SQL Server.
Right, but that's an artifact of broken bureaucracy -- its not that Access is the right tool for the job, its that its the best tool left when bureaucratic controls are misapplied to prevent the use of the best tool for the job.
I also worked in an office full of scientists that were savvy enough with Excel but absolutely not programmers. One of them threw together a Access project to track a bunch of internal data about test results with a functional, but non-fancy forms for data entry. It kept the data clean and portable, and dumped the data out into Excel as needed. If we really needed it, we could have gotten a database installed at some expense and gotten our outsourced IT manager to back it up. But why go through the hassle?
Neither am I (though I tend to view the idea that it is the right tool for any job with skepticism); my point above was that the particular scenario pointed out in the post I was responding to indicated that it was the "right" tool, insofar as it was, because of bureaucratic barriers to selecting certain other tools rather than purely technical suitability.
VBA isn't really going to be any easier unless you already have visual basic experience in some capacity.
If you use a "proper" database you're also going to get advantages in robustness and ability to easily deploy over a network. My last memories of using access for anything (admittedly about 10 years ago) was that it was prone to performance issues and data loss.
In the end Excel -> Access is a short-term cheap, long-term very expensive decision. It means you invest a lot of money and effort in a tool that can't do databases properly and can't do forms properly and when you want to move to something that can do either properly you have to do both completely over again without any reusable code as no-one uses anything vaguely resembling VBA any more and Access as a DB that positively encourages the inexperienced to do a lot of things wrong.
I am sometimes perplexed why MS hasn't released VBA# yet. It's like there's some petty war that has been going on for the last 7 or 8 years between the office team and the .Net language team.
I hear this all the time, I wonder if you could expand on your reasons for this belief. Is Access always the wrong tool for any job?
I know of a few smallish (5 to 25 or so employees) that have been running their companies for well over a decade on quite large and complex custom written Access applications that were developed for far, far less than it would have cost to have it done "properly".
On the other hand, you can install Postgres under Windows and then build an Access frontend over ODBC. This gets you the best of both worlds, IMO, at the cost of running a kind of odd stack. Cheaper than SQL Server though, and I like the maintenance story better.
TBH the last time I considered a solution like this I was consulting, so it was a few years ago, and all my clients either didn't sign up at all or went for a custom web solution instead, so I don't have a lot of war stories about this platform. I do think it would work though.
Bad devs write bad code. Beginner bad devs tend to write their bad code in Access (or Excel). It gets the job done --- until it doesn't.
1. It's there.
Most large organisations stump up for Office Pro, and Office Pro includes Access. The corporate policies prevent you from installing the Real Database of your choice -- you can only use what's already installed. Happily, that includes Access.
2. It's upgradeable to a Real Database.
Microsoft make transforming Access into a true multi-user SQL database fairly straightforward: install SQL Server and run the upgrade Wizard.
If SQL Server is not your personal favourite Real Database, then with a bit more work you can get Access to talk to something else via ODBC. Not as seamless, but still a clear upgrade pathway.
One of the projects that made me realise I wanted to be a developer and not a lawyer (long story) was an Access database I wrote for my part-time job. An errors-tracking system. I calculated that it saved the company 35 hours of manager time per month.
What did they have before that? A physical book, typed into an Excel spreadsheet once per month.
Access is a tool with unique bureaucracy-dodging properties. It's important not to discount those.
TL;DR: Access is a great place to get started.
If you have had traders ask you for 'some Excel program that has live streaming price updates along with live pricing model params from our internal database' or some crap like that, believe me, this would definitely be worth the cost over using/maintaining spaghetti code VBA.
I can go to the Data tab, click on the ribbon item "From other sources:From SQL server" or "From other sources:From Microsoft Query" in far less time than it takes me to read this blog post.
Am I missing something? (other the possibility for SQL injection attacks by users of that spreadsheet)
Whilst this is a blog post by the party who have created the library, I also disagree with some of their assertions.
Hosting it in a sharepoint type thing, with track changes on, multiple users work quite well indeed. Not to mention that if someone is doing modifies or deletes, I'd much, much rather have that kind of history (hell even git/svn) than having a database without an audit setup. Given the amount of work involved in setting up an audit system, merging it into the excel UI they've just created, I really can't see the point he is making, or where he is coming from.
In fact I wouldn't really suggest people moved away from Excel for the volume of data he speaks of ether, it is very easy to backup (host on sharepoint or similar) incredibly easy to share with the people work on it.
What I would say for it being time to move is when you have a relationship then its damn well time to move.
It adds SQL Server functionality to Excel, speeding up large queries, adding SQL-like query functionality and greatly extending the limits on data, such as the approx one million row max.
Oops, bad example. Never store money using a floating-point data type! SQLite has a NUMERIC type that preserves the exact value of your decimal amount.
As an experiment, I wrote a quick ruby script called csv2sqlite which parses one more CSV files (and their headers), and automatically populates an SQLite database based on the CSV.
If you have a CSV and want to easily know how many records it has, or to filter or join these records, it can be just a matter of running something like following:
ruby ~/csv2sqlite/csv2sqlite.rb baby-names-10.csv --output babynames.db
sqlite3 babynames.db "SELECT * FROM baby_names_10 WHERE percent > .05;"
Hope it helps you!
'CSV' and 'easy to parse' do not go together that well http://en.wikipedia.org/wiki/Comma-separated_values#Toward_s... also is instructive:
Nevertheless, RFC 4180 is an effort to formalize CSV. It defines the MIME type "text/csv", and CSV files that follow its rules should be very widely portable.
[...]
Each record "should" contain the same number of comma-separated fields.
[...]
Fields containing a line-break, double-quote, and/or commas should be quoted.
[...]
The format is simple and can be processed by most programs that claim to read CSV files.
$ irb
>> require 'csv'
# => true
>> CSV.parse(file)
In this case you have someone that understands SQL and Python, but still wants to use a spreadsheet. Applying the predicates at the start, this person should dump Excel entirely - they can get much better integrity using SQL and Python alone.
This could we useful to integrate Excel sheets & data with other systems without too much manual intervention.
Being able to interact with those spreadsheets in a sensible language with a front-end that doesn't suck would be huge.
I actually wrote a wrapper for the ADO/Jet DB engine in VBA which does exactly this [1]. However, doing it all in python would be a heck of a lot easier.
1) {=DB_QUERY("/path/to/.csv|.xls|.db","SELECT * FROM....")}
I'll definitely give this plugin a try.
We use handsontable (like Google Docs spreadsheet) with a MySQL back end. Users just need a web browser to edit data. I wouldn't call it a replacement for Excel, just a way for non-technical people to edit info in a database.
Anyone know of a Mac OS X friendly alternative?
[1] : http://www.rstudio.com/
Is it just to get them on an open source solution or is it to replicate the functionality of Excel in something you've written/control?
Business users already know how use Excel. Spreadsheets were the original killer app. They have transformed business and I'm not convinced we've moved beyond their usefulness. It's the same argument as trying to reinvent SQL syntax for the NoSQL flavor of the month. Why try to change what your users are already proficient at? Why not instead try to feed data to that software in a more seamless way?
I may have gone ot from what you meant, but I'm interested in what you meant by the "wean Excel junkies off Excel" comment.
Belittling Excel is an effective way to burnish one's programming credentials.
I know many languages (Flex, Html, PHP, JS, C# etc). Excel and VBA have their place, especially for very rapid app development.
Web apps are perfect for trapping data. However, output is best handled in Excel. The first thing people ask when getting a report is "How can I get this into Excel?". People like to play with their numbers.
90% of the time, that's Excel; they're looking at reports that can easily be pivot tabled or VLOOKUP'd to get what they need, or use the strengths of the UI and auto-updating to get the formulas they want working in an easily tweakable fashion. For those cases, things like R, SQL or Python can be overkill, especially if you spend more time preparing the data format than it would take to use Excel, let alone analyze the data.
However, 10% of the time, they're pushing Excel beyond the limits of what it can handle and wasting time as a result, spending an hour doing massive VLOOKUPs between two huge spreadsheets looking for an answer that SQL can answer in a heartbeat. Those are the use cases I want to solve for; where the advantages of statistical software, scripting languages and relational databases overtake the powerful and convenient simplicity of Excel.
Some API wrappers I've used: a slightly outdated official wrapper gdata-python-client [1] and a bit more convenient gspread [2].
And works with Python.
Once you have a lot of data, it really depends on your specific situation. If you don't have any preferences, you can try something new - MongoDB is interesting.
Out of all the nosql options, why pick the one with the most broken default configuration? People complain that MongoDB is has a bad rep when used for things its not meant to, and then its constantly offered as solution.