How MySQL evaluates the blank line between two lines written in the text file while importing that text file into MySQL table?

MySQL MySQLi Database

Suppose if there is a blank line between two line written in the text file then MySQL evaluates it as the data line while importing that text file into MySQL table. It can be understood with the help of the following example −

Example

Suppose we are having a blank line between two lines in a text file named ‘A.txt’ as follows −

105,Chum,USA,11000
106,Danny,AUS,12000

Now we can write the following query to import the data from text file into MySQL table −

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee10_tbl FIELDS TERMINATED BY ',';
Query OK, 3 rows affected, 4 warnings (0.05 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 4

The above query shows that 3 rows have been uploaded with 4 warnings. Now, with the help of the following query we can see what has been uploaded −

mysql> Select * from employee10_tbl;
+------+----------------+----------+--------+
| Id   | Name           | Country  | Salary |
+------+----------------+----------+--------+
| 105  | Chum           | USA      |  11000 |
|   0  | NULL           | NULL     |   NULL |
| 106  | Danny          | AUS      |  12000 |
+------+----------------+----------+--------+
3 rows in set (0.00 sec)

The above result set shows that MySQL evaluates the blank line between two lines as the data value and uploads NULL and 0 in the columns.

Ramu Prasad

Published on 20-Feb-2018 08:07:41

Previous Page Print Page