Byzer SQL Notebooks for Excel Power-Users

Life is Short; We DON’T Use Excel.

As straightforward as the title suggests, let’s get hands dirty.

In this tutorial, we’ll use an Excel file that contains two sheets: order and store.

P.S. Feel free to play with your own Excel workbooks!

#1 — Load Data from an Excel File with Multiple Sheets in Byzer

Load data from Excel sheets into Byzer as a table.

Here is the Load Statement:

load excel.`<file_path>` where header=”<true/false>” and dataAddress=”<sheet_name>!<first_cell>:<last_cell>”

as <table_name>;

-- Import data from Excel Sheet - Orders into a table called order
load excel.`./sample_data/sales/sales.xlsx`
where header="true" and dataAddress="Orders!A1:I4990"
as order;
-- Import data from Excel Sheet - Stores into a table called store
load excel.`./sample_data/sales/sales.xlsx`
where header="true" and dataAddress="Stores!A1:D23"
as store;

#2 — Export Data into an Excel File in Byzer

You can analyze the data by running SQL queries in Byzer and then save those insights into a new Excel file as follows:

-- save your insights into an Excel file
select count(*) as order_num from order as output;
save overwrite output as excel.`./sample_data/sales/insights.xlsx`
where header="true";

Try this command to find what parameters you can play with using Load&Save Excel Functions:

!show datasources/params/excel;

What’s Byzer?

Want to see how seamlessly Byzer Notebook integrates with SQL and Python? Go try this tutorial:

The EASIEST Way to Build and Visualise a Conversion Funnel

You can Do More with Less in Byzer !

Leaving feedback:

I’d love to hear back from you on this topic.

Thanks for reading!

Please share, subscribe to my email list, or follow me on Medium for upcoming blogs.