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

31–40 of 66 posts

Re: Excel and SQL

#31

Post by resu »

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…

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.

Re: Excel and SQL

#32

Post by Glyptodon »

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.

Re: Excel and SQL

#33

Post by mattmanser »

jiggy2011 wrote: post #21
slg wrote: post #20

Earlier quoted context omitted.

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

Once you are going down the road of actually writing code you may as well just take the plunge and use a nicer language than VBA. VBA isn't really going to be any easier unless you already have visual basic experience in some capacity. If you use a "proper" database you're also going to get advantages in robustness and ability to easily deploy over a network. My last memories of using access for anything (admittedly…

It also doesn't do some extremely basic things properly, e.g. foreign keys. Basically Excel -> Access is easy. Excel -> SQL is easy for a proper programmer. But Excel -> Access -> SQL is a complete nightmare.

In the end Excel -> Access is a short-term cheap, long-term very expensive decision. It means you invest a lot of money and effort in a tool that can't do databases properly and can't do forms properly and when you want to move to something that can do either properly you have to do both completely over again without any reusable code as no-one uses anything vaguely resembling VBA any more and Access as a DB that positively encourages the inexperienced to do a lot of things wrong.

I am sometimes perplexed why MS hasn't released VBA# yet. It's like there's some petty war that has been going on for the last 7 or 8 years between the office team and the .Net language team.

Re: Excel and SQL

#34

Post by n00b101 »

rebelde wrote: post #8

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

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

Re: Excel and SQL

#35

Post by amitparikh »

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.

Re: Excel and SQL

#37

Post by kyllo »

redman25 wrote: post #26
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…

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.

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.

Re: Excel and SQL

#38

Post by jpitz »

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…

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.

Re: Excel and SQL

#39

Post by chaz »

dragonwriter wrote: post #25
rz2k wrote: post #19

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

> 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 connected. It's the same reason why someone might use sqlite3 in a desktop app today.

I also worked in an office full of scientists that were savvy enough with Excel but absolutely not programmers. One of them threw together a Access project to track a bunch of internal data about test results with a functional, but non-fancy forms for data entry. It kept the data clean and portable, and dumped the data out into Excel as needed. If we really needed it, we could have gotten a database installed at some expense and gotten our outsourced IT manager to back it up. But why go through the hassle?

Re: Excel and SQL

#40

Post by jacques_chester »

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…

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.

Post reply on HN