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]
Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
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]
how to suppress NULL values in SELECT ... INTO OUTFILE ?
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
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
