Introduction

Importing CSV data into MySQL becomes tricky as soon as missing values are involved. A CSV file might represent “no value” as an empty field, the literal text NULL, or the MySQL-style marker \N, and those are not the same thing unless you map them deliberately during import.

Decide What the CSV Means

Before writing the LOAD DATA statement, inspect how the source file represents missing values. These are the patterns you will usually see:

• empty fields such as ,,
• the text NULL
• the marker \N

MySQL can treat those differently. An empty field may become an empty string for a text column, while \N is commonly interpreted as a real SQL null in text imports. If the file mixes formats, you need explicit conversion rules.

Import into User Variables First

The safest pattern is to load raw values into user variables and then normalize them in the SET clause.

1LOAD DATA LOCAL INFILE '/path/to/contacts.csv'
2INTO TABLE contacts
3FIELDS TERMINATED BY ','
4OPTIONALLY ENCLOSED BY '"'
5LINES TERMINATED BY '\n'
6IGNORE 1 LINES
7(id, @email, @age, @notes)
8SET
9  email = NULLIF(NULLIF(@email, ''), 'NULL'),
10  age = NULLIF(NULLIF(@age, ''), 'NULL'),
11  notes = NULLIF(NULLIF(@notes, ''), 'NULL');

This pattern gives you control over every column. In the example above, both an empty string and the text NULL are converted into SQL NULL.

Example Table and CSV

Suppose the destination table looks like this:

1CREATE TABLE contacts (
2  id INT PRIMARY KEY,
3  email VARCHAR(255) NULL,
4  age INT NULL,
5  notes VARCHAR(255) NULL
6);

And the CSV contains this data:

1id,email,age,notes
21,[email protected],32,NULL
32,,41,preferred customer
43,[email protected],\N,
54,[email protected],NULL,NULL

Without cleanup, the import can leave you with a mixture of empty strings and actual nulls in the same logical field. Using variables and NULLIF makes the result predictable.

Preserve Empty Strings When Needed

Do not assume empty string and null are interchangeable. Sometimes a blank string is meaningful, especially in text columns. If you want to preserve empty text but still convert explicit NULL markers, change the mapping.

1LOAD DATA LOCAL INFILE '/path/to/contacts.csv'
2INTO TABLE contacts
3FIELDS TERMINATED BY ','
4OPTIONALLY ENCLOSED BY '"'
5LINES TERMINATED BY '\n'
6IGNORE 1 LINES
7(id, @email, @age, @notes)
8SET
9  email = NULLIF(@email, 'NULL'),
10  age = NULLIF(NULLIF(@age, ''), 'NULL'),
11  notes = NULLIF(@notes, 'NULL');

This distinction matters later because WHERE notes = '' and WHERE notes IS NULL are different queries.

Check the Import Environment

A correct SQL statement can still fail because of the environment around it. A few settings matter a lot:

• 'LOAD DATA INFILE reads from the MySQL server machine.'
• 'LOAD DATA LOCAL INFILE reads from the client side.'
• 'secure_file_priv can restrict readable directories.'
• line endings such as \r\n can affect how the last field is parsed.

If null handling looks wrong, inspect the raw file first. Many import bugs come from invisible carriage returns, inconsistent quoting, or a client that does not allow LOCAL.

Common Pitfalls

• Assuming an empty CSV field automatically becomes SQL NULL.
• Forgetting that the string "NULL" is still text unless you convert it.
• Mixing several null conventions in one CSV export.
• Using LOAD DATA INFILE when the file is actually on the client machine.

Summary

• Decide whether missing values are encoded as empty fields, NULL, or \N.
• Use user variables plus NULLIF when null conversion needs to be explicit.
• Treat empty string and SQL NULL as different values unless you intentionally collapse them.
• Choose LOCAL or non-LOCAL import based on where the file actually lives.
• Check file formatting and server settings when the SQL looks correct but the import still misbehaves.