How to handle null field when exporting MYSQL tables to CSV
Asked Answered
S

4

19

Right now when I export MYSQL tables to CSV files, I'm getting \N for NULL fields in the database which is expected. If there a way to change the \N output to just an empty string when exporting tables to csv?

Thanks

SELECT 'CU_CustomerID','CU_UserName','CU_Password','CU_Email','CU_Company','CU_Comment','CU_LastBasket','CON_FirstName','CON_MiddleI','CON_LastName','CON_Address1','CON_Address2','CON_City','CON_State','CON_Province','CON_Country','CON_Zip','CON_Phone1','CON_Phone2'
UNION
SELECT T1.CU_CustomerID,T1.CU_UserName,T1.CU_Password,T1.CU_Email,T1.CU_Company,T1.CU_Comment,T1.CU_ShopperPoints,T2.CON_FirstName,T2.CON_MiddleI,T2.CON_LastName,T2.CON_Address1,T2.CON_Address2,T2.CON_City,T2.CON_State,T2.CON_Province,T2.CON_Country,T2.CON_Zip,T2.CON_Phone1,T2.CON_Phone2
FROM CUSTOMERS AS T1
INNER JOIN CONTACT AS T2 ON T1.CU_CustomerID = T2.CON_CustomerID
WHERE T1.CU_CustomerID BETWEEN 0 AND 1000
INTO OUTFILE  'customers.csv'
FIELDS TERMINATED BY  ','
ENCLOSED BY  '"'
Scarlettscarp answered 16/6, 2014 at 19:14 Comment(0)
C
16

From MySQL documentation

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

Cheri answered 16/6, 2014 at 19:19 Comment(6)
So in my case, how would I output NULL fields to empty strings instead of \N? what's the exact query? thanksScarlettscarp
@Scarlettscarp Please add your Statement, then i can correct it.Cheri
@Scarlettscarp add ESCAPED BY ""; at the end of your query.Cheri
ok right now its returning value "NULL" instead of "\N". Is there a way to return empty string for NULl fields?Scarlettscarp
@Scarlettscarp do it in the select: SELECT ifNull'CU_CustomerID',''),ifnull('CU_UserName',''),...Cheri
thats awesome. just doesnt need quotes around column name or it treats it as string. thanks for the help!Scarlettscarp
P
4

The hardway:-

SELECT 'CU_CustomerID','CU_UserName','CU_Password','CU_Email','CU_Company','CU_Comment','CU_LastBasket','CON_FirstName','CON_MiddleI','CON_LastName','CON_Address1','CON_Address2','CON_City','CON_State','CON_Province','CON_Country','CON_Zip','CON_Phone1','CON_Phone2'
UNION
SELECT COALESCE(T1.CU_CustomerID,''),COALESCE(T1.CU_UserName,''),COALESCE(T1.CU_Password,''),COALESCE(T1.CU_Email,''),COALESCE(T1.CU_Company,''),COALESCE(T1.CU_Comment,''),COALESCE(T1.CU_ShopperPoints,''),COALESCE(T2.CON_FirstName,''),COALESCE(T2.CON_MiddleI,''),COALESCE(T2.CON_LastName,''),COALESCE(T2.CON_Address1,''),COALESCE(T2.CON_Address2,''),COALESCE(T2.CON_City,''),COALESCE(T2.CON_State,''),COALESCE(T2.CON_Province,''),COALESCE(T2.CON_Country,''),COALESCE(T2.CON_Zip,''),COALESCE(T2.CON_Phone1,''),COALESCE(T2.CON_Phone2,'')
FROM CUSTOMERS AS T1
INNER JOIN CONTACT AS T2 ON T1.CU_CustomerID = T2.CON_CustomerID
WHERE T1.CU_CustomerID BETWEEN 0 AND 1000
INTO OUTFILE  'customers.csv'
FIELDS TERMINATED BY  ','
ENCLOSED BY  '"'

This will default all values to an empty string rather than NULL ('\N').

Polonaise answered 4/7, 2017 at 6:16 Comment(0)
I
3

Just in case. To avoid typing "ifnull(var,'') as var" for every single variable one can use this:

SELECT GROUP_CONCAT(CONCAT("IFNULL(",COLUMN_NAME,",'') AS ", COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<table name>'
AND TABLE_SCHEMA = '<schema name>'
ORDER BY ORDINAL_POSITION
Iveson answered 9/6, 2016 at 17:30 Comment(0)
A
3

I also had this problem, solved as follows:

select ifnull(`addr`.`reference`, "") AS `reference`, data2 from ...
Alfeus answered 10/7, 2019 at 13:15 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.