MySQL - Import CSV File into Database

MySQL: Importing Data from a CSV File

Bulk loading data from external files is a fundamental task in data management. MySQL provides a highly efficient statement, LOAD DATA INFILE, specifically designed to import data from text files, including the common Comma-Separated Values (CSV) format, directly into a database table.

Prerequisites for Importing

Before you can import a CSV file, ensure the following conditions are met:

• A Target Table: You must have an existing table in your database with a structure that matches the data in the CSV file.
• A CSV File: The data file must be accessible to the MySQL server.
• File Privileges: The MySQL user account executing the statement must have the FILE and INSERT privileges.
• Column Order: The columns in the CSV file should ideally be in the same order as the columns in the target table. If not, you must specify the column order in the LOAD DATA statement.

The LOAD DATA INFILE Statement

This command reads rows from a text file and inserts them into a table at a very high speed.

Syntax

LOAD DATA INFILE 'path/to/your/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n' -- or '\r\n' for Windows-style line endings
IGNORE 1 ROWS; -- Use this if your CSV has a header row

• FIELDS TERMINATED BY: Specifies the character separating values (e.g., a comma).
• ENCLOSED BY: Specifies the character used to quote fields, which is useful if your data contains the terminator character (e.g., a comma in a text field).
• LINES TERMINATED BY: Specifies the end-of-line sequence.
• IGNORE N ROWS: Skips the first N lines of the file, typically used to ignore a header row.

Critical Security Consideration: secure_file_priv

Modern MySQL installations have a security feature called secure_file_priv. This system variable restricts file operations (like LOAD DATA INFILE) to a specific directory. If you try to load a file from a different location, you will get an error.

To check your secure_file_priv setting, run: SHOW VARIABLES LIKE 'secure_file_priv';

• If the value is a directory path (e.g., /var/lib/mysql-files/), you must place your CSV file in that directory.
• If the value is NULL, file import/export operations are disabled.
• If the value is empty, there is no restriction (less common and less secure).

An alternative is LOAD DATA LOCAL INFILE, which reads the file from the client machine. This requires both the server and the client to be configured to allow it (local_infile=1 on the server and a flag in the client connection), as it has security implications.

Step-by-Step Example

Let’s import new employee data into our database.

Step 1: The CSV File

Imagine you have a file named new_hires.csv with the following content, including a header row:

id,first_name,last_name,department,salary
101,John,Doe,Engineering,95000.00
102,Jane,Smith,Marketing,82000.00
103,Peter,Jones,Engineering,110000.00

Step 2: Create the Target Table

Create a table that matches the structure of the CSV file.

CREATE TABLE new_employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Step 3: Load the Data

Place new_hires.csv in the directory specified by secure_file_priv. Then, run the LOAD DATA command.

-- Assuming secure_file_priv is '/var/lib/mysql-files/'
LOAD DATA INFILE '/var/lib/mysql-files/new_hires.csv'
INTO TABLE new_employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- To skip the header

Step 4: Verify the Import

Check the contents of the table to confirm the data was imported successfully.

SELECT * FROM new_employees;

id	first_name	last_name	department	salary
101	John	Doe	Engineering	95000.00
102	Jane	Smith	Marketing	82000.00
103	Peter	Jones	Engineering	110000.00

Importing a CSV File Using a Client Program

Automating CSV imports is often done via scripts. Here’s how to do it using modern programming practices.

Syntax Overview

Node.js
Python
Java
PHP



Using `mysql2/promise`. Note the use of `local-infile` option in the connection for `LOAD DATA LOCAL INFILE`.

const connection = await mysql.createConnection({ ...config, localInfile: true });
const sql = "LOAD DATA LOCAL INFILE ..."; // Note the 'LOCAL' keyword
await connection.query(sql);



Using `mysql-connector-python` with `allow_local_infile=True` in the connection arguments.

connection = mysql.connector.connect(..., allow_local_infile=True)
cursor = connection.cursor()
query = "LOAD DATA LOCAL INFILE ..." # Note the 'LOCAL' keyword
cursor.execute(query)



Using JDBC with the `allowLoadLocalInfile=true` property in the connection URL.

String url = "jdbc:mysql://localhost:3306/db?allowLoadLocalInfile=true";
Connection conn = DriverManager.getConnection(url, user, pass);
Statement stmt = conn.createStatement();
String sql = "LOAD DATA LOCAL INFILE ..."; // Note the 'LOCAL' keyword
stmt.execute(sql);



Using PDO with the `PDO::MYSQL_ATTR_LOCAL_INFILE` attribute set to true.

$options = [ PDO::MYSQL_ATTR_LOCAL_INFILE => true, ... ];
$pdo = new PDO($dsn, $user, $pass, $options);
$sql = "LOAD DATA LOCAL INFILE ..."; // Note the 'LOCAL' keyword
$pdo->exec($sql);

Complete Example (Python)

This Python script demonstrates how to use LOAD DATA LOCAL INFILE to upload a CSV from the client machine. This is often more convenient than moving files on the server.

import mysql.connector
import os

# --- Configuration ---
DB_CONFIG = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database',
    'allow_local_infile': True  # Critical for LOAD DATA LOCAL
}
CSV_FILE_PATH = 'new_hires.csv' # Path on the client machine
TABLE_NAME = 'new_employees'

def import_csv_local():
    """Uploads a local CSV file to a MySQL table."""
    # Ensure the CSV file exists before proceeding
    if not os.path.exists(CSV_FILE_PATH):
        print(f"Error: CSV file not found at '{CSV_FILE_PATH}'")
        return

    try:
        with mysql.connector.connect(**DB_CONFIG) as connection:
            with connection.cursor() as cursor:
                # Use forward slashes and escape backslashes if on Windows
                safe_file_path = CSV_FILE_PATH.replace('\\', '/')

                load_query = (
                    f"LOAD DATA LOCAL INFILE '{safe_file_path}' "
                    f"INTO TABLE {TABLE_NAME} "
                    "FIELDS TERMINATED BY ',' "
                    "LINES TERMINATED BY '\n' "
                    "IGNORE 1 ROWS;"
                )

                print("Executing data import...")
                cursor.execute(load_query)
                connection.commit()
                print(f"{cursor.rowcount} rows were successfully imported into '{TABLE_NAME}'.")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")

if __name__ == "__main__":
    # You would first need to create the CSV file locally
    # with open(CSV_FILE_PATH, 'w') as f:
    #     f.write('id,first_name,last_name,department,salary\n')
    #     f.write('101,John,Doe,Engineering,95000.00\n')
    import_csv_local()