1

Is there any software that combines spreadsheet and database engine functionality?

I would like to be able to paste two tables (for example results of SQL queries, with 20+ different columns each) and write a simple sql-like query to join the two tables and generate a third one. Or conditionally replace certain values, easily split string columns into two, etc.

I currently do this in Sql, for every little task like above I have to define temporary tables and keep track of all subqueries, which is a bit time consuming (again 20+ columns).

I've heard Power Query can help with this, but cannot use excel for various reasons, for example data formatting is still required - pasted data is usually incorrectly parsed, (big numbers are converted to exponential form, dates parsed as floats), and many more.

I am considering writing a script which would convert tables (csv's?) to an Sql table variable declaration and population script, but if there's an existing solution, I'd rather use that.

I would prefer free software, both Windows and Linux options would work.
However if you know of any software that can do this - please share!

  • 1
    regarding joining tables: select * from table1,table2 where table1.column=table2.column. This should output all fields without temporary tables. What determines how you are splitting your columns? Is it delimiter based, position based, or something else? You can also do functions and stored procedures. See also: stackoverflow.com/questions/2696884/… – cybernard Nov 4 '17 at 2:35
  • @cybernard thanks for that link, it's useful. I know basic sql, and used splitting just as an example. I have to work with existing queries (long and complex), and joining these would require more effort than doing it in excel; however the excel is not particularly convenient (hence my question). – user5226582 Nov 13 '17 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.