load data infile optionally enclosed by not working as expected

If MySQL splits a quoted CSV value into separate columns, you can fix the import by checking the enclosure, delimiter, and escaping rules. Most cases take only a few minutes to isolate.

The symptom behind load data infile optionally enclosed by not working as expected usually comes from one of three places:

• Incorrect quoting: The SQL generated by PHP doesn't contain the enclosure character you intended.
• Mismatched CSV formatting: The file's quotes, escape characters, or line endings don't match the LOAD DATA options.
• Unexpected field structure: MySQL interprets extra separators, malformed quotes, or missing fields differently from the CSV exporter.

MySQL's OPTIONALLY ENCLOSED BY '"' tells the import parser to recognize double-quoted fields. A comma inside a properly enclosed value should remain part of that value, not become a column separator. If it doesn't, inspect the actual SQL and CSV bytes, verify the escape rules, and test the import directly in MySQL.

Quick fix: Stop MySQL from splitting commas inside quotes

Consider this CSV record:

ABSOLUTE,Glenn,Anakie,"Cool Synd, Super Cool"

It contains four fields:

The comma inside the owner field is data, not a separator.

A broken import might produce five values instead:

ABSOLUTE
Glenn
Anakie
"Cool Synd
 Super Cool"

That's the behavior we're fixing.

Create a temporary test table in your selected database:

CREATE TABLE import_test (
    company VARCHAR(100),
    first_name VARCHAR(100),
    city VARCHAR(100),
    owner VARCHAR(255)
);

Save the sample CSV record as /tmp/owners.csv on a Linux machine running the MySQL server.

For this example, the file uses Unix line endings and contains no header.

Run:

LOAD DATA INFILE '/tmp/owners.csv'
INTO TABLE import_test
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(company, first_name, city, owner);

Then check the result:

SELECT
    company,
    first_name,
    city,
    owner
FROM import_test;

Expected owner value:

Cool Synd, Super Cool

The double quotes should disappear, while the internal comma remains.

The command above is based on documented MySQL syntax. It hasn't been executed against your specific database or CSV file.

One detail matters here: OPTIONALLY doesn't change how MySQL reads enclosure characters during an import. MySQL documents that ENCLOSED BY and OPTIONALLY ENCLOSED BY have the same input interpretation.

OPTIONALLY primarily affects which field types receive quotes when exporting data with SELECT ... INTO OUTFILE.

See the MySQL LOAD DATA reference for the documented behavior.

Why load data infile optionally enclosed by not working as expected happens

Cause 1: PHP escapes the enclosure character incorrectly

A common source of confusion is mixing PHP string escaping with SQL string escaping.

For example, older PHP code might generate an import query like this:

$q = "LOAD DATA INFILE '$file'
      INTO TABLE import_test
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '\"'
      LINES TERMINATED BY '\\n'";

There are two layers involved:

1. PHP builds the query string.
2. MySQL interprets the resulting SQL.

The backslash before the double quote in a PHP double-quoted string may be needed to keep the PHP string valid.

But that doesn't mean MySQL requires that same backslash in the SQL statement.

How to confirm

Print the actual query before executing it:

echo $q;

Look for:

OPTIONALLY ENCLOSED BY '"'

If PHP generates unexpected backslashes or malformed quotation marks, fix the PHP string first.

A clearer option is to use a nowdoc string when the SQL doesn't require PHP variable interpolation:

$sql = <<<'SQL'
LOAD DATA INFILE '/tmp/owners.csv'
INTO TABLE import_test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(company, first_name, city, owner)
SQL;

Nowdoc preserves the SQL text without applying PHP's double-quoted string escaping.

If you need a dynamic file path, use a properly quoted value generated by your database connection. Don't concatenate untrusted file paths or table names directly into SQL.

Also, avoid mysql_query() in modern applications. The old mysql_* extension was removed from PHP, and current projects should use PDO or MySQLi.

Cause 2: MySQL CSV import splits a quoted field at a comma

This is the exact symptom developers usually notice first.

The file contains:

ABSOLUTE,Glenn,Anakie,"Cool Synd, Super Cool"

