- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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 −
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
- Related Articles
- 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?
- Python Tkinter – How to export data from Entry Fields to a CSV file?
- How can we export all the data from MySQL table into a text file?
- How to read the data from a CSV file in Java?\n
- How can we export some field(s) from MySQL table into a text file?
- Write data from/to a .csv file in java
- Writing data from database to .csv file
- How to Export and import CSV file manually in PowerShell?
- How to read data from .csv file in Java?