sql - How do I read this text file and Insert into MySQL?

Question

sample

user id  User Name
   U456      Mathew 
   U457      Leon
   U458      Cris
   U459      Yancy
   U460      Jane

and so on up to 500k.

I need to read this text file and insert to MySQL in two columns say User ID and User Name. How do I do this in PHP?

Answer 1

LOAD DATA INFILE

Example:

NOTE: if you run this from Windows you need to escape the forward slashes in the file path.

EXAMPLE:

C:\pathofile.txt

Looks like:

C:\\path\to\file.txt

Here is the query:

LOAD DATA INFILE '/path/to/sample.txt' 
INTO TABLE `database_name`.`table_name` 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'
IGNORE 1 LINES
(
user_id, user_name
)