7 ms·
Excel and SQL
- magicg 13y agoWhich database is best for stuff like this? And works with Python.
- IanChiles 13y agoPython 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)
- kevinthew 13y agoI'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).
- IanChiles 13y agoSlightly 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.
- trapezoid 13y agoLike the article says, SQLite is a good choice when you're starting out. It's included in Python and is great for the basics. 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.
- tinsel 13y agoMongoDB/NoSQL is not for the type of analysis mentioned in the article and is NOT a replacement for a spreadsheet. 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.
- yahelc 13y agoOof, 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?
- vj44 13y agoYes, DataNitro only works with Windows versions of MS Excel. Maybe a VM/Parallels with Windows + Excel would work for you?
- testbro 13y agoAs 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/ http://www.rstudio.com/ [2] : https://code.google.com/p/sqldf/ https://code.google.com/p/sqldf/
- yahelc 13y agoThanks. 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.
- pfarrell 13y agoWhat 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.
- rebelde 13y ago"DataNitro is embedded in Excel." So users need to install something? Yuck. That doesn't work for us. 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.
- n00b101 13y agoJust curious, why does installing software on end-user devices not work for you?
- slg 13y agoI 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.
- mattmanser 13y agoAccess 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.
- rz2k 13y agoAccess 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.
- dragonwriter 13y ago> 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. 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.
- cafard 13y agoNot to be too obvious, but what about plain old Microsoft Query? I pull data into Excel from time to time with that.
- reeses 13y agoPre-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]"
- BrianEatWorld 13y agoIts 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. http://www.microsoft.com/en-us/bi/PowerPivot.aspx http://www.microsoft.com/en-us/bi/PowerPivot.aspx
- dergachev 13y agoExcel can easily export CSV files, which are easy to parse. However, standard Linux tools aren't great for working with CSVs, especially ones with multi-line content (eg exports from a CMS). 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!
- redman25 13y agoExcel's CSV export has a lot to be desired. From what I've tried, it doesn't export data in Unicode and exports non-standard CSV that many parsers choke on.
- kyllo 13y agoExcel exports CSV in UTF-16 with byte order mark (BOM). Very annoying if you're sending the data to another application that is expecting UTF-8.
- Someone 13y ago"Excel can easily export CSV files, which are easy to parse" 'CSV' and 'easy to parse' do not go together that well http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization 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.
- Groxx 13y ago$ irb >> require 'csv' # => true >> CSV.parse(file) ^ works every time I've tried it. Systems which export mangled data to CSV probably do so elsewhere, so CSV isn't special there, and CSV is really really simple to escape well enough that any decent parser won't have any problems at all.
- sokoloff 13y agoIs Python/DataNitro really "One of the easiest ways to connect Excel to a database"? 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)
- Someone 13y agoAlso, one do it the other way around: use SQL queries to read (and update!) Excel files. For examples, see http://support.microsoft.com/kb/306397 http://support.microsoft.com/kb/306397 and http://sqlserverpedia.com/wiki/Access_Excel_Sheets_as_a_Table_in_SQL_Server http://sqlserverpedia.com/wiki/Access_Excel_Sheets_as_a_Tabl...
- WayneDB 13y agoI think an even easier way is to use .NET/Mono with the open source library called EPPlus which lets you read and write Excel 2007 (Office Open XML/xlsx) files. http://epplus.codeplex.com/ http://epplus.codeplex.com/
- gizzlon 13y agoCan you use it with arbitrary sql (non MS) servers on other hosts?
- sokoloff 13y agoYes, the latter is an ODBC option, which gives you arbitrary connectivity.
- danbruc 13y agoExcel supports querying databases out of the box - Data > Get External Data, no coding besides writing the query required.
- TheAnimus 13y agoYes, and with the new 2013, it has some good OData stuff too. 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.
- amitparikh 13y agoI'm not saying it's the smartest thing to do, but Excel can most certainly handle data on the order of tens of thousands of rows. It's not blazing fast, but Pivoting that much data is not a problem at all.
- andrewflnr 13y agoI don't understand the point about backups. I would have thought a database was more complicated to back up than an excel file.
- jwilliams 13y agoNot sure of the audience for this. If you understand SQL you'd already know most of it... The rest use Excel because that's what they understand. 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.
- gizzlon 13y agoThe person using Excel doesn't have to be the person writing the sql :) This could we useful to integrate Excel sheets & data with other systems without too much manual intervention.
- regularfry 13y agoThere are many, many organisations which use Excel sheets as de facto standard interfaces, or "applications" you have to use, because it's the only common tool the people with the domain knowledge have access to and are trained in, and sharing them is trivial - just email a .xlsx. I've seen government regulators supply template Excel sheets as the mandated standard by which private companies must, legally, submit data. Being able to interact with those spreadsheets in a sensible language with a front-end that doesn't suck would be huge.
- digz 13y agoI have had the unfortunate pleasure of having to integrate SQL and Excel for my start up. A provider's tool that we need only works in Excel, so I have to bring data from SQL into Excel, process it and then put it back into the DB. Then all of our real data processing is built with python. So far I have just used VBA/ODBC to handle everything, but for obvious reasons, I hate it. I'll definitely give this plugin a try.
- kijin 13y ago> ticker text, position integer, purchase_price real 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.
- retube 13y agoAs a heavy user of Excel+VBA I would love to be able to switch to coding in Python. I detest VBA with a passion. Plus the DB stuff is nice. It's much better able to return results as a formula array vs the native MS facilities which populate ranges with static data (and potentially clobber existing content). Plus it also means your data is refreshed with a simple Shift-F9. 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....")}
- bornhuetter 13y agoOut of curiosity, what do you hate so much about VBA?