Spark Notebook to import data into Excel

[CBull]

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?

[paultyner]

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]

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]

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]

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 · Accepted solution]

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]

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

[Kaniz]

HI @Cory Bullard​ , Looks like your problem is solved. Thank you @Merca Ovnerud​ !!