Search

Import CSV File Into MySQL Table : MYSQL

Import CSV File Into MySQL Table : MYSQL

The procedure for importing a CSV file into MySQL is similar to PostgreSQL, but there are slight differences in commands and interface, particularly if you're using a tool like phpMyAdmin or the MySQL CLI. Here's how to achieve the same outcome in MySQL:

Step 1: Create the Database

  1. Log into MySQL:
    2. bash
CopyEdit
mysql -u root -p
  2. Create a database:
    3. 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

  1. Create CSV files for students, courses, and enrollments.
  2. 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

  1. Use the LOAD DATA INFILE command to import the CSV file:
    2. 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);
  2. Repeat for courses and enrollments:
    3. 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

  1. 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
  2. Permissions:
    • Ensure the MySQL user has the required FILE privilege to access the file.
  3. Error Handling:
    • Use SHOW WARNINGS; after running LOAD DATA INFILE to troubleshoot import issues.