Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Feb 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    [Resolved] Taking a resultset from a SQL query, and writing it to a CSV

    Hi all,

    I'm creating a class which pops up a Save File window, and when the "Save" button is pressed, I'd like a CommaSeparatedValue file to be created with the filename that was input by the user on the Save File window. This CSV would simply contain the results of a SQL query. Problem is, I have no idea how to actually save the file, or how to write the CSV. Could someone guide me on how to do this? Ideally, I'd also like to include the column headers of the resultset. Someone told me I had to use Metadata somehow, but I don't really know how to manipulate that either.

    Code follows:

    Code:
    package hottbooks.reports;
    
    import hottbooks.exception.HOTTError;
    import hottbooks.reports.dao.ReportDataDAO;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.util.Date;
    import java.sql.*;
    import java.text.SimpleDateFormat;
    
    import javax.sql.*;
    import hottbooks.db.*;
    import java.util.Formatter;
    
    import java.io.*;
    
    
    import org.apache.log4j.Logger;
    
    public class Report1099Report extends GeneralDAO {
    
    	private static Logger logger = Logger.getLogger(Report1099Report.class);
    	
    	private Date startDate = null;
    	private Date endDate = null;
    	
    	
    	
    	
    	public Report1099Report() throws HOTTError{
    		super(null, null, null, null, null);
    	}
    	
    	//You will see later on in this constructor some weird looking Update row checks and loops.  
    	//This needs to be done because Microsoft's JDBC driver doesn't adhere to certain standards and 
    	//returns update rows as resultsets.  So we have to filter those out; Otherwise, this 
    	//program thinks there is no resultset.
    	//The meat of what is relevant is in the ELSE portion of the WHILE(TRUE) loop
    
    	public Report1099Report(Date sDate, Date eDate) throws HOTTError{
    		super(null, null, null, null, null);  //the superconstrucor is irrelevant for this situation
    		startDate = sDate;
    		endDate = eDate;
    		System.out.println(startDate);
    		ResultSet rs = null;
    		CallableStatement cs = null;
    		
    			try{
    			
    			String storedProc = "{call Report_1099( ?, ?)}";
    			cs = getConn().prepareCall(storedProc);   //getConn() is a method in GeneralDAO (which this extends from) that gets the connection object I need)
    			cs.setDate(1, new java.sql.Date(startDate.getTime()));
    			cs.setDate(2, new java.sql.Date(endDate.getTime()));
    			boolean returnedResults = cs.execute();
    			
    			while(true){
    				if (! returnedResults){
    					int updateCount = cs.getUpdateCount();
    				
    					if(updateCount == -1){
    					System.out.println("Done handling results");
    					break;
    					}
    				}  
    				else {
    					rs = cs.getResultSet();
    					System.out.println("Resultset finally found");
    					
    					javax.swing.JFileChooser saver = new javax.swing.JFileChooser();
    					
    			        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
    			        String fileName = saver.getCurrentDirectory().getAbsolutePath() + File.separator + "1099Report." + formatter.format(startDate) + ".to." + formatter.format(endDate) + ".csv";
    			        File newFile = new File(fileName);
    			        saver.setSelectedFile(newFile);
    			        
    			        int saveable = saver.showSaveDialog(null);
    			        if (saveable==javax.swing.JFileChooser.APPROVE_OPTION) {
    			            File file = saver.getSelectedFile();
    			            
    			            try {
    			            				//This is where I would attempt to create the .csv
    			            				while (rs.next()){
    			            							System.out.println(rs.getObject(1));
    			            						}
    					
    			            }
    			           catch(Exception ex){
    			        	   System.out.println("Error while writing file");
    			           }
    			            
    			            finally{
    			            	
    			            }
    					
    			        }
    					
    					
    				
    			        rs.close();
    				}
    			
    			
    		
    			returnedResults = cs.getMoreResults();
    			}
    		
    			
    		
    			}
    			catch(SQLException ex){
    				System.out.println(ex.toString());
    			}
    			finally{
    				try{
    					
    					rs.close();
    				
    				cs.close();
    				
    				System.out.println("succesful Finally of Report1099Report");
    				
    				}
    				catch(Exception ex){
    					System.out.println("In Finally of Report1099Report" + ex.toString());
    				}
    			}
    			
    	}
    	
    	
    }

    If it matters, the resultset of the stored procedure [Report_1099] always has 8 columns

    If you would like to see what the Save window looks like that is generated, you can see it here:
    http://img40.imageshack.us/img40/1695/saveboxe.png

    Thanks!

    -Rob V.
    Last edited by sygg13; 02-10-2011 at 10:04 PM.

  • #2
    New Coder
    Join Date
    Feb 2011
    Posts
    48
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Here is an example solution for data export into CSV file:
    http://www.mkyong.com/java/how-to-ex...csv-file-java/

    You can use the FileWriter or the BufferedWriter class to export your data.

    So, first task is to create a BufferedWriter, the second is to prepare your data, third is the actual output.

    Don't forget to close your stream when the exportation finished.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •