I might be laughed off of HN for even suggesting this, but why not use Access and VBA for something like this? Most computers that have Excel installed will already come with a copy of Access. Plus Microsoft built the three to work together pretty seamlessly. It might not provide all the same advantages of Python and other database software, but it is easier to pickup and is a huge improvement over the old Excel as a database solution.
Excel and SQL
11–20 of 66 posts
Re: Excel and SQL
#12Oof, DataNitro being Windows-only is a major bummer. Something like this could be hugely useful for slightly less technical analysts on my team. Anyone know of a Mac OS X friendly alternative?
As an alternative RStudio [1] offers a decent GUI on top of R, which can load data via SQL. It doesn't give you WYSIWYG editing of data, but you can manipulate data frames as if they were SQL with the sqldf package [2]. The learning curve is very steep though so it's not a perfect alternative. [1] : http://www.rstudio.com/ [2] : https://code.google.com/p/sqldf/
Thanks. I'm more comfortable with R, but I'm trying to wean Excel junkies off of Excel, and something like this (Python and SQL within Excel) seems like a good gateway drug. Sadly, RStudio is a bit too big of a leap for that.
Re: Excel and SQL
#13Earlier quoted context omitted.
Python has bindings for pretty much everything, so I'd imagine that the database you're most familiar with, or that there is the most information on (most likely MySQL, Postgres, or SQLite)
I'd suggest SQLite unless you had a ton of users interacting all at once (if it's something involving editing excel spreadsheets, I doubt you'd have a problem with this).
Slightly off topic, but has anyone built an Excel-esque program on top of SQL? I feel like something of the sort could really be an improvement, as now it'd be easy for end users (who normally use excel) and developers to collaborate on the same data.
Re: Excel and SQL
#14Not to be too obvious, but what about plain old Microsoft Query? I pull data into Excel from time to time with that.
Re: Excel and SQL
#15I might be laughed off of HN for even suggesting this, but why not use Access and VBA for something like this? Most computers that have Excel installed will already come with a copy of Access. Plus Microsoft built the three to work together pretty seamlessly. It might not provide all the same advantages of Python and other database software, but it is easier to pickup and is a huge improvement over the old Excel as a…
Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers or get some proper programming training rather than a SAMS teach yourself VBA in 21 days book (OT, do they still have them?).
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.
Re: Excel and SQL
#16Pre-SQLite, I used the Excel OLEDB provider for a simple data store. Basically, each sheet is a table, and once you have a connection, you can run SQL against the workbook. "SELECT * FROM [People]" works mostly as expected, and ranges can be specified like "SELECT [People$A] FROM [People$A2:Y314]"
Re: Excel and SQL
#17Earlier quoted context omitted.
As an alternative RStudio [1] offers a decent GUI on top of R, which can load data via SQL. It doesn't give you WYSIWYG editing of data, but you can manipulate data frames as if they were SQL with the sqldf package [2]. The learning curve is very steep though so it's not a perfect alternative. [1] : http://www.rstudio.com/ [2] : https://code.google.com/p/sqldf/
Thanks. I'm more comfortable with R, but I'm trying to wean Excel junkies off of Excel, and something like this (Python and SQL within Excel) seems like a good gateway drug. Sadly, RStudio is a bit too big of a leap for that.
What is the goal in trying to wean people off Excel?
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.
Re: Excel and SQL
#18Its no substitute for a real db but, if you are stuck in Excel, I find that Microsoft's Power Pivot is a good transitioning step. It comes free with most licenses and is pretty well supported.
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.
Re: Excel and SQL
#19I might be laughed off of HN for even suggesting this, but why not use Access and VBA for something like this? Most computers that have Excel installed will already come with a copy of Access. Plus Microsoft built the three to work together pretty seamlessly. It might not provide all the same advantages of Python and other database software, but it is easier to pickup and is a huge improvement over the old Excel as a…
Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers or get some proper programming training rather than a SAMS teach yourself VBA in 21 days book (OT, do they still have them?). 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…
Access can be a convenient way to distribute structured data that is relatively static to other people, such as colleagues in another office, when it is not worth the effort to get approval for and implement a longer term solution.
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.
Re: Excel and SQL
#20I might be laughed off of HN for even suggesting this, but why not use Access and VBA for something like this? Most computers that have Excel installed will already come with a copy of Access. Plus Microsoft built the three to work together pretty seamlessly. It might not provide all the same advantages of Python and other database software, but it is easier to pickup and is a huge improvement over the old Excel as a…
Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers or get some proper programming training rather than a SAMS teach yourself VBA in 21 days book (OT, do they still have them?). 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…
I agree with most of what you are saying, but I just don't see how this tool solves that problem. Both these options seem to be middle steps between the layman and full fledged software development. The Access/VBA route is slightly closer to the layman side while the DataNitro route is closer to the programming end (if you are the type of person who thinks Excel is a great front end for your database interactions, you probably aren't completely fluent with SQL). I don't see what advantages DataNitro provides that are worth jumping through the extra hoops.