How to Import CSV File in MySql Database

How to deal with Common Errors. 7-Step To the Point Guide to Load CSV in MYSQL .

Image Reference

Steps to Follow:

1. Identify the CSV file dataset

Dataset

My dataset is named: mpg.csv

Step 2: Connect to MySQL / Or use MYSQL Workbench Directly

To connect to MySQL:

mysql -u root -p 
enter password:

Step 3: Create New Database

CREATE DATABASE Datascience;

Step 4: Use the new database

USE Datascience;

Step 5: Create a Table in New Database With Column Names:

Note: The column and datatype parameters in the SQL table that we will create must match the number of columns and data types of the CSV file.

USE Datascience;
CREATE TABLE MPG (
mpg varchar(255),
cylinders varchar(255),
displacement varchar(255),
horsepower varchar(255),
weight varchar(255),
acceleration varchar(255),
model_year varchar(255),
origin varchar(255) ,
carname varchar(255))

Along with the column names, you also need to specify the data types of columns.

Most commonly used is varchar :
Varchar: A variable-length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters — can be from 0 to 65535

To Refer to different Data Types in Mysql: W3 school Link

STEP 6: Import the CSV data into the MySQL table

LOAD DATA LOCAL INFILE “C:/Users/Shelvi Garg/Desktop/Code/Datasets General/mpg.csv” 
INTO TABLE MPG
FIELDS TERMINATED BY ‘,’ 
LINES TERMINATED BY ‘\n’ 
IGNORE 1 ROWS (mpg,cylinders,displacement,horsepower,weight,acceleration,model_year,origin,carname);

Dealing with Errors if any you encounter:

• Loading of local data could be disabled in both client and server. To successfully load the file, you need to enable it:

For client-side enabling use this command to “ON” local sharing

For server-side enabling:

Go to Edit Connection — Advanced Setting Tab and write this commands as shown in the image:

OPT_LOCAL_INFILE=1

• Also, do mention the file path in “Load data local file”. Note: To avoid possible errors use forward lash (/) in filepath

STEP 7: Check

SELECT * FROM MPG

You are done!

I made this tutorial focussing mainly on how to deal with common errors one may encounter, as I did while importing.

Hope you are able to import successfully!

Cheers

Shelvi