How can we store any other value than \N in CSV file if we export the data to CSV file from a table which contains a NULL value(s)?

If we want to store any other value than \N in CSV file on exporting the data to CSV file from a table which contains NULL value(s) then we need to replace \N values with other value by using IFNULL statement. To illustrate it we are taking the following example −

Example

Suppose if we want to export the values of the table ‘student_info’ having the following data −

mysql> Select * from Student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
| 130  | Ram     | Jhansi     | Computers  |
| 132  | Shyam   | Chandigarh | Economics  |
| 133  | Mohan   | Delhi      | Computers  |
| 150  | Saurabh | NULL       | Literature |
+------+---------+------------+------------+
7 rows in set (0.00 sec)

As we can see that the result has NULL value for the address field where id is 150. Now the following query will export this table’s data into Student_28.CSV and stores ‘Not Applicable’ at the place of \N −

mysql> Select IFNULL(id,'Not Applicable'), IFNULL(Name,'Not Applicable'), IFNULL(Address,'Not Applicable'), IFNULL(Subject,'Not Applicable') from Student_info INTO OUTFILE 'C:/mysql/bin/mysql-files/student_28.csv' FIELDS TERMINATED BY ',';
Query OK, 7 rows affected (0.02 sec)

We can see that student_28.CSV have Not Applicable at the place of \N as can be seen from the following values −

101   YashPal   Amritsar         History
105   Gaurav    Chandigarh       Literature
125   Raman     Shimla           Computers
130   Ram       Jhansi           Computers
132   Shyam     Chandigarh       Economics
133   Mohan     Delhi            Computers
150   Saurabh   Not Applicable   Literature

Nancy Den

Published on 20-Feb-2018 09:54:44

Previous Page Print Page