Market Sizing Prescription Drugs in the UK – Part 2 – How to Upload Data into an Exasol Database

by Emily Chen

This is a continuation of my previous post on market sizing prescription drugs in the UK.

This was the first time I had used SQL and so the idea of how to import the data was vague because I couldn’t understand how we were storing/querying the data. Luckily Damiana walked me through it – I’m just doing the documentation :). It’s a 3 step process …so pretty easy!

Let’s assume I wanted to upload my table with Region and Post Code data. Here’s what it looks like in excel.

post code to region data

Step 1: Create a Table

So currently this file currently lives on my desktop and it has to go into the database. That’s how we can join 2 tables together (as I will be joining this table against my prescription dataset table). In order for it to go into a database, this data has to be imported somehow. The way to do this is to first create a table (the place where it will be imported). Here’s the syntax for creating a table within SQL.

Create Table Query

When we run this command, we are giving a description of the table. This includes its name (“UK_Regions”), which database it will live in (PRESCRIPTIONS). The shorthand for this is PRESCRIPTIONS.UK_Regions.  Within the brackets, we are telling EXASOL that there are 2 fields within the table (where the syntax is partitioning the fields). For those 2 fields, here is the name (“County”/”Post_Code”), its character type (varchar), how many characters in each field type (255 is standard). And for each field, do not create NULL values.

When you run this query, a new table will pop-up underneath the PRESCRIPTIONS database with a table called UK_Regions… exactly what we wanted.

Create Table - Post Query

Step 2: Import the Data

Once you have created a table for where the data will be stored, you can now import the data. Here’s what to select and what the prompts will look like.

To import, right click on the table and select “Import”.

create table import

 

Then you will be prompted with this window. Navigate to where the file is stored and fill out the rest. If it’s an excel file, save it as a CSV and clear all the formatting inside to save yourself a world of headache. You might have to troubleshoot how to configure the options, but let me know if its not working 🙂

 

 

Create Table - Import Window - Select File

Step 3: Check to see if the data has imported correctly

Run a SQL command to check if the table has imported correctly. Since it is now a table, we can run SQL commands against it. I just ran the classic command below and have also displayed the resulting table. Looks good! We’re ready to start blending the data.

Reviewing Table Import

 

 

Happy SQL-ing!