//
you're reading...
Java, Uncategorized

Exporting your database to csv file in java

Exporting your database to a csv file. Sounds very trivial. However I did not find any straightforward solution to this. I am using mysql version 5.x.

I looked into the workbench , into the import export options , found sql dump commands, to store into the csv u have to go and do it for every table or may be write a sql script which does that.What I was looking for was a single click to store the database dump in csv file.

Being a java programmer, I always tend to go for java coding rather than writing any sql script(it takes me far less time doing things in java than doing it in sql).So, the first step was google out some pre existing java class which does this kind of job.Was not convinced with what I got so decided to go with my own program.I use hibernate for interaction with database, but hql for my queries were not very clear to me so I had to write the platform specific(mysql in my case),learnt a bit of jdbc also. You may need to update those according to your database.So here is the working code, I have tried to comment logically so that it can be easily interpreted on reading.

package com.xbrl.dao;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import jxl.write.Label;

import com.utility.HibernateUtil;

public class DB2CSV {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		 
		  //usual database connection part
		  Connection con = null;
		  String url = "jdbc:mysql://localhost:3306/";
		  String db = "db_name";
		  String driver = "com.mysql.jdbc.Driver";
		  String user = "username";
		  String pass = "password";
		  FileWriter fw ;
		  try{
		  Class.forName(driver);
		  con = DriverManager.getConnection(url+db, user, pass);
		  Statement st = con.createStatement();
		  
		  //this query gets all the tables in your database(put your db name in the query)
		  ResultSet res = st.executeQuery("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' ");
		  
		  //Preparing List of table Names
		  List <String> tableNameList = new ArrayList<String>();
		  while(res.next())
		  {
			  tableNameList.add(res.getString(1));
		  }
		  
		  //path to the folder where you will save your csv files
		  String filename = "D:/db2csv/";
		  
		  //star iterating on each table to fetch its data and save in a .csv file
		  for(String tableName:tableNameList)
			{
				int k=0;
				
				int j=1;
				
				System.out.println(tableName);
				
				List<String> columnsNameList  = new ArrayList<String>();
				
				//select all data from table
				res = st.executeQuery("select * from xcms."+tableName);
				
				//colunm count is necessay as the tables are dynamic and we need to figure out the numbers of columns
				int colunmCount = getColumnCount(res);
				
				 try {
					fw = new FileWriter(filename+""+tableName+".csv");
					
					
					//this loop is used to add column names at the top of file , if you do not need it just comment this loop
					for(int i=1 ; i<= colunmCount ;i++)
					{
						fw.append(res.getMetaData().getColumnName(i));
						fw.append(",");
			
					}
					
					fw.append(System.getProperty("line.separator"));
					
					while(res.next())
					{
						for(int i=1;i<=colunmCount;i++)
						{
							
							//you can update it here by using the column type but i am fine with the data so just converting 
							//everything to string first and then saving
							if(res.getObject(i)!=null)
							{
							String data= res.getObject(i).toString();
							fw.append(data) ;
							fw.append(",");
							}
							else
							{
								String data= "null";
								fw.append(data) ;
								fw.append(",");
							}
							
						}
						//new line entered after each row
						fw.append(System.getProperty("line.separator"));
					}
					
					 fw.flush();
					  fw.close();
					
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
				
		  con.close();
		  }
		  catch (ClassNotFoundException e){
		  System.err.println("Could not load JDBC driver");
		  e.printStackTrace();
		  }
		  catch(SQLException ex){
		  System.err.println("SQLException information");
		  }
 }
	
	//to get numbers of rows in a result set 
	public static int  getRowCount(ResultSet res) throws SQLException
	{
		  res.last();
		  int numberOfRows = res.getRow();
		  res.beforeFirst();
		  return numberOfRows;
	}

	//to get no of columns in result set
	
	public static int  getColumnCount(ResultSet res) throws SQLException
	{
		return res.getMetaData().getColumnCount();
	}
	

}

Hope this helps you!!

Thanks
Gaurav

Advertisements

About Gaurav Mutreja

Well I think a lot !! Now I would like to speak a bit!

Discussion

14 thoughts on “Exporting your database to csv file in java

  1. Great post Gaurav!! I really appreciate your effort.

    Posted by dummiesmind | October 18, 2011, 11:57 pm
  2. Why not use some specialized library like supercsv or opencsv?

    Posted by tomekkaczanowski | January 28, 2013, 9:11 am
    • Hi,

      When I explored most of the open source libraries were focusing on parsing csv file , converting it to java pojos. My primary need was to get the data for all tables from database to an csv or excel file. That’s why I went with my own code.

      Thanks
      Gaurav

      Posted by Gaurav Mutreja | March 2, 2013, 6:56 am
  3. What is xcms? line 58.

    Posted by darshan | March 1, 2013, 11:05 am
  4. I really didnt understand what is xcms, can you explain more thank you

    Posted by alex | April 5, 2013, 9:36 am
  5. Really great job Gaurav. Helped me to make a significant change in JMeter 🙂

    Posted by Manisha | June 12, 2013, 11:27 am
  6. Thanx for this code snippet..I was searching for it from a long time..kudos 🙂

    Posted by Gaurav | October 1, 2013, 12:44 pm
  7. WHAT IS THE JAR FILE THAT NEEDED TO USE

    Posted by KEVIN CANLAS | November 19, 2013, 7:22 am
  8. Quite ingenious solution, but I think is the worst way to do it if you have a lot ot tables with millions of records.
    Reading record by record, and column by column!! … just think about it.

    Posted by Pabel cm | April 25, 2014, 11:04 pm
  9. Hey Gaurav, Thanks a loooooooooooot yaar. Awesome work.

    Posted by rakesh | March 19, 2015, 8:54 am
  10. Hey Gourav,
    Its very helpful thanks for this,
    Good job

    Posted by dilip | December 27, 2015, 11:30 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: