Thanks Jorrit,
Yes, it is a combi of Paul's suggestion to use IFNULL()
with the ENCLOSED BY '' option. So when using:
Select IFNULL(Jan,''), IFNULL(Feb,''), ... Into Outfile ...
Fields Terminated By ';' Enclosed By '' Escaped By ''
Lines Terminated By '\r\n' ...
I do get results like 1;2;;4;;2;9 without NULLs or quotes.
Regards, Cor
----- Original Message -----
From: "Jorrit Kronjee" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, April 02, 2006 11:33 AM
Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
Cor,
You can set the enclosing character with the ENCLOSED BY parameter.
So something like ENCLOSED BY '' will remove those quotes.
- Jorrit
C.R.Vegelin wrote:
Thanks Paul,
Yes, I've tried IFNULL() to map NULL values to empty strings.
But then I get "1";"2";"";"4";"";"2";"9" in stead of 1;2;;4;;2;9
So building CSV files with 1;2;;4;;2;9 output is not possible ?
Maybe an idea to extend the FIELDS options to enable this ...
Regards, Cor
----- Original Message ----- From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Saturday, April 01, 2006 6:11 PM
Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
At 13:29 +0100 4/1/06, C.R.Vegelin wrote:
Hi everyone,
I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9
where NULL values are suppressed in the CSV file.
I tried the following alternatives:
a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By ''
Lines Terminated By '\r\n' ...
but this generates output like: 1;2;NULL;4;NULL;2;9
b) Select ... Into Outfile ... Fields Terminated By ';'
Lines Terminated By '\r\n' ...
but this generates output like: 1;2;\N;4;\N;2;9
Any idea how to get CSV rows like: 1;2;;4;;2;9 ?
Thanks for your time and effort.
Regards, Cor
You could use IFNULL() to map NULL values to the empty string:
mysql> set @x = null, @y = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select ifnull(@x,''), ifnull(@y,'');
+---------------+---------------+
| ifnull(@x,'') | ifnull(@y,'') |
+---------------+---------------+
| | 1 |
+---------------+---------------+
1 row in set (0.00 sec)
You'll need to apply this to each column that might contain NULL
values.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
[EMAIL PROTECTED]
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
