select into outfile

[Duran Harris]

Hi all...

I'm using the following command to export data:
SELECT * FROM Agents INTO OUTFILE '/tmp/agentsUpload4.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

But I have \N and little squares(ASCII null bytes?)in the output where the tables contain nulls.
How can I tell mySQL to output these as ''(empty string) instead?

Thanks

[Sagar Rohankar]

Duran Harris wrote:
But I have \N and little squares(ASCII null bytes?)in the output where the tables contain nulls.

Can you copy paste the resulted file, generally MySQL put \N for NULL fields

Duran Harris wrote:
How can I tell mySQL to output these as ''(empty string) instead?

Any special need? You can do this using some multiple CASE statement in SELECT query

[Scott Selikoff]

MySQL replace() is your friend.

[Sagar Rohankar]

Scott Selikoff wrote:MySQL replace() is your friend.

Didn't worked out for me, I tried this,

SELECT replace(name, null, ' ') as NameCol FROM tableone;

It results into all the values from column 'name' to be null.

[Scott Selikoff]

Strings don't contain null. They either are null or are strings.

[Sagar Rohankar]

Scott Selikoff wrote:They either are null

Like this,

 SELECT replace(name, 'NULL', ' ') as NameCol from tableone;   

[Scott Selikoff]

Oh nevermind, I thought your issue was with the "\n" and other special characters, converting them to empty strings.

If you have entire fields that are null, your friend is IFNULL()

[Sagar Rohankar]

Scott Selikoff wrote:
If you have entire fields that are null, your friend is IFNULL()

You're Genius, How you got such a great knowledge ?
And this is working,

  SELECT ifnull(name, '') as NameCol from tableone;  

I bookmarked the site

[Scott Selikoff]

Thanks! Just years of practice I guess. If you want to read more, Jean and I tend to post JDBC blog entries from time to time.

[Duran Harris]

So in my case I could use:

SELECT IFNULL(name,'') FROM Table INTO OUTFILE '/tmp/agentsUpload4.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
My problem is that I am trying to save the data in a format that will work in excel....The server is on linux....

But I don't see how I can replace those little squares....I don't even know how to generate one using a keyboard!

[Scott Selikoff]

Overall, this is a good example of expecting too much from a database. For example, if you were to write a JDBC program to accomplish the same task you wouldn't need the database dependent IFNULL() statement. You could just check it by using "rs.getObject("...") == null)". This is the JDBC forum, and as such I recommend YOU USE JDBC to accomplish this task. You'll have a lot more control and won't run into all these database issues.

[Duran Harris]

Yes or even use JPA...if my javac could only find javax.ejb!!