News for hackers. Stuff that matters.
Re:You Suck at Excel (2015) [video] by Nullabillity on Friday December 20, 2019 @05:55PM (#21848106)
Please, just learn SQL instead of creating more Excel messes...
Re:You Suck at Excel (2015) [video] by enitihas on Friday December 20, 2019 @06:16PM (#21848241)
Excel allows you to do interactive analysis though, which SQL simply comes no where near close. For example, after creating an Excel table, you add a filter, see the changed data. You can Ctrl-Z for undo. Add another filter and see changed data instantaneously, undo/redo, sort on many columns, create derived columns and sort on those, and all of this interactively.

Also, excel supports far more mathematical functions.

Re:You Suck at Excel (2015) [video] by nwallin on Friday December 20, 2019 @10:10PM (#21849347)
No offense, but you sounds like someone who doesn't 'get' SQL.

I'm reasonable at Excel. I've been using it in anger for close to 20 years. My most serious dressing down I've every received at work was related to a column of incorrect data in Excel. (I built a spreadsheet based on incorrect data. Management expected me to build correct spreadsheets from incorrect data.) I'm reasonable at SQL. I've been using it casually for 10 years. My cutoff at this point is that I'll use Excel if I expect my analysis to take less than five minutes. I'll use pandas or SQL for anything more complicated than that, because it's that much more powerful.

Excel stops scaling at about a million rows. SQL stops scaling when your indices no longer fit in RAM.

Re:You Suck at Excel (2015) [video] by enitihas on Saturday December 21, 2019 @02:48AM (#21850221)
I have written a lot of SQL over the years, and consider myself to be reasonably well versed with SQL and relational databases. SQL doesn't allow you to act on your data while seeing it. The interaction modes are totally different.

In SQL: Type query -> See some results -> Repeat

Excel: Everything in front of your eyes. You are manipulating live data. That is not something you do in SQL. (SQL doesn't mean much though, as it is a standard and not a tool. Maybe you had some specific SQL based tooling in mind)

> Excel stops scaling at about a million rows.

A large amount of real world data has less than a million rows. Every tool will stop at some point. At that point the benefits of Excel fade away, even if it could handle the data, as 1M rows are too large for our brain to make a sense of live.

>My cutoff at this point is that I'll use Excel if I expect my analysis to take less than five minutes

A lot of real world analysis takes 5 minutes.

Of course, Excel is not perfect, and I too will never use Excel for anything that has to be done repeatedly (pandas or similar solutions are perfect here), but for quick and dirty data exploration, Excel is very good.

Re:You Suck at Excel (2015) [video] by liamwestray on Friday December 20, 2019 @10:56PM (#21849498)
Excel is hands down the best financial analysis and modeling tool ever made. SQL is not really comparable here.

Any attempt to use it as a database or for data analysis should be met with extreme caution. As soon as someone suggests a VB macro for anything they should be shot. Excel is not the right tool for those kids of jobs.

Python and R have just destroyed excel for data analysis and statistics (and most visualizations now) and are free. You can’t go back once getting used to that flexibility.

Jupyter Notebooks reproducibility and traceability put the nail in the coffin for excel as a data analysis tool.

Re:You Suck at Excel (2015) [video] by Nullabillity on Friday December 20, 2019 @06:23PM (#21848274)
> For example, after creating an Excel table, you add a filter, see the changed data.

Add a WHERE.

> You can Ctrl-Z for undo.

Drop the WHERE. Hell, ctrl+z will work fine here too!

> sort on many columns

ORDER BY supports multiple columns too.

> create derived columns and sort on those

You can have arbitrary expressions in your SELECT clause.

> and all of this interactively.

Destructively*.

And SQL makes it clear exactly what you've done to the source data in order to get there.

> Also, excel supports far more mathematical functions.

Surely that's what extensions are for!

Re:You Suck at Excel (2015) [video] by ALittleLight on Friday December 20, 2019 @06:41PM (#21848381)
Earlier today I was looking at data from two different sources about the same thing. I wanted to see how similar the reports were. I grepped my logs, got columns of data, copied and pasted into Excel, created a line chart, copied and pasted the line chart into an email to a colleague. How would SQL let me do the same thing? With Excel this was probably less than a minute end to end. Would SQL be as fast? Keeping in mind that I wouldn't have a database for any of the data I was looking at.
Re:You Suck at Excel (2015) [video] by hoistbypetard on Friday December 20, 2019 @07:16PM (#21848598)
Concur. For whacking out a quick analysis like that, it's hard to beat a spreadsheet. (I do like Excel, but no longer think it's all that far ahead of LibreOffice for most things I tend to use it for.)

It's also good for creating what amounts to a quick interactive form.

But I've lost count of how many times I've run across a spreadsheet being misused as a shitty, ad-hoc database where it'd be less work (and less error-prone) to just use sqlite (or even postgres, honestly), so I have some empathy for GP's perspective.

Re:You Suck at Excel (2015) [video] by rabidrat on Friday December 20, 2019 @07:06PM (#21848523)
In SQL, how can I search all columns which contain some text?
Re:You Suck at Excel (2015) [video] by Nullabillity on Friday December 20, 2019 @07:52PM (#21848770)
If you don't have any meaningful column structure then you won't be able to do any useful analysis anyway.
Re:You Suck at Excel (2015) [video] by DonHopkins on Friday December 20, 2019 @08:02PM (#21848821)
Why "learn SQL instead" and not "learn both"?

SQL is a language. Excel (and Google Sheets) is a tool (that also supports SQL, the language). Why wouldn't you want a sleek Excel-like user interface to SQL data and queries?

https://www.lifewire.com/excel-front-end-to-sql-server-24953...

https://www.benlcollins.com/spreadsheets/google-sheets-query...

And no I don't mean like the steaming piece of shit called MySQLWorkbench (or PHPMyAdmin for that matter).

https://devrant.com/rants/1342177/mysql-workbench-user-frien...

http://www.colinpistell.com/tutorial/mysql-workbench/

Re:You Suck at Excel (2015) [video] by Nullabillity on Friday December 20, 2019 @08:28PM (#21848949)
Because Excel's flexibility encourages bad behaviour that will come back to bite you.

Input data should be separate from your calculations, and you should never deal with single cells in your results (fix your formula or input data instead).

Yes, Excel actually has a mode that supports that workflow: the Unified Get & Transform Experience[0] (just rolls off the tongue, right?). And it's basically drag-and-drop Pandas.

But I've only ever seen one person use it (after repeatedly bugging him about how we will need to repeat this analysis and how surely there must be a better way). And even then it's very easy to screw up and introduce bugs whenever the input data is replaced. Wanted to make a graph and only included the populated data region? Referred to the aggregation row somewhere in some stats sheet? Too bad!

Also, Colin's post ends up recommending SQL Workbench/J, which seems to be much closer to MySQL Workbench than Excel.

[0]: https://support.office.com/en-us/article/unified-get-transfo...

Re:You Suck at Excel (2015) [video] by Exuma on Friday December 20, 2019 @09:31PM (#21849211)
Ah yes, the old "please learn this entirely different thing even though it completely doent fit your use case, just because I'm good at it and makes me feel better about myself to make blanket suggestions that validate my skill"
Re:You Suck at Excel (2015) [video] by mobjack on Friday December 20, 2019 @08:38PM (#21848985)
Try copying a pivot table in SQL.

A lot of simple functions in Excel are incredibly complex in SQL. You might technically can pull most off put then you end up with a lot of ugly SQL.

Also each time you make a change with SQL, you have to query the database again which can be slow and inefficient.

I use both tools together, exporting SQL results and opening in Excel for further clean up. That way you can take advantage of the strengths of each one.

Re:You Suck at Excel (2015) [video] by kristianp on Saturday December 21, 2019 @11:15PM (#21855148)
A Pivot table can be replicated using GROUP BY in sql. Not too hard, but not beginner-level sql either. Not just a few clicks either, it takes some code.

https://modern-sql.com/use-case/pivot

Re:You Suck at Excel (2015) [video] by fencepost on Friday December 20, 2019 @08:44PM (#21849018)
Please, just learn SQL instead of creating more Excel messes...

Absolutely, it's amazing the things you can do in Access!

Re:You Suck at Excel (2015) [video] by slowhand09 on Saturday December 21, 2019 @11:56AM (#21852107)
As a DB engineer/developer/architect/modeler... SQL is great. But Excel is great also. Its awesome to show a usr how to pull what they need from the database to populate a spreadsheet, and vice versa. We have multiple tools for a reason. And the Swiss Army knife is a perfect example of a non-optimal implementation.
Re:You Suck at Excel (2015) [video] by dbeley on Friday December 20, 2019 @06:31PM (#21848320)
What's the quickest way to query a simple CSV file with SQL ? Interested in any gui or cli tools.
Re:You Suck at Excel (2015) [video] by hoistbypetard on Friday December 20, 2019 @07:24PM (#21848632)
If you're accustomed to windows/office you can just set up a CSV as an ODBC data source and use access.

Otherwise [csvkit](https://csvkit.readthedocs.io/en/latest/) gives a pretty good command line interface to make sql queries against a CSV or dump the CSV into a database so you can use any standard database tool to query.

Re:You Suck at Excel (2015) [video] by slowhand09 on Saturday December 21, 2019 @12:08PM (#21852185)
In Oracle I 'd just set up an External Table. Trivial to do.
Re:You Suck at Excel (2015) [video] by cgarvis on Friday December 20, 2019 @06:43PM (#21848390)
not sql, but xsv is awesome: https://github.com/BurntSushi/xsv
Re:You Suck at Excel (2015) [video] by perl4ever on Friday December 20, 2019 @07:13PM (#21848575)
SQL is how you create Excel messes, at least in my world.