Import CSV into MySQL: 5 Practical Approaches | Blog

MySQL is widely used for OLTP workloads, but CSV imports can get tricky: delimiters differ, encodings break, headers are inconsistent, and types misalign. Understanding what a CSV file is and where format issues typically arise helps you avoid the most common mistakes. Here are five practical ways to import CSVs into MySQL, from quick commands to repeatable pipelines.

1) LOAD DATA INFILE (server-side)

Fastest bulk option when the CSV is on the database server and you control file permissions.

Best when: file sits on the DB server, you have server access, you need maximum throughput.

LOAD DATA INFILE '/var/lib/mysql/import/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(email, first_name, last_name);

Note

MySQL must be allowed to read the file (check `secure_file_priv`). Paths and permissions matter.

2) LOAD DATA LOCAL INFILE (client-side)

Use when the CSV is on your laptop/CI and you prefer to stream from the client without server file access.

Best when: local/CI imports, no server file placement, occasional/manual runs.

LOAD DATA LOCAL INFILE '/Users/igor/Downloads/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(email, first_name, last_name);

Enable `local_infile=1` on both client and server if disabled; mind client library flags in CI.

3) MySQL Workbench Import Wizard (UI)

Workbench provides a guided CSV import: pick the table, set delimiters/encoding, map columns, and preview before loading.

Best when: non-technical teammates need a UI, small/medium imports, one-off tasks.
Becomes slow on very large files and not ideal for repeatable automation.

4) mysqlimport CLI

A wrapper over LOAD DATA that reads a CSV into a table matching the file name (or specified). Useful for quick CLI flows.

mysqlimport \
  --local \
  --fields-terminated-by="," \
  --fields-enclosed-by='"' \
  --lines-terminated-by='\n' \
  --ignore-lines=1 \
  --user=dbuser --password --host=localhost \
  app users.csv

Great for quick loads; use `--columns` if file columns differ from table order.

5) Custom Import Script (staging or Python)

For recurring or user-facing imports, add validation, type casting, logging, and retries. Two common patterns:

Option A: Staging Table + SQL

LOAD DATA (INFILE/LOCAL) into a staging table (all TEXT).
Validate/transform/cast into the final table with INSERT...SELECT.
Bad rows stay inspectable; one bad value does not block the batch.

Option B: Python + mysql-connector

pip install mysql-connector-python

import csv
import mysql.connector

conn = mysql.connector.connect(
    user="app", password="secret", host="localhost", database="app"
)

