Database and Spreadsheet combination

Question

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!

Answer 1

Indeed, combining worksheets and databases, simple scripts are more suitable than Power Query. But easy-to-learn and easy-to-use processing scripts are rare. You can try esProc. I have a small example of this, and you can refer to:

A1=file("./employee.xlsx").xlsimport@t()
A2=DB.query("select ORDERID,SELLERID,AMOUNT from sales")
> A2.switch(SELLERID,A1:EID)
A4=A2.group(SELLERID.EID;sum(AMOUNT):Total,SELLERID:Seller)
//Female sales under 40 years old
A5=A4.select(Seller.GENDER == "F"&&age(date(Seller.BIRTHDAY))<=40).new(Seller.NAME:Name,Total)
A6=A4.group(Seller.STATE)
A7=A6.(~.top(-3;Total))
//Top three sales in each state
A8=A7.news(~;Seller.STATE:State,Seller.NAME,Total)
return A5,A8