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!

Improve this question
2
  • 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, 2017 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, 2017 at 7:49

1 Answer 1

Reset to default
2

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.