How to Import Text Files into MySQL: A Step-by-Step Guide

Secondary keywords: importing text file into mysql importing text files into mysql Type: Blog Post

This post covers everything you need to know about how to import text files into MySQL.

How to prepare your MySQL table?

First, set up your MySQL table to reflect the structure of your text file data. For instance, if your file includes id, name, and email, create a table with matching columns:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255),
    PRIMARY KEY (id)
);

How to format your text file in MySQL?

Make sure to format your text file correctly; it should meet MySQL’s expectations. A typical CSV file, for example, separates values with commas and rows with newlines. Adjust your file so it looks like this:

1,John Doe,[email protected]
2,Jane Smith,[email protected]

Tailor the file according to the delimiters your data uses, whether they’re tabs, semicolons, or something else.

How to use the LOAD DATA INFILE command in MySQL?

Import your text file into MySQL with the LOAD DATA INFILE command. Adapt the path to your file and modify the delimiter and table/column names as needed:

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
(id, name, email);

Adjust the field terminators and line endings to fit your file’s format. Change the delimiter from a comma to whatever your file uses if necessary.

How to handle file permissions and security in MySQL?

If MySQL can’t access your file due to permission issues, move the file to MySQL’s data directory or enable local file access in your MySQL client:

mysql --local-infile=1 -u yourusername -p

Then import your file locally:

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
(id, name, email);

Final thoughts

By importing text files into MySQL, you empower yourself to manage data more effectively and efficiently. Always remember to back up your database before bulk operations to avoid any data loss.