MySQL ODBC Connector - how to suppress NULL values in SELECT ... INTO OUTFILE ?

Author

how to suppress NULL values in SELECT ... INTO OUTFILE ?

C.R.Vegelin

2006-04-01, 7:25 am

------ =_NextPart_000_0005_
01C65590.4794BF20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 ''=20
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
------ =_NextPart_000_0005_
01C65590.4794BF20--

Paul DuBois

2006-04-01, 11:26 am

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/mysql? unsub...sie.nctu.edu.tw

C.R.Vegelin

2006-04-02, 3:26 am

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" <paul@mysql.com>
To: "C.R.Vegelin" <cr.vegelin@hetnet.nl>; <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:
>
> 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/mysql? unsub...sie.nctu.edu.tw

Jorrit Kronjee

2006-04-02, 7:27 am

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" <paul@mysql.com>
> To: "C.R.Vegelin" <cr.vegelin@hetnet.nl>; <mysql@lists.mysql.com>
> Sent: Saturday, April 01, 2006 6:11 PM
> Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
>
>
>
>

--
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
j.kronjee@infopact.nl
http://www.infopact.nl/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

C.R.Vegelin

2006-04-02, 7:27 am

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" <j.kronjee@infopact.nl>
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:
>
>
> --
> 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
> j.kronjee@infopact.nl
> http://www.infopact.nl/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=cr.vegelin@hetnet.nl
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links