• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

select into outfile

 
Ranch Hand
Posts: 608
Eclipse IDE Spring Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Sagar Rohankar
Ranch Hand
Posts: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Strings don't contain null. They either are null or are strings.
 
Sagar Rohankar
Ranch Hand
Posts: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Scott Selikoff wrote:They either are null



Like this,
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes or even use JPA...if my javac could only find javax.ejb!!
 
When evil is afoot and you don't have any arms you gotta be hip and do the legwork, but always kick some ... tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic