Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select into outfile

 
Duran Harris
Ranch Hand
Posts: 608
Eclipse IDE Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQL replace() is your friend.
 
Sagar Rohankar
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott Selikoff wrote:MySQL replace() is your friend.

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



It results into all the values from column 'name' to be null.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Strings don't contain null. They either are null or are strings.
 
Sagar Rohankar
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott Selikoff wrote:They either are null


Like this,
 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,

I bookmarked the site
 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 608
Eclipse IDE Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 608
Eclipse IDE Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes or even use JPA...if my javac could only find javax.ejb!!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic