Import CSV File Into MySQL Table : MYSQL

Difficulty

Last Reviewed

Notes

Reference Link

Reviewed

Status

Step 1: Create the Database

Log into MySQL:

bash
CopyEdit
mysql -u root -p

Create a database:

sql
CopyEdit
CREATE DATABASE school;
USE school;

Step 2: Create the Tables

Run the following SQL script to create the tables (students, courses, and enrollments):

sql
CopyEdit
-- Create students table
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    date_of_birth DATE NOT NULL
);

-- Create courses table
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_code VARCHAR(10) UNIQUE NOT NULL,
    credit_hours INT NOT NULL
);

-- Create enrollments table
CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

Step 3: Prepare the CSV Files

Create CSV files for students, courses, and enrollments.
Ensure each file matches the column structure of the respective table.

Example CSV file for students (students.csv):

csv
CopyEdit
first_name,last_name,email,date_of_birth
John,Doe,john.doe@example.com,2000-05-15
Jane,Smith,jane.smith@example.com,1999-08-20
Alice,Johnson,alice.johnson@example.com,2001-02-12

Step 4: Import the CSV Files

Use the LOAD DATA INFILE command to import the CSV file:

sql
CopyEdit
-- Import data into students table
LOAD DATA INFILE '/path/to/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(first_name, last_name, email, date_of_birth);

Repeat for courses and enrollments:

sql
CopyEdit
-- Import data into courses table
LOAD DATA INFILE '/path/to/courses.csv'
INTO TABLE courses
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(course_name, course_code, credit_hours);

-- Import data into enrollments table
LOAD DATA INFILE '/path/to/enrollments.csv'
INTO TABLE enrollments
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(student_id, course_id, enrollment_date);

Step 5: Verify the Data

Query each table to confirm the data has been imported:

sql
CopyEdit
SELECT * FROM students;
SELECT * FROM courses;
SELECT * FROM enrollments;

Key Notes for MySQL

File Path:

For LOAD DATA INFILE, the file must be in a directory accessible to the MySQL server.
If the file is local, you can use LOAD DATA LOCAL INFILE and enable -local-infile during connection:

bash
CopyEdit
mysql --local-infile -u root -p

Permissions:

Ensure the MySQL user has the required FILE privilege to access the file.

Error Handling:

Use SHOW WARNINGS; after running LOAD DATA INFILE to troubleshoot import issues.