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.
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.
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.
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”.
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 🙂
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.