Search code examples
phpmysqlload-data-infile

LOAD DATA LOCAL INFILE from line after line

i am trying to copy data from text file into db with "load data local infile". the text example:

jack
|
hi   
|-
ron
|
whats new
|-
jack
|
ok
|-
ron
|
bye
|-

the code i am trying:

include 'conect.php'; // conect to DB
$query = "LOAD DATA LOCAL INFILE 'messages2.txt'
INTO TABLE `conv`
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '-'
(name, msg)";
mysql_query($query);
mysql_close();

its only copying the first name to the db. what is the correct way to do this? thanks alot.

Solution

  • The way I look at it is:

    jack|hi|-
ron|whats new|-
jack|ok|-
ron|bye|-

    So I would try:

    FIELDS TERMINATED BY '|'
LINES TERMINATED BY '|-'

    However, since you have carriage returns, you may need either \n or \r or \r\n:

    FIELDS TERMINATED BY '|\n'
LINES TERMINATED BY '|-\n'