But the imported values don't match the intended columns.

First, check whether the quote immediately follows the field separator.

This is valid for the sample format:

ABSOLUTE,Glenn,Anakie,"Cool Synd, Super Cool"

This has an extra space before the opening quote:

ABSOLUTE,Glenn,Anakie, "Cool Synd, Super Cool"

That space matters.

MySQL doesn't automatically treat every quote found inside a field as an enclosing quote. An enclosure is recognized at the beginning of a field.

How to confirm

Open the CSV in a text editor that shows whitespace, not Excel's formatted table view.

Check the characters before the quoted value.

You can also inspect a small sample using Python:

from pathlib import Path

path = Path("/tmp/owners.csv")

for line in path.read_bytes().splitlines()[:5]:
    print(repr(line))

Expected output for the example:

b'ABSOLUTE,Glenn,Anakie,"Cool Synd, Super Cool"'

An unwanted space will appear visibly:

b'ABSOLUTE,Glenn,Anakie, "Cool Synd, Super Cool"'

Fix

Correct the CSV export process so quoted fields begin immediately after their delimiter.

Don't blindly remove all spaces from the source file. Spaces inside customer names, addresses, and descriptions may be meaningful.

Some older Stack Overflow answers suggest reversing the order of TERMINATED BY and OPTIONALLY ENCLOSED BY.

Both orderings are supported by MySQL's field-option syntax. Changing their order isn't a general fix for malformed CSV.

I'd verify the actual field contents before changing a query that is already valid.

Cause 3: The CSV uses double quotes differently from MySQL's escape rules

CSV exporters commonly represent a double quote inside a quoted field by repeating it.

For example:

ABSOLUTE,Glenn,Anakie,"The ""Super Cool"" Syndicate"

The expected owner value is:

The "Super Cool" Syndicate

MySQL supports doubled enclosure characters in this situation.

It also supports an escape character configured through FIELDS ESCAPED BY.

For example, another exporter might produce:

ABSOLUTE,Glenn,Anakie,"The \"Super Cool\" Syndicate"

These formats look similar when opened in a spreadsheet, but their actual bytes differ.

How to confirm

Find a row containing both an internal quote and a comma.

Check whether the file uses doubled quotes, backslash-escaped quotes, or inconsistent combinations.

For MySQL's default backslash escape handling, use:

FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'

The SQL literal '\\' represents a single backslash character under MySQL's normal string-escaping mode.

If your connection uses NO_BACKSLASH_ESCAPES, SQL literal handling changes. Check it with:

SELECT @@SESSION.sql_mode;

Don't set ESCAPED BY '' just because the file uses doubled quotes.

Removing escape processing can create ambiguous parsing behavior and affect how special sequences are interpreted.

For a file generated by another system, first identify its actual CSV dialect, then configure MySQL to match it.

Cause 4: LOAD DATA INFILE reads the wrong line endings

A CSV created on Windows often uses carriage return plus line feed:

\r\n

A Unix-style file usually uses:

\n

If you specify the wrong record terminator, MySQL may leave carriage-return characters inside the last column or handle record boundaries incorrectly.

How to confirm

Inspect the file's line endings with Python:

from pathlib import Path

data = Path("/tmp/owners.csv").read_bytes()

print("CRLF:", data.count(b"\r\n"))
print("LF:", data.count(b"\n"))
print("CR:", data.count(b"\r"))

Remember that the LF count also includes the LF part of CRLF.

If the file uses Windows line endings, specify:

LOAD DATA INFILE '/tmp/owners.csv'
INTO TABLE import_test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(company, first_name, city, owner);

For a file with Unix line endings, use '\n' instead.

Don't assume the operating system determines the file format. A CSV downloaded from another application can use either style.

Cause 5: MySQL reports too many columns or missing fields

The enclosure may be correct, but the CSV and table structure can still disagree.

For example:

ABSOLUTE,Glenn,Anakie,"Cool Synd, Super Cool",ACTIVE

There are five fields here.

Our test table expects four.

MySQL can report a warning such as:

Row 1 was truncated; it contained more data than there were input columns

