How to Provide SQL Access to MS Excel Files Without Coding

In this post, I’ll show you an example of how you can get quick access to MS Excel files via the SQL (Structured Query Language) console.

To start querying data in SQL, we need to turn a raw unstructured Excel spreadsheet into a SQL table. The data-management SaaS TABLUM.IO will help me import data from the MS Excel spreadsheet, build a database schema for it, cleanse the data automatically, and provide a powerful SQL console to access loaded data for further analysis. No coding is required.

Data source. To describe this example, I’ll take public data from the Global Organized Crime Index Dataset).

This is the direct link to the MS Excel file we are going to work with https://ocindex.net/assets/downloads/global_oc_index_2021.xlsx

Let’s start. Go to https://go.tablum.io/ and log in via your email or existing social network account. What we need to do now is to import an MS Excel file into the TABLUM.IO. database. There are several easy ways of bringing your data into TABLUM.IO, for instance, loading data via URL from a remote host, copy-and-paste via the Clipboard, or drag-and-drop a file from a local computer.

I’m going to load data from the MS Excel spreadsheet located on the remote host using a special TABLUM.IO feature called the “URL Downloader”:

It also allows configuring the periodic refresh of the dataset from the MS Excel file hosted on the remote server.

As an Excel file can comprise multiple sheets, you need to tell TABLUM.IO what spreadsheet you are going to obtain data from. If you don’t specify the sheet number, TABLUM.IO will automatically take data from the last active sheet.

Let’s put the sheet number as the first parameter in the TABLUM.IO Query Console:

sheet = '1'

Now everything is ready, and you can click the “Run Query” button:

As a result, we’ve got a SQL table ready for ad-hoc analysis and further manipulation. Here’s what TABLUM.IO has done during the data import:

Read the original data from an MS Excel Sheet.
Recognized the type of column data in the dataset (DateTime, numeric, special numeric, or string).
Automatically generated database SQL schema.
Created a SQL table and insert data into it.

Here’s how it is visualized afterward:

You may want to extract some specific columns from the source sheet but not the entire dataset. In this case, you need to specify the column names you are going to obtain:

cols = 'Continent, Country, CRIMINALITY'

Once specified, we click the “Run Query” button once again and it will replace the existing data in the table with the updated column set:

Great! Our new dataset contains only three columns we are interested in. They are ‘Continent, Country, and CRIMINALITY”:

Let’s get down to ad-hoc reporting. I want to find out the TOP-10 countries with the lowest crime rate in Europe.

To start querying, first, I click the “SQL Transform” button to access the loaded data via SQL:

It will create a new TABLUM View with the SQL Console UI filled with the default SQL “SELECT” for the entire table. So, basically, we start querying from the data source which is our recently loaded data from MS Excel:

Now I can update or replace the default SQL query in the Console to find out the TOP-10 safest countries in Europe:

SELECT
Continent,
Country,
CRIMINALITY
FROM
DS_.V_1001
WHERE Continent = 'Europe'
ORDER BY CRIMINALITY ASC
LIMIT
10

Done! In the table below, you can see the results of the update query:

Check out this video to have a better understanding of how TABLUM.IO turns MS Excel spreadsheets into relational databases without coding:

That’s all so far. Thank you for reading this post!

Come and try TABLUM.IO for free!

Next time I’m going to share with you more tips on how to make your data preparation routine more productive and pleasant.

Stay tuned :)