How can we import a text file with data in the same row and with delimiters into a MySQL table?

Actually, we can write data to the same line of a text file by using delimiters. In this case, we have to use the "LINES TERMINATED BY" option when importing this text file into the MySQL table. It can be understood through the following example -

Suppose we are using "|" as row terminator symbol in the text file as follows -

id,Name,Country,Salary|105,Chum*,Marsh,USA,11000|106,Danny*,Harrison,AUS,12000

Now, while importing this text file into the MySQL table, We also need to mention the "LINE TERMINATED BY" option in the query as shown below -

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee7_tbl FIELDS TERMINATED BY ',' ESCAPED BY '*' LINES TERMINATED BY '|'IGNORE 1 ROWS;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

Now we can view the imported content with the help of the following query -

mysql> Select * from employee7_tbl;
+------+----------------+----------+--------+
| Id   | Name           | Country  | Salary |
+------+----------------+----------+--------+
| 105  | Chum,Marsh     | USA      |  11000 |
| 106  | Danny,Harrison | AUS      |  12000 |
+------+----------------+----------+--------+
2 rows in set (0.00 sec)

The above is the detailed content of How can we import a text file with data in the same row and with delimiters into a MySQL table?. For more information, please follow other related articles on the PHP Chinese website!