cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Spark Notebook to import data into Excel

CBull
New Contributor III

Is there a way to create a notebook that will take the SQL that I want to put into the Notebook and populate Excel daily and send it to a particular person?

1 ACCEPTED SOLUTION

Accepted Solutions

merca
Valued Contributor II

You can use python code to send email from notebook with attachment, so if you are comfortable with that... here is stackoverflow post on that

Other alternative is to send result to azure function (http trigger) and use whatever code you want to send email.

You can also use Azure data fatory or logic app or power automate to pick up csv file and add as attachment and send email via office 365.

Excel treats csv's as native format and will render is as table, multi sheet workbook is a different story and that may not be achievable. If this is requrement - direct connection to tables are probably your best bet here.

Also alternative - create a databricks sql dashboard that contains tables, graphs and such, send this on schedule to email - it will produce pdf - but its better than nothing if its just overview the person needs.

View solution in original post

6 REPLIES 6

paultyner
New Contributor II

The best way is...don't. Save Excel files as .csv and do a read.csv. You can get VBA scripts which will unpack all the sheets of an Excel file into separate CSVs.

CBull
New Contributor III

Appreciate you. So, to do that I would need a script to make this happen? I'm new to this so I'm just seeing if there's a way to do this. What I'm looking to do is provide a manager a list of customers etc every day. I thought maybe I could schedule the sql code to run and then send them an excel doc or something.

merca
Valued Contributor II

Do I understand you correctly:

You want to run a notebook or sql query that will generate some data in form of table and you need to "send" somehow this data to someone (or somebody needs this data at some point)?

If this is correct assumption, you have many options to achieve this. But the essential is that you run a job on schedule that prepares this data, either to an csv file in storage or table in databricks metastore.

As I don't know what you are comfortable with I will give the simplest way:

If you store data as scv file in storage - Excel can connect directly to this csv.

If you store the table in databricks metastore - Excel can connect to databricks sql endpoint via Simba ODBC driver and pull table directly to Excel.

If you give me more information - I can give you more options how to resolve this ๐Ÿ˜

CBull
New Contributor III

Thanks for helping me! Yes, I have sql code that I would set up as a job to run.....ideally, I want to send the results that the code produces to someone via Excel or something like that if that makes sense.

I'm new to Databricks, so I'm not sure how I'd set something like that up.

merca
Valued Contributor II

You can use python code to send email from notebook with attachment, so if you are comfortable with that... here is stackoverflow post on that

Other alternative is to send result to azure function (http trigger) and use whatever code you want to send email.

You can also use Azure data fatory or logic app or power automate to pick up csv file and add as attachment and send email via office 365.

Excel treats csv's as native format and will render is as table, multi sheet workbook is a different story and that may not be achievable. If this is requrement - direct connection to tables are probably your best bet here.

Also alternative - create a databricks sql dashboard that contains tables, graphs and such, send this on schedule to email - it will produce pdf - but its better than nothing if its just overview the person needs.

CBull
New Contributor III

Thanks! I'm not familiar with Python.....But, I can look into figuring it out! Worse case I can use your dashboard suggestion! Thanks!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group