Another possibility is:

Row 1 doesn't contain data for all columns

These messages point to a mismatch between the parsed record and the expected field layout.

How to confirm

Immediately after the import, run:

SHOW WARNINGS;

Then inspect the table:

SELECT *
FROM import_test;

If the CSV contains an additional status field, add the correct destination column or explicitly map only the fields you intend to use.

For example, if you want to ignore the fifth field:

LOAD DATA INFILE '/tmp/owners.csv'
INTO TABLE import_test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(
    company,
    first_name,
    city,
    owner,
    @ignored_status
);

The user variable accepts the extra CSV value without requiring an additional table column.

Don't change a VARCHAR column to TEXT expecting that to fix quote parsing.

Field parsing happens before MySQL assigns the parsed value to the destination column.

Changing the data type can help with a value that's too long, but it won't correct an incorrectly split CSV field.

For more on field selection and mapping, see how to select multiple columns based on a condition in SQL.

Cause 6: LOAD DATA LOCAL INFILE is disabled or the file can't be accessed

Sometimes the import never reaches CSV parsing.

Instead, MySQL returns:

ERROR 3948 (42000): Loading local data is disabled

Or:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option

These are access or configuration problems, not enclosure problems.

How to confirm

Run:

SHOW VARIABLES LIKE 'local_infile';

SHOW VARIABLES LIKE 'secure_file_priv';

There are two different import modes:

• LOAD DATA INFILE reads a file from the MySQL server's filesystem and is subject to server-side file restrictions.
• LOAD DATA LOCAL INFILE reads a file from the client machine and transfers it to the server.

If you're importing through PHP on another machine, LOCAL may be appropriate, provided both the client library and server permit it.

For example, when enabled and permitted:

LOAD DATA LOCAL INFILE '/tmp/owners.csv'
INTO TABLE import_test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

For the MySQL command-line client, a permitted local-file connection can be started with:

mysql --local-infile=1 -u your_user -p your_database

This doesn't override server-side restrictions or guarantee that local loading is enabled.

Enable local-file imports only for trusted data sources. The restriction exists for security reasons.

If the server uses secure_file_priv, place server-side import files in its permitted directory rather than disabling the restriction unnecessarily.

Cause 7: The CSV encoding doesn't match the MySQL import

An encoding mismatch usually affects characters rather than the basic comma delimiter.

You might see:

José

Instead of:

José

Some files also contain a UTF-8 byte order mark (BOM) at the beginning.

A BOM can become part of the first imported field if it isn't removed.

How to confirm

Check the first bytes:

from pathlib import Path

data = Path("/tmp/owners.csv").read_bytes()

print(data[:8])

A UTF-8 BOM starts with:

b'\xef\xbb\xbf'

If the source is UTF-8, explicitly tell MySQL:

LOAD DATA INFILE '/tmp/owners.csv'
INTO TABLE import_test
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The CHARACTER SET clause doesn't remove a BOM automatically.

If necessary, remove the BOM during preprocessing. Keep the original file as a backup.

For related connection-level encoding issues, see how to fix an unknown character set index received from MySQL.

Fixing CSV import issues in PHP with PDO

If the SQL works directly in MySQL but fails through PHP, inspect the query generated by your application.

Here's a PDO example using a fixed server-side CSV path.

<?php

$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=test;charset=utf8mb4',
    'your_user',
    'your_password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]
);

$sql = <<<'SQL'
LOAD DATA INFILE '/tmp/owners.csv'
INTO TABLE import_test
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(company, first_name, city, owner)
SQL;

try {
    $pdo->exec($sql);

    $warnings = $pdo->query(
        'SHOW WARNINGS'
    )->fetchAll(PDO::FETCH_ASSOC);

    print_r($warnings);

} catch (PDOException $e) {
    error_log($e->getMessage());
    throw $e;
}

This example uses server-side LOAD DATA INFILE, not LOCAL.

The MySQL user must have the required permission, and the CSV must be accessible to the database server.

For a Laravel application, you can also run the same verified SQL through the database connection.

use Illuminate\Support\Facades\DB;

DB::statement($sql);

