How to load CSV file in MySQL in MySQL Workbench

Hello Guys,

This is a step by step guide to load data from a CSV file into a relation (table) in MySQL workbench.

In brief, here is the list of steps that we will follow

1. Create a database in the workbench
2. Have a look at the CSV file
3. Create a table
4. Import the CSV file data into the table

Let’s get started then.

Step 1: Open server in the workbench

open server in workbench

Step 2: Create a SQL file by clicking below shown icon

create SQL file

Step 3: Create the database

To load any CSV files, firstly we will need a database.

Using the below command, we can create a database in MySQL.

create database dummy;

To run a query where our cursor currently is, we can use the below icon in the workbench window.

run the current query

We can verify in the action output whether the query is executed successfully.

Voila! we have created a database.

Yeah, there is one important step left but.

After creating a database, to use it. We have to explicitly mention to MySQL that yes! we want to use the newly created database.

use database dummy;

Let's move on to the next step then.

Step 4: Have a look at your CSV file.

snippet of CSV file

In this step, we will check the following things about our CSV file

1. number of columns
2. names of columns
3. type of values in columns
4. row number from where actual data that we want to load in relation (table) is starting

So here there are two columns with team_id, team_name as column names respectively. And the data that we want to load starts from row number 2.

This is helpful to create our table schema. Our table schema should be identical to the CSV file schema for a successful import.

Step 5: Create a relation (table)

Now we will create a table in which our CSV file data will be imported.

create table team (
team_id int auto_increment,
team_name text,
primary key (team_id)
);

Let’s understand the above commands.

• We have created a table named team with columns team_id and team_name
• For every column, we have to specify what type of values it will have. Hence, as per our CSV file, we can have integer type values in the team_id and text type values in the team_name column.
• We can have some additional constraints on the data that a column can have.
• Here we have given auto_increment constraint on team_id and we have made it primary key for our table. auto_increment will automatically give a value which is an increment of max value of team_id column to team_id for that newly inserted data. While primary key for any table specifies that we can uniquely pick the corresponding row from the table using that column.

You can have more information about constraints in the manual.

Now let’s move on to the step that will help us achieve our final goal!

Step 6: Import CSV file data into the relation (table)

We can load the CSV file using the following command

load data local infile "team.csv" 
into table team
fields terminated by ','
lines terminated by '\n'
ignore 1 rows
(team_id, team_name)
;

Let’s understand how we did it.

• load data is to load the CSV file data.
• local keyword specifies where the CSV file is to be found. It specifies that the client host will read the file and send it to the server.
• fields terminated by ‘,’ specifies that columns in our CSV file will be terminated by given delimiter i.e. comma (,)
• same as lines terminated by ‘\n’ specifies the delimiter at the termination of rows in our CSV file.
• As per our CSV file, we will need to specify how many rows we want to ignore i.e. from which row the MySQL command should actually start importing data. In our case, our first row contains columns names that we want to ignore. And so ignore 1 rows
• Finally, we will specify which column of our table corresponds to which column of our CSV file.

Run the command and check the green tick!

Ta Da! we are done.