How to import a text file to MySQL direct or through PHP

Issue

I need to import my text file to MySQL which has only one field. It is like this:

I could also do this with PHP.

Solution

You can use Mysql LOAD DATA LOCAL INFILE syntax

LOAD DATA LOCAL INFILE '/path/to/file.txt' 
    INTO TABLE 'table1'
    LINES TERMINATED BY '\n'

For this, make sure Mysql has access to /path/to/file.txt. Also the user who is executing the query must have FILE privilege.

With Pure PHP its easy. Read the file, build the query, execute it.
You need to build the query so that you dont end up looping query which is slow.

$data = file("/path/to/file.txt", FILE_SKIP_EMPTY_LINES);

// make sure you have valid database connection prior to this point.
// otherwise mysql_real_escape_string won't work
$values = "('". implode("'), ('", array_map('mysql_real_escape_string', $data)). "')";

$query = "INSERT INTO `TABLE1` (`COLUMN1`) VALUES $values";

// Now just execute the query once.
mysql_query($query);

Answered By – Shiplu Mokaddim

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0