How to Import Data From Text File Using MySQL Query

If you need to import data from text file (.txt, .csv, …) to a database table, you should use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.

For example:

LOAD DATA LOCAL INFILE '/home/cpaneluser/data.txt' INTO TABLE Data;

LOAD DATA LOCAL INFILE ‘file-path‘ INTO TABLE table-name;

You need to upload the text file to your hosting account and then add the path to the query.

Note: Line terminators can differ depending on the operation system where the file has been created.

In Linux a newline is signified with the following characters: \n

In Windows a newline might be signified with \r\n or only \r (WordPad).

You need to know that difference if you need to specify newlines in your SQL import query.

For example:

$query="FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx' LINES TERMINATED BY '\r\n'";

If the text file is created on Windows OS, you might have to use LINES TERMINATED BY ‘\r\n’ as a line terminator.