Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
Updated on: 07-Feb-2020
116 Views
Advertisements