During Data Analysis it is a common situation during which we have to import CSV files into MYSQL database.

In this post, we will discuss the same scenario and a simple way for importing CSV files using SQL.

Before importing a CSV file we need to create a table that will contain all columns of CSV file.

Suppose I have CSV file order_items_dataset.csv and we want to import this CSV file in  MYSQL Database.

 

Then before importing CSV file, we have to create a table in MYSQL Database.

So, At first, we are creating a table

Create a table in MYSQL Database

/**
  * create order_items_datasets table for importing csv file
*/

CREATE TABLE order_items_datasets (
order_id int PRIMARY KEY,
order_item_id int,
product_id varchar (200),
seller_id varchar(200),
shipping_limit_date DATETIME,
price float,
freight_value float
)

Now our table has been created successfully.

Now we need to import CSV file in order_items_datasets table.

For that, we have to follow these few steps.

STEP 1:   Load Data

For that purpose, we will LOAD DATA Statement.

LOAD DATA Statement: reads a row from a text file into a table with a very high speed.  

# comment line

LOAD DATA

Step 2: Include CSV file

For including CSV file from which we have to import Data we used INFILE command with LOAD DATA  Statement.

INFILE: allow us to read CSV data from a text file.

NOTE: LOAD DATA INFILE statement allows us to read CSV data from a text file and import that into a database table with very fast speed.  

So for Including a file, we will write 

# comment line

LOAD DATA INFILE "/home/dheeraj/Downloads/brazilian-ecommerce/order_items_dataset.csv"  -- file location

Step 4: Import Data to table

For importing data inside the table we will add INTO then after table name with LOAD DATA statement. 

As shown below for importing data into my new table order_items_datasets that we have created previously.

# import data inside table


LOAD DATA INFILE "/home/dheeraj/Downloads/brazilian-ecommerce/order_items_dataset.csv" -- file location
INTO TABLE order_items_datasets -- table name 

Step4: Extract values from CSV file 

CSV file contains comma-separated values. So we need to separate these values on the basis of rows and columns.

For this, we will add these few lines

# comment line

FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Step5: Ignore header of CSV file

During importing CSV file we need to ignore the header. For this, we will add this line.

# ignore header 

IGNORE 1 ROWS;

After following these steps we can be able to import CSV files in MYSQL database.

Complete Query:

/**
Create table
*/

CREATE TABLE order_items_datasets (
order_id int PRIMARY KEY,
order_item_id int,
product_id varchar (200),
seller_id varchar(200),
shipping_limit_date DATETIME,
price float,
freight_value float
)

--  load data 
LOAD DATA

-- include file

LOAD DATA INFILE "/home/dheeraj/Downloads/brazilian-ecommerce/order_items_dataset.csv"  -- file location

-- import data inside table

LOAD DATA INFILE "/home/dheeraj/Downloads/brazilian-ecommerce/order_items_dataset.csv" -- file location
INTO TABLE order_items_datasets -- table name 

-- extract data

FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

-- ignore header

IGNORE 1 ROWS;