Create Tables and Load the Data from a Spreadsheet

Alright, now that the spreadsheets are in good order, let's create table schemas to import the data exported from the spreadsheet tabs.

We will need two tables: users and new_roots.

Info

In the last step, we created a table called roots, this time we're going to create a new version called new_roots. We did that on purpose, so that in creating the two new tables here we wouldn't have a conflict with that practice table.

Here is the SQL and the resulting DB Browser entries for each table:

users

SQL STATEMENT:

CREATE TABLE "users" (
    "id"    INTEGER,
    "email" TEXT,
    "first" TEXT,
    "last"  TEXT,
    "username"  TEXT
);

Note that this statement has no FOREIGN KEY or PRIMARY KEY constraints in it, and does not even contain the NOT NULL constraints on other columns. This is to facilitate import of data with NULL values, which is necessary for illustrating the properties of JOIN statements we will discuss later. We will fix these issues later on in the tutorial.

You will also see in the next image that users has a PRIMARY KEY constraint. We are removing that for now.

DB Browser Action:

Our users table created with a SQL in DB Browser.

new_roots

SQL STATEMENT:

CREATE TABLE "new_roots" (
    "id"    INTEGER,
    "userid"    INTEGER,
    "english"   TEXT,
    "nicodemus" TEXT,
    "root"  TEXT
);

Note that this statement has no FOREIGN KEY or PRIMARY KEY constraints in it, and does not even contain the NOT NULL constraints on other columns. This is to facilitate import of data with NULL values, which is necessary for illustrating the properties of JOIN statements we will discuss later. We will fix these issues later on in the tutorial.

You will also see in the next image that new_roots has a PRIMARY KEY AND a FOREIGN KEY constraint. We are removing those for now.

DB Browser Action:

Our new_roots table created using SQL in DB Browser.

Import the Exported Data into the Database Tables

Okay, now we need to import the data into the constructed tables. Here is an example of how to do that with the users table. The new_roots table is left as an exercise.

Choose File → Import → Table From CSV File from the DB Browser menu.

A screenshot of DB Browser illustrating how to import a table from a CSV file.

Here you select the file you want to import the data FROM. Navigate to the directory using the popup file browser screen and then select the correct file.

A screenshot of DB Browser illustrating how to import a table from a selected CSV file.

Then select Open.

User Import Characteristics

The Import CSV file view in DB Browser. From here you can customize the file import action.

At this point, you will be presented with another screen asking for the import characteristics of this particular file going into this particular table. At the bottom of the screen you will see a table that is derived from the entries that are in the csv file. The table name is derived from the file name, so change this to the intended table (users). This specifies the table you want the data imported TO. You want to choose Column names in first line since this is the way Excel outputs the data. At this point, the columns should have correct names. It is important to note that if you want these columns to match the column names in the table then you should always make the column headers in the spreadsheet match the column names in the table. The Field Seperator and Quote Seperator columns should be left as is. The encoding is UTF-8 and that is fine for us now. You might select another one if you have a different encoding for your data. Trim Fields should be left selected, since this removes spurious blanks from the beginning and end of strings, and you want that.

At this point, click OK and the data will be imported.

Info

You may see some weird errors at this point. For example, if you have ever put information in a column in your spreadsheet -- but then deleted that information -- the column will be exported with a bunch of blanks. You may need to go back to your spreadsheet and delete that kind of weird/bad information before the import actually works.

Verify the Database

Now, browse the users table with DB Browser for SQLite, you will see that the program populated the table users. 🎉

A DB Browser summary view of our imported users table.

After doing the same steps for the csv file for roots, the new_roots table will now also have the correct data.

A DB Browser summary view of our imported new_roots table.

Next Steps

Congratulate yourself - you have a database! Next we'll learn how to create more rows, and backup and restore the database.🚀