Import CSV File Into MySQL Table : pgAdmin 4

Step 1: Prepare Your CSV File

1. Ensure the file is in CSV format (.csv).
2. Make sure it has a header row (column names) that matches the table structure in PostgreSQL.
3. Prepare a scheme design to show mirror the csv file showing the column_name, datatypes, constraints, and Example values

Scheme Design: Student

Scheme Design: courses

Scheme Design: enrollments

Create the Database

1. Open pgAdmin 4 and connect to your PostgreSQL server.
2. Right-click on Databases → Select Create → Database.
3. Name the database school and click Save.

Create the Tables

1. Open the Query Tool from the toolbar or by right-clicking the database.
2. Use the following SQL to create the students , enrollment and courses tables:

-- Create the students table


CREATE TABLE students (
    student_id SERIAL 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 the courses table


CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_code VARCHAR(10) UNIQUE NOT NULL,
    credit_hours INT NOT NULL

-- Create the enrollment table to link students and courses
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    enrollment_date DATE DEFAULT CURRENT_DATE
);

Step 4: Import the CSV File

1. Navigate to the table where the data will be imported:
• Expand your database → Expand Schemas → Expand Tables → Right-click your table.
2. Select Import/Export Data.

Step 5: Configure the Import

1. In the Import/Export Data dialog:
• Filename: Click … (browse) and select your CSV file.
• Format: Choose CSV.
• Encoding: Select the appropriate encoding (usually UTF-8).
• Header: Check this box if the first row in your file is a header row.
• Delimiter: Specify the delimiter (default is , for CSV).
2. Verify that the Columns in the dialog match your table's schema.

Step 6: Execute the Import

1. Click OK to begin the import process.
2. A message will appear confirming the successful import or any errors.

Step 7: Verify the Data

1. Query your table to confirm the data has been imported correctly:
Copy

SELECT * FROM student;

Notes and Troubleshooting

1. CSV Formatting:
• Ensure that the number of columns in the CSV matches the table.
• Use quotes (") for text values if they contain delimiters.
2. Permissions:
• Ensure you have write access to the table and the file path is accessible.
3. Error Handling:
• If there’s an error, check the Log Output in pgAdmin for details.