with conn.cursor() as cur, open("users.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    rows = []
    for row in reader:
        rows.append((row["email"], row["first_name"], row["last_name"]))
    cur.executemany(
        """
        INSERT INTO users (email, first_name, last_name)
        VALUES (%s, %s, %s)
        """,
        rows,
    )
conn.commit()

Best when: imports recur, validation/transform rules matter, you need logging/retries/error reporting.

Choosing the Right Approach

The right method depends on where the file lives, who triggers the import, and how strict your MySQL security configuration is. The secure_file_priv variable is the first thing to check: if it points to a specific directory, LOAD DATA INFILE only reads files from that path, which rules it out for ad hoc imports unless you control the server filesystem. Many managed MySQL hosts (RDS, PlanetScale, Cloud SQL) disable LOAD DATA INFILE entirely, pushing you toward LOCAL INFILE or application-level loading.

Server-local bulk loads: LOAD DATA INFILE is the fastest path by far, but only practical when you can place files in the secure_file_priv directory and your MySQL user has the FILE privilege. Ideal for cron-driven ETL on the same host.
Client-side or CI imports: LOAD DATA LOCAL INFILE streams from the client, bypassing secure_file_priv. You need local_infile=1 on both server and client. Watch out: some MySQL connectors (JDBC, certain Python drivers) disable local infile by default and require an explicit flag.
Non-technical users or visual review: MySQL Workbench's Table Data Import Wizard handles delimiter detection, encoding, and column mapping through a UI. It is slow on files over 50 MB but eliminates the need for SQL knowledge.
Batch scripting: mysqlimport wraps LOAD DATA behind a CLI. It maps the filename to a table name automatically, which is convenient for convention-based pipelines but awkward when filenames vary.
Recurring production imports: staging tables (all TEXT columns) plus INSERT...SELECT give you a validation layer MySQL otherwise lacks. This is the only built-in pattern that lets you inspect and reject bad rows without aborting the entire batch.

If the data import starts with non-technical users uploading spreadsheets directly inside a product, a client-side CSV upload with mapping can validate and normalize the data before it ever reaches MySQL.

Where FileFeed Fits

The fundamental problem with MySQL CSV imports is trust. LOAD DATA will tell you it processed 10,000 rows, zero errors, zero warnings. You check the table and everything looks fine. Two weeks later a customer support ticket reveals that 300 date fields loaded as 0000-00-00 because the source file used DD/MM/YYYY instead of YYYY-MM-DD, and MySQL in its default sql_mode silently accepted the invalid values rather than rejecting them. Change sql_mode to STRICT_TRANS_TABLES on your staging server and suddenly the same file throws errors that never appeared in production. This inconsistency across environments means you genuinely cannot tell whether a LOAD DATA succeeded correctly without writing a post-load audit query that checks every single row. Data validation best practices exist precisely because MySQL will not protect you from yourself.

FileFeed acts as a trust layer between the raw file and your MySQL tables. Every field is checked against explicit rules you define: date formats are parsed and standardized, strings are measured against your varchar limits before loading (not silently truncated after), and numeric fields are verified to actually contain numbers. You get a clear report of what passed and what failed before a single row is written. Your application receives pre-validated JSON that maps directly to your table structure, so even the simplest INSERT path produces correct data regardless of which sql_mode your MySQL instance happens to be running.

Teams that need this level of confidence on a recurring basis typically connect FileFeed as an SFTP file automation layer, where partner files are checked, cleaned, and delivered on a schedule without anyone having to verify the output manually.

Frequently asked questions about MySQL CSV imports

Why does LOAD DATA INFILE fail with access denied?

MySQL restricts file access through the secure_file_priv variable. The CSV file must be located in the directory specified by this variable, or the variable must be empty. Check the value with SHOW VARIABLES LIKE 'secure_file_priv' and either move your file or adjust the MySQL configuration accordingly.

How do I handle CSV encoding issues in MySQL?

Use CHARACTER SET utf8mb4 in your LOAD DATA statement to handle most encoding issues. If the source file uses a different encoding like Latin-1, specify CHARACTER SET latin1 instead. For files with BOM markers, preprocess them with a text editor or command-line tool to remove the BOM before importing.

What is the fastest way to import large CSV files into MySQL?

LOAD DATA INFILE is the fastest option, often ten to twenty times faster than INSERT statements. For very large files, disable indexes before loading with ALTER TABLE DISABLE KEYS, load the data, then re-enable indexes. Also consider splitting the CSV into smaller chunks and loading them in parallel.

Final Thoughts

LOAD DATA INFILE is one of the fastest CSV import mechanisms across any relational database, regularly hitting hundreds of thousands of rows per second. But that speed comes with strict security constraints (secure_file_priv, FILE privilege, local_infile flags) that make it impractical in many production environments, especially multi-tenant SaaS platforms where you cannot give each client a server-side directory. Cleaning your CSV data before loading removes the most common causes of failed imports. The gap between MySQL's raw import speed and the operational complexity of using it safely in production is where most teams end up writing custom glue code. FileFeed bridges that gap: your files get validated, mapped, and transformed before they reach MySQL, so you can use the simplest possible INSERT path and still maintain data quality across every client.

Teams designing ingestion pipelines for MySQL often evaluate similar patterns used when importing CSV into Redshift or loading CSV into PostgreSQL, where the import constraints differ but the need for validation and mapping remains the same.

How to Import CSV Files into MySQL: 5 Practical Approaches