In this topic we will try out a couple methods to import data directly from a file, specifically from a CSV (comma separated value) file. You could create CSV file directly from most spreadsheet software or even through a text file editor by putting commas between the values. There are some rules associated with creating and parsing CSVs properly, but it’s a relatively straightforward data format, and is popular especially for transporting tabular data in flat files.

Let’s think through a database that has the ER diagram as below.

This is going to be a database for a running club. We will have a table of information for runners and courses. Runners in the club will get together for races in the courses. A race entry will consist of who ran where and what the result was.

The SQL code to create the runner table could be as below:

For the course and race, try to create them so that you will get the results below when you DESCRIBE them.

Now that we have the tables set up, let’s kickstart the database by loading up some data. First, download the three files below. They should be in CSV format. You could check the contents in any text editor, IDE or spreadsheet software.

Below is the code to load files into MySQL tables.

LOAD DATA INFILE '[file location]' 

INTO TABLE runner

FIELDS TERMINATED BY ',' 

LINES TERMINATED BY 'n'

IGNORE 1 ROWS;

You could see that it’s laying out where the file is, where the data will go, then what the data field delimiters are (commas) and what the row delimiters are (new line). Also, note that it’s asking to ignore the first row. If you’ve opened up the CSVs, you might have noticed that the first row was headers, so you wouldn’t want the first row to load as data. Of course, this might not always be the case.

Did it work?

Unless you’ve changed some parameters of your MySQL instance, you might have gotten an error message that reads “Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement.” This is a security feature in MySQL that is set by default that only lets files from a specific trusted location be available for certain actions (such as direct uploads).

Try the command below.

It will show the secure location that MySQL will accept files. Find the exact location as listed and copy the files you need to upload in that folder. You’ll have to make sure that the file location is exactly the same and you will also have to use escape characters for the backslashes.

Before you upload all three CSV files, let’s also try out the upload wizard feature through MySQL workbench. First try to find the table you’d like to upload the CSV to in the navigator pane. If you don’t see the table you would like to upload, right click in the pane and click on “Refresh All.”

Once you see the table to upload data into, right click on the table and click on “Table Data Import Wizard.”

Use browse to find the CSV to load.

Select “Use existing table” and choose the table the CSV data will go into.

Check that the columns are mapping correctly and click “Next.”

Then click next a couple times and “Finish” and you’re good to go! Use SELECT to check if the data has been imported correctly. This is also a good timing to explore further on what could be done in the navigation pane.

Once you’ve uploaded all three CSVs into the three tables, we’re ready to move on to the next topic!

You can watch a video of a brief walk through of this topic here.