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

41–50 of 66 posts

Re: Excel and SQL

#41

Post by dragonwriter »

chaz wrote: post #39
dragonwriter wrote: post #25

Earlier quoted context omitted.

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

I'm not convinced that Access is automatically the wrong tool for every job. I worked with a team that had remote salespeople that were all over the place, with laptops and a custom VB custom configuration tool that would allow them to assemble a quote amongst thousands of parts. I can't imagine any proper grown up database would have been the appropriate solution for this kind of work, especially since they're not c…

> I'm not convinced that Access is automatically the wrong tool for every job

Neither am I (though I tend to view the idea that it is the right tool for any job with skepticism); my point above was that the particular scenario pointed out in the post I was responding to indicated that it was the "right" tool, insofar as it was, because of bureaucratic barriers to selecting certain other tools rather than purely technical suitability.

Re: Excel and SQL

#42

Post by WayneDB »

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)

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/

Re: Excel and SQL

#43

Post by Spooky23 »

mattmanser wrote: post #15
slg wrote: post #11

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…

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…

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

Re: Excel and SQL

#44

Post by untog »

jacques_chester wrote: post #40
mattmanser wrote: post #15

Earlier quoted context omitted.

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…

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

Seconded- Access was my gateway drug into development, too. Well, not quite- I knew I wanted to do it and had played around with PHP in my spare time, but after graduating college I found myself in a crappy administration job. They had a crappy Excel-based tracking system, I replaced it with Access. Then that grew into a SQL Server-backed VB.NET application. Then I left, went into another .NET job developing C#, then journeyed into the world of Node, Python and Ruby.

TL;DR: Access is a great place to get started.

Re: Excel and SQL

#45

Post by mtdewcmu »

Glyptodon wrote: post #32
dergachev wrote: post #22

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

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.

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.

Re: Excel and SQL

#46

Post by goofygrin »

fusiongyro wrote: post #30
mistermann wrote: post #24

Earlier quoted context omitted.

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

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…

One of my clients has a poorly designed access system (using, now, mssql as the backend). It's not access's fault that the system is shit, it's the developer's fault.

Bad devs write bad code. Beginner bad devs tend to write their bad code in Access (or Excel). It gets the job done --- until it doesn't.

Re: Excel and SQL

#47

Post by moron4hire »

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.

JET isn't all that bad. It can be used to run SQL queries CSV files. As an ETL tool it's pretty flexible.

Re: Excel and SQL

#48

Post by racecar789 »

pfarrell wrote: post #17
yahelc wrote: post #12

Earlier quoted context omitted.

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…

Agreed. Excel bashing often comes from web developers who lack real business experience in a gritty production environment.

Belittling Excel is an effective way to burnish one's programming credentials.

I know many languages (Flex, Html, PHP, JS, C# etc). Excel and VBA have their place, especially for very rapid app development.

Web apps are perfect for trapping data. However, output is best handled in Excel. The first thing people ask when getting a report is "How can I get this into Excel?". People like to play with their numbers.

Re: Excel and SQL

#49

Post by jrarredondo »

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.

Actually, in the latest Office 2012, Access apps are now three tier apps with SQL Server 2012 or SQL Azure backends.

Re: Excel and SQL

#50

Post by yahelc »

pfarrell wrote: post #17
yahelc wrote: post #12

Earlier quoted context omitted.

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…

Really, the goal is to use the right tool for the data analysis they're doing.

90% of the time, that's Excel; they're looking at reports that can easily be pivot tabled or VLOOKUP'd to get what they need, or use the strengths of the UI and auto-updating to get the formulas they want working in an easily tweakable fashion. For those cases, things like R, SQL or Python can be overkill, especially if you spend more time preparing the data format than it would take to use Excel, let alone analyze the data.

However, 10% of the time, they're pushing Excel beyond the limits of what it can handle and wasting time as a result, spending an hour doing massive VLOOKUPs between two huge spreadsheets looking for an answer that SQL can answer in a heartbeat. Those are the use cases I want to solve for; where the advantages of statistical software, scripting languages and relational databases overtake the powerful and convenient simplicity of Excel.

Post reply on HN