Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

select into outfile

 
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
 
Ranch Hand
Posts: 2908
1
Spring Java 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
 
author
Posts: 4173
29
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQL replace() is your friend.
 
Sagar Rohankar
Ranch Hand
Posts: 2908
1
Spring Java 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
Posts: 4173
29
jQuery Eclipse IDE Java
  • 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: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Scott Selikoff wrote:They either are null



Like this,
 
Scott Selikoff
author
Posts: 4173
29
jQuery Eclipse IDE Java
  • 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: 2908
1
Spring Java 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
Posts: 4173
29
jQuery Eclipse IDE Java
  • 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
Posts: 4173
29
jQuery Eclipse IDE Java
  • 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!!
 
I RELEASE YOU! (for now .... ) Feel free to peruse this tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic