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

Updated on 07-Feb-2020 05:36:23

• Related Questions & Answers
• 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)?
• How MySQL evaluates if we export the data to CSV file from a table which contains a NULL value(s)?
• How can we export all the data from MySQL table into a CSV file?
• How can we export some field(s) from MySQL table into a CSV file?
• How can we export data to a CSV file whose filename name contains timestamp at which the file is created?
• How can we import data from .CSV file into MySQL table?
• How can we export data to a CSV file along with columns heading as its first line?
• What kind of settings can we do to a CSV file by query while exporting the values from MySQL table into a CSV file?
• How can we export all the data from MySQL table into a text file?
• How can we export some field(s) from MySQL table into a text file?
• Python Tkinter – How to export data from Entry Fields to a CSV file?
• Write data from/to a .csv file in java
• How to read the data from a CSV file in Java?
• Writing data from database to .csv file
• How to read data from .csv file in Java?
• Plot data from CSV file with Matplotlib