We help IT Professionals succeed at work.

Export fields from a ResultsSet to a new table similar to FoxPro

Vanavah Edwards
on
I want to take the colums, rows from my ResultSet and automatically create and export to a new table similar to the FoxPro command  “Export to tablename type dbf or csv”
Comment
Watch Question

Awarded 2011
Awarded 2011
Commented:
I'm not sure about FoxPro but this is a simple example of dumping data from table to CSV file -
this exampel is in sservelet environment but the same will work in any java program


http://www.roseindia.net/servlets/jdbccsv.shtml
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
 
public class JdbcCsvFile extends HttpServlet { 
public  void doGet (HttpServletRequest request,
  HttpServletResponse response) 
  throws ServletException,IOException  {
  String filename = "c:\\csv\\myjdbcfile.csv";
  Connection conn = null;
  String url = "jdbc:mysql://localhost:3306/";
  String dbName = "user_register";
  String driver = "com.mysql.jdbc.Driver";
  String userName = "root"; 
  String password = "root";
  Statement stmt;
  try
  {
  PrintWriter out = response.getWriter();
  FileWriter fw = new FileWriter(filename);
  fw.append("Employee Code");
  fw.append(',');
  fw.append("First Name");
  fw.append(',');
  fw.append("Last Name");
  fw.append('\n');

  Class.forName(driver).newInstance();
  conn = DriverManager.getConnection
   (url+dbName,userName,password);
  String query  = "select * from employee_details";
  stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(query);
  while(rs.next())
  {
  fw.append(rs.getString(1));
  fw.append(',');
  fw.append(rs.getString(2));
  fw.append(',');
  fw.append(rs.getString(3));
  fw.append('\n');
  }
  fw.flush();
  fw.close();
  conn.close();
  out.println
   ("<b>You are Successfully Created Csv file.</b>");
  } catch (Exception e) {
  e.printStackTrace();
  }
  
  }
}

Open in new window

Awarded 2011
Awarded 2011

Commented:

If you want to go ore sophisticated - download OPEN CSV
and they have a special method which dumps ResultSet to CSV


http://opencsv.sourceforge.net/#sql-integration

java.sql.ResultSet myResultSet = ....
writer.writeAll(myResultSet, includeHeaders);
Awarded 2011
Awarded 2011

Commented:

This is the source of OpenCSV
http://opencsv.sourceforge.net/

http://sourceforge.net/projects/opencsv/

a useful package when it comes to writing the CSV files -espcially if your data have embedded commas, etc
when it  beciomes not so straightforward

And aslo dumping the whole result set to csv in one line - useful thing

Author

Commented:
Thanks I will try the program code above and get back to you.  It looks like the solution.  I have also posted for a visual calender for a datafield on the internet.
Awarded 2011
Awarded 2011

Commented:

vanavah,
Pleas, dont post in that old trail
http://www.experts-exchange.com/Programming/Languages/Java/Q_27411392.html

which was initiated (and then closed ) more than a month ago by another person - I mentioned it for you to take solution from there not to use it for posting.

I posted several replies to your questions in your trail related to date checking:

http://www.experts-exchange.com/Programming/Languages/Java/Q_27476343.html?cid=1131 

Use your own questions to post stuff related to your questions