File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes select into outfile Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select into outfile" Watch "select into outfile" New topic
Author

select into outfile

Duran Harris
Ranch Hand

Joined: Nov 09, 2008
Posts: 598

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

===>SCJP 1.5(72%)<===
==>SCWCD1.5(76%)<===
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

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


[LEARNING bLOG] | [Freelance Web Designer] | [and "Rohan" is part of my surname]
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

MySQL replace() is your friend.


My Blog: Down Home Country Coding with Scott Selikoff
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Strings don't contain null. They either are null or are strings.
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Scott Selikoff wrote:They either are null


Like this,
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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

Joined: Feb 19, 2008
Posts: 2902
    
    1

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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

Joined: Nov 09, 2008
Posts: 598

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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

Joined: Nov 09, 2008
Posts: 598

Yes or even use JPA...if my javac could only find javax.ejb!!
 
 
subject: select into outfile
 
Similar Threads
odd n even numbers program
return to wrong page
FileWriter. From the API.
Writing serialized objects to files
Export into file, sql query to insert data