Excel and SQL

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.

Is Python/DataNitro really "One of the easiest ways to connect Excel to a database"?<p>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.<p>Am I missing something? (other the possibility for SQL injection attacks by users of that spreadsheet)

Excel supports querying databases out of the box - Data &#62; Get External Data, no coding besides writing the query required.

Pre-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]"

Its 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.<p>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.<p><a href="http://www.microsoft.com/en-us/bi/PowerPivot.aspx" rel="nofollow">http://www.microsoft.com/en-us/bi/PowerPivot.aspx</a>

&#62; <i>ticker text, position integer, purchase_price real</i><p>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.

Excel 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).<p>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.<p>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:<p>ruby ~/csv2sqlite/csv2sqlite.rb baby-names-10.csv --output babynames.db<p>sqlite3 babynames.db "SELECT * FROM baby_names_10 WHERE percent &#62; .05;"<p>Hope it helps you!

Not to be too obvious, but what about plain old Microsoft Query? I pull data into Excel from time to time with that.

Not 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.<p>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.

I don't understand the point about backups. I would have thought a database was more complicated to back up than an excel file.

I'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.

As 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.<p>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.<p>1) {=DB_QUERY("/path/to/.csv|.xls|.db","SELECT * FROM....")}

I 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.<p>I'll definitely give this plugin a try.

"DataNitro is embedded in Excel." So users need to install something? Yuck. That doesn't work for us.<p>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.

Oof, DataNitro being Windows-only is a major bummer. Something like this could be hugely useful for slightly less technical analysts on my team.<p>Anyone know of a Mac OS X friendly alternative?

Which database is best for stuff like this?<p>And works with Python.