Here, $sql must contain the complete, trusted import statement.

Don't build it from user-supplied table names without validation. SQL placeholders aren't a general solution for table identifiers.

Also, don't automatically retry imports after errors. A previous attempt might already have inserted some records.

If an import must be repeatable, use a staging table and decide how duplicate records should be handled.

If nothing worked: Collect the exact query and warnings

At this point, stop changing delimiter settings randomly.

You need evidence from one failing row.

Start with a fresh test table, preferably separate from production data. Run the import once, then immediately collect diagnostics:

SHOW WARNINGS;

SHOW COUNT(*) WARNINGS;

SELECT COUNT(*) AS imported_rows
FROM import_test;

SHOW WARNINGS reports conditions from the most recent relevant statement, so don't run unrelated queries before collecting it.

Check the MySQL server version and SQL mode:

SELECT
    VERSION() AS mysql_version,
    @@SESSION.sql_mode AS sql_mode,
    @@GLOBAL.local_infile AS local_infile,
    @@GLOBAL.secure_file_priv AS secure_file_priv;

For the server error-log location, use:

SHOW VARIABLES LIKE 'log_error';

The server error log helps with operational failures, but row-level CSV parsing warnings are usually more useful here.

The slow query log isn't the first place I'd look for a field-splitting problem.

If the import runs through PHP or Laravel, also capture the application exception and the exact SQL text, with credentials and sensitive paths removed.

For Laravel, check:

storage/logs/laravel.log

Or the dated log files in that directory if your application uses daily logging.

If the same data loads correctly in the MySQL client but not through PHP, compare the actual SQL strings, connection settings, and file locations.

For more complicated database sessions, finding the query from a MySQL query ID can help you inspect database activity.

Before escalating the issue, collect:

• One failing CSV record with sensitive data replaced
• The exact LOAD DATA statement sent to MySQL
• Output from SHOW WARNINGS
• MySQL version and SQL mode
• CSV encoding and line-ending format
• Whether the import uses LOCAL

A small reproduction is much easier to debug than a large production CSV.

If you'd like help checking the actual import statement or PHP integration, you can contact HeadsNTail.

FAQ

Does OPTIONALLY ENCLOSED BY work with commas inside quoted fields?

Yes. MySQL treats a delimiter inside a properly enclosed field as part of that field's value.

For example, "Cool Synd, Super Cool" should import as one value when you use FIELDS TERMINATED BY ',' and OPTIONALLY ENCLOSED BY '"'. If it's split, check the opening quote position and the actual SQL being executed.

What is the difference between ENCLOSED BY and OPTIONALLY ENCLOSED BY in MySQL?

For LOAD DATA input, both interpret the enclosure character the same way.

The difference matters mainly when exporting with SELECT ... INTO OUTFILE. Without OPTIONALLY, all fields are enclosed; with it, MySQL encloses string-type values rather than every value.

Why does LOAD DATA INFILE remove double quotes?

MySQL removes recognized enclosure characters from the beginning and end of imported field values.

That is expected behavior, not data loss. Quotes that belong inside a field must follow the supported quote-escaping rules so MySQL recognizes them as actual content.

Can LOAD DATA INFILE import CSV files with headers?

Yes. Use IGNORE 1 LINES to skip the first line when it contains column names.

For example:

LOAD DATA INFILE '/tmp/owners.csv'
INTO TABLE import_test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(company, first_name, city, owner);

Only use this option if your CSV actually contains a header. Otherwise, you'll lose the first data record.

Why does MySQL import fewer rows than my CSV contains?

Possible causes include duplicate-key errors, malformed record boundaries, missing values, or restrictions on the import operation.

Check the statement result and run SHOW WARNINGS immediately afterward. Also verify that the line terminator matches the file and that any IGNORE or REPLACE behavior is intentional.

For load data infile optionally enclosed by not working as expected, the next useful step is to reproduce one failing row in a temporary table, inspect the exact query, and compare its parsed values with the original CSV bytes before modifying the production import.

Originally published at headsntail.com. Free tools for PHP, Laravel and cPanel work: headsntail.com/tools.