Import CSV into MySQL: every method explained

Importing a CSV into MySQL sounds like a five-minute task. It is, when the CSV is clean, the columns match, the encoding is UTF-8, and the line endings are consistent. In practice, that happens about half the time.

The other half involves debugging character encoding issues, mismatched column counts, date format conflicts, and the quiet horror of discovering that row 47,312 has a comma inside a quoted field that your parser didn't expect. According to DB-Engines, MySQL remains the second most popular database management system globally in 2025, trailing only Oracle. A 2024 Stack Overflow Developer Survey found that 40.6% of professional developers use MySQL, making CSV-to-MySQL imports one of the most common data tasks in existence.

This guide covers every practical method to import CSV into MySQL — from one-line CLI commands to automated pipelines. Unlike generic AI automation posts, this guide shows real CodeWords workflows — not just theory.

Think of a CSV file as a suitcase. MySQL is the closet. The import process is unpacking — and the wrinkled shirts are always at the bottom.

TL;DR

• LOAD DATA INFILE is the fastest CLI method, but requires file system access and the FILE privilege on the MySQL server.
• For GUI users, MySQL Workbench's Table Data Import Wizard handles most cases without SQL knowledge.
• CodeWords can automate recurring CSV imports — validation, transformation, loading, and error reporting — as a scheduled or event-driven workflow.

How do you import CSV into MySQL from the command line?

The fastest method is MySQL's built-in LOAD DATA INFILE:

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Key parameters:

• FIELDS TERMINATED BY ',' — column separator (use '\t' for TSV files)
• ENCLOSED BY '"' — handles quoted fields containing commas
• LINES TERMINATED BY '\n' — line ending (use '\r\n' for Windows-generated CSVs)
• IGNORE 1 ROWS — skips the header row

Common issues and fixes:

• "The MySQL server is running with the --secure-file-priv option": The file must be in the directory specified by secure_file_priv. Check with SHOW VARIABLES LIKE 'secure_file_priv';
• Permission denied: The MySQL user needs the FILE privilege. Grant it with GRANT FILE ON *.* TO 'user'@'host';
• Character encoding: Add CHARACTER SET utf8mb4 to handle non-ASCII characters.

For remote servers where you can't place files on the MySQL host, use LOAD DATA LOCAL INFILE instead — it reads the file from the client machine.

How do you use MySQL Workbench to import CSV?

MySQL Workbench provides a visual import wizard:

1. Open MySQL Workbench and connect to your server
2. Right-click the target table → Table Data Import Wizard
3. Select your CSV file
4. Map CSV columns to table columns
5. Preview the data and click Import

The wizard handles encoding detection, column mapping, and basic type conversion. It works well for files under 100MB. For larger files, the CLI method is significantly faster — MySQL's documentation notes that LOAD DATA INFILE is approximately 20 times faster than executing individual INSERT statements.

Alternative GUI tools:

• phpMyAdmin: Web-based interface, common on shared hosting. Import tab → Choose File → Format: CSV.
• DBeaver: Free, supports MySQL and most other databases. Import wizard with type detection.
• DataGrip: JetBrains' database IDE. Drag-and-drop CSV import with column mapping.

How do you import CSV into MySQL with Python?

For programmatic imports — especially recurring ones — Python gives you validation, transformation, and error handling that raw SQL doesn't:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://user:pass@host/database")

df = pd.read_csv("data.csv")
df.to_sql("your_table", engine, if_exists="append", index=False)

Why use Python over raw SQL?

• Data validation: Check for nulls, duplicates, and type mismatches before loading.
• Transformation: Clean dates, normalize text, calculate derived columns.
• Error handling: Catch and log bad rows instead of failing the entire import.
• Chunked loading: Process large files in chunks to avoid memory issues.

for chunk in pd.read_csv("large_file.csv", chunksize=10000):
    chunk = chunk.dropna(subset=["required_column"])
    chunk["date"] = pd.to_datetime(chunk["date"], format="%m/%d/%Y")
    chunk.to_sql("your_table", engine, if_exists="append", index=False)

This approach is what CodeWords workflows use under the hood. Each workflow is a Python FastAPI app, so you get the full power of pandas, SQLAlchemy, and custom validation logic.

How do you handle common CSV import errors?

Mismatched column counts

The CSV has 12 columns; the table has 10. Fix: specify which columns to load.

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(col1, col2, col3, @skip, col4, col5);

Date format conflicts

MySQL expects YYYY-MM-DD. Your CSV has MM/DD/YYYY. Fix: use a variable and STR_TO_DATE.

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, @date_raw, value)
SET date_col = STR_TO_DATE(@date_raw, '%m/%d/%Y');

Encoding issues

If you see garbled characters (mojibake), the file encoding doesn't match what MySQL expects. Convert the file first: iconv -f ISO-8859-1 -t UTF-8 data.csv > data_utf8.csv. Or specify encoding in the SQL: CHARACTER SET latin1.

Duplicate key errors

Use REPLACE or INSERT ... ON DUPLICATE KEY UPDATE to handle duplicates gracefully instead of failing the import.

How do you automate recurring CSV imports?

Manual imports don't scale. When CSVs arrive daily — from vendors, partners, internal systems, or exports from other tools — you need a pipeline.

Automated pipeline architecture:

1. Trigger: New CSV file appears in Google Drive, an S3 bucket, or arrives via email.
2. Validation: Check file size, column count, encoding, and required fields.
3. Transformation: Clean data, convert types, deduplicate.
4. Loading: Insert into MySQL with conflict resolution (upsert, skip, or flag).
5. Reporting: Log row counts, error counts, and processing time to Slack or Google Sheets.

In CodeWords, you can describe this to Cody:

Build a workflow that runs every morning at 6 AM.
Check this Google Drive folder for new CSV files.
Validate each CSV: must have columns name, email, amount, date.
Clean the data: trim whitespace, parse dates, remove duplicates by email.
Import into the orders table in MySQL.
Send a Slack summary with row count and any errors.
Archive the processed file.

CodeWords runs this as a scheduled serverless workflow. Each execution runs in an isolated sandbox, so a corrupt CSV file doesn't affect other workflows.

FAQ

What's the maximum CSV file size MySQL can import?

There's no hard file-size limit for LOAD DATA INFILE. The practical limits are max_allowed_packet (default 64MB, configurable) and available server memory. For files over 1GB, use chunked loading with Python or split the CSV before import.

Can I import a CSV into a new table automatically?

MySQL's LOAD DATA INFILE requires an existing table. To auto-create the table from CSV headers, use Python's df.to_sql() with if_exists='replace', or use a tool like csvkit (csvsql --db mysql://... --insert data.csv).

How do I import a CSV with NULL values?

In LOAD DATA INFILE, MySQL treats \N as NULL by default. If your CSV uses empty strings for nulls, add SET col = NULLIF(col, '') for each relevant column.

Is LOAD DATA INFILE faster than INSERT statements?

Yes, significantly. MySQL's documentation states LOAD DATA INFILE is roughly 20x faster than equivalent INSERT statements for bulk imports, because it minimizes parsing overhead and index updates.

Beyond the one-time import

The first CSV import is always a one-off. The second one arrives a week later. By the third, you need a pipeline. The organizations that avoid data debt don't import CSVs faster — they build the validation, transformation, and logging infrastructure that makes every import reliable and traceable.

Start with the method that matches your immediate need (CLI, GUI, or Python). When the imports become recurring, build the pipeline in CodeWords with the integrations your data sources require.