Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Excel and SQL

datanitro.com

Post reply on HN
Print view View original

61–66 of 66 posts

Re: Excel and SQL

#61

Post by regularfry »

jwilliams wrote: post #51

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.

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.

Re: Excel and SQL

#62

Post by quiesbill »

yahelc wrote: post #5

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?

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/

Re: Excel and SQL

#63

Post by fusiongyro »

jpitz wrote: post #38
fusiongyro wrote: post #30

Earlier quoted context omitted.

There are hard limits to Access's internal engine (JET, IIRC) which you're likely to run into as time goes on. The unfortunate thing is that there isn't anything you can do to avoid the limits or recover once you've hit them. 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. Che…

JET's days are long gone. The SQL Server Desktop engine is the underpinning for Access now. It essentially _is_ SQL Server. The biggest limitation? The concurrent transaction count is limited to 5. There are others, but it is a far, far cry from that prone to corrupt, slow beast JET.

It's amazing how much things change when you're not paying attention. Thanks for explaining this.

Re: Excel and SQL

#64

Post by sokoloff »

gizzlon wrote: post #55
sokoloff wrote: post #23

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)

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

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

Re: Excel and SQL

#65

Post by yahelc »

quiesbill wrote: post #62
yahelc wrote: post #5

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?

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-p…

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

Re: Excel and SQL

#66

Post by panderon »

jpitz wrote: post #38
fusiongyro wrote: post #30

Earlier quoted context omitted.

There are hard limits to Access's internal engine (JET, IIRC) which you're likely to run into as time goes on. The unfortunate thing is that there isn't anything you can do to avoid the limits or recover once you've hit them. 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. Che…

JET's days are long gone. The SQL Server Desktop engine is the underpinning for Access now. It essentially _is_ SQL Server. The biggest limitation? The concurrent transaction count is limited to 5. There are others, but it is a far, far cry from that prone to corrupt, slow beast JET.

Point of clarification, concurrent transaction count is limited to 255. http://office.microsoft.com/en-us/access-help/access-2010-sp...
Post reply on HN