Insert data from csv file in MySQL

 To insert data from a CSV file into a MySQL database, you can use MySQL's LOAD DATA INFILE statement. This method is efficient for bulk data insertion from CSV files. Here’s a step-by-step guide on how to do it:

Step-by-Step Guide

1. Prepare Your CSV File

Ensure your CSV file is properly formatted and accessible from your MySQL server. For example, let's assume your CSV file (books.csv) looks like this:

title,author,publisher,year_of_publication,isbn,category

Book A,Author A,Publisher X,2000,1234567890,Fiction

Book B,Author B,Publisher Y,2005,2345678901,Non-fiction

Book C,Author C,Publisher Z,2010,3456789012,Science

2. Create a Table in MySQL

Before loading data, you need to have a table in your MySQL database that matches the structure of your CSV file. Here's an example of how you might create a Books table:

CREATE TABLE Books (

    title VARCHAR(255) NOT NULL,

    author VARCHAR(255) NOT NULL,

    publisher VARCHAR(255),

    year_of_publication INT,

    isbn VARCHAR(13),

    category VARCHAR(100)

);

3. Load Data from CSV File into MySQL

Use the LOAD DATA INFILE statement in MySQL to import data from the CSV file into your Books table. Here's how you can do it:

LOAD DATA INFILE '/path/to/your/books.csv'

INTO TABLE Books

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n'

IGNORE 1 LINES;  -- to ignore the header row if present

  • /path/to/your/books.csv: Replace this with the absolute path to your CSV file on the MySQL server's filesystem.
  • FIELDS TERMINATED BY ',': Specifies that fields in the CSV file are separated by commas.
  • LINES TERMINATED BY '\n': Specifies that lines in the CSV file are terminated by newlines.
  • IGNORE 1 LINES: Skips the first line of the CSV file, assuming it contains headers. Remove this line if your CSV file does not have header
4. Verify the Data
After running the LOAD DATA INFILE statement, verify that the data has been successfully inserted into the Books table by querying it:
SELECT * FROM Books;

Notes
  • Make sure that the MySQL server has the necessary filesystem permissions to read the CSV file.
  • Adjust the FIELDS TERMINATED BY and LINES TERMINATED BY clauses according to your CSV file's format (e.g., if it uses a different delimiter or line terminator).
  • Ensure that the structure of your Books table matches the columns and data types in your CSV file.

This method is efficient for large datasets and can be much faster than inserting data row by row using INSERT statements, especially when dealing with large CSV files.

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more