Excel and SQL

https://datanitro.com/blog/2013/5/23/Excel_and_sql/

by karamazov • 13 years ago

93 65 13 years ago

datanitro.com

magicg• 13 years ago

Which database is best for stuff like this?

And works with Python.

IanChiles2• 13 years ago

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)

kevinthew• 13 years ago

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).

trapezoid• 13 years ago

Like 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• 13 years ago

MongoDB/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.

yahelc4• 13 years ago

Oof, 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• 13 years ago

Yes, DataNitro only works with Windows versions of MS Excel. Maybe a VM/Parallels with Windows + Excel would work for you?

testbro• 13 years ago

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/

yahelc4• 13 years ago

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.

quiesbill• 13 years ago

It's not an exact alternative but I'm using Google Spreadsheets with Python and SQLite. It's webapp, it's in the cloud and always synced among the team. There are some API performance issues since you're working with a remote server but it's OK for me.

Some API wrappers I've used: a slightly outdated official wrapper gdata-python-client [1] and a bit more convenient gspread [2].

[1] https://code.google.com/p/gdata-python-client/

[2] https://github.com/burnash/gspread/

yahelc4• 13 years ago

That's really interesting. Is any of your GDocs/Python/SQLite setup publicly available?

rebelde• 13 years 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• 13 years ago

Just curious, why does installing software on end-user devices not work for you?

slg2• 13 years ago

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.

mattmanser2• 13 years ago

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.

rz2k• 13 years 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.

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.

slg2• 13 years ago

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.

mistermann• 13 years ago

> 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

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".

jacques_chester• 13 years ago

Speaking as a Real Database(tm) bigot, Access has two key advantages over Real Databases.

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.

Spooky23• 13 years ago

If you have a single user problem, Access is fine.

resu• 13 years ago

Looks like this is aimed at development teams that have traders/Gov't Excel monkeys as their customer. This is really the less evil of the two options (other being VBA), and when dealing with those types of customers in organizations where developers typically are the least valued cost center... well, it's not a bad compromise.

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.

cafard• 13 years ago

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

reeses• 13 years ago

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

BrianEatWorld• 13 years ago

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.

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

dergachev• 13 years ago

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).

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• 13 years ago

Excel'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• 13 years ago

Excel 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.

Someone2• 13 years 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_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• 13 years 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.

Glyptodon• 13 years ago

I've had pretty straightforward success with Python's built in CSV parsing. Not sure what you mean by "Linux tools aren't great for working with CSVs" exactly. I guess maybe Python's not a "Linux tool," but it's sure easy to use for quick stuff from the shell.

mtdewcmu• 13 years ago

I imagine he means things like awk, sed, sort, etc., that are standard unix tools. The newer versions of gawk have extensions that are designed to help handle CSV files, but, unfortunately, Arnold Robbins opted to make it "some assembly required." Given the fiddlyness of parsing CSV files and their ubiquity, hard-coding a CSV mode probably would have been a better solution.

sokoloff2• 13 years ago

Is 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)

Someone2• 13 years ago

Also, 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 and http://sqlserverpedia.com/wiki/Access_Excel_Sheets_as_a_Tabl...

WayneDB• 13 years ago

I 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/

gizzlon2• 13 years ago

Can you use it with arbitrary sql (non MS) servers on other hosts?

sokoloff2• 13 years ago

Yes, the latter is an ODBC option, which gives you arbitrary connectivity.

danbruc• 13 years ago

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

TheAnimus• 13 years ago

Yes, 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• 13 years ago

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.

andrewflnr• 13 years ago

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

jwilliams• 13 years ago

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.

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.

gizzlon2• 13 years ago

The 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.

regularfry2• 13 years ago

There 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• 13 years ago

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.

I'll definitely give this plugin a try.

kijin• 13 years 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• 13 years ago

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.

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• 13 years ago

Out of curiosity, what do you hate so much about VBA?

Excel and SQL

Related Stories