Import Table From CSV File

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
*
* Import employee table to csv file.
*
*
*/
public class JDBCTest4 {

// used for db connection
private static Connection conn;

private static BufferedReader brFile;

private static final String CSV_FILE_NAME = "C:\\emp_csv.txt";

// at loading of class, connects to DB
static {
// make connection with Employee Database
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// connectivity 1 : Require DSN named : dsn
// conn = DriverManager.getConnection("jdbc:odbc:dsn" ,"","");

// connectivity 2 : No requirement of DSN, direct connectivity to
// input file name
String sDBQ = "D:\\emp.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="
+ sDBQ + ";DriverID=22;READONLY=true";
conn = DriverManager.getConnection(database, "", "");

// connection to file to write data
brFile = new BufferedReader(new InputStreamReader(
new FileInputStream(CSV_FILE_NAME)));
} catch (ClassNotFoundException cnfe) {
System.out.println("Class Not Found Exception");
cnfe.printStackTrace();
} catch (SQLException sqle) {
System.out.println("SQL Exception");
sqle.printStackTrace();
} catch (FileNotFoundException fnfe) {
System.out.println("FileNotFound Exception");
fnfe.printStackTrace();
}
}

/**
* execution begins from here
*
* @param args
* command line arguments
*/
public static void main(String[] args) {
importEmpData();
}

/**
* gets employees table data records and calls the writeDataInFile() to
* store data
*
*/
public static void importEmpData() {
try {
// get data from file
Statement stmtObj = conn.createStatement();
ResultSet rsObj = stmtObj
.executeQuery("select * from employees where 11<2");

// to store header
Vector vecHeader = new Vector();

// display header
for (int i = 1; i <= rsObj.getMetaData().getColumnCount(); i++) {
vecHeader.add(rsObj.getMetaData().getColumnName(i));
}

// fetch header from file
Vector vecData = readDataFromFile();

// check for vaild header
// flag for same header
boolean blHeder = true;
// counter
int intCnt = 0;

// check size of both header
if (vecData.size() != vecHeader.size())
blHeder = false;
else {
// if size are same then check field naems
for (String strH : vecHeader) {
if (!strH.equals(vecData.elementAt(intCnt++))) {
blHeder = false;
break;
}
}
}

// if header is same then read data from file and write in db
if (blHeder) // same header
{
// record counter
int intRecCounter = 0;
// / read a record from file and check for it
while ((vecData = readDataFromFile()) != null
&& vecData.size() > 0) {

// increment counter
intRecCounter++;

// extract data from vector
String strEno = vecData.elementAt(0);
String strEfn = vecData.elementAt(1);
String strEln = vecData.elementAt(2);
String strEct = vecData.elementAt(3);
String strEpc = vecData.elementAt(4);
String strEsal = vecData.elementAt(5);
String strEdh = vecData.elementAt(6);

System.out.println("vecdata : " + vecData);

// insert record in DB
int intRes = stmtObj
.executeUpdate("insert into employees values ('"
+ strEno + "','" + strEfn + "','" + strEln
+ "','" + strEct + "','" + strEpc + "','"
+ strEsal + "','" + strEdh + "')");

}

// display msg
System.out.println("\n\t" + intRecCounter + " : Records"
+ " Inserted Successfully!!");

// close statement, file and db connectoin
stmtObj.close();
brFile.close();
conn.close();
} else // different heaader
{
System.out.println("Invalid Header Found!!");
System.out.println("Can't Import Data From Given File!!");
}

} catch (SQLException sqle) {
sqle.printStackTrace();
} catch (IOException ioe) {
ioe.printStackTrace();
} finally {
conn = null;
}
}

/**
* writes DB table data in csv file
*
* @param vecData
* input vector that would be stored in csv file
*/
public static Vector readDataFromFile() {

try {
// temp to store data from file
Vector vecData = new Vector();

// read line from file
String strLine = brFile.readLine();

// check if eof reached
if(strLine==null || strLine.length()<=0)
return new Vector();

// split line by comma and store in vector
for (String strTemp : strLine.split(","))
vecData.add(strTemp);

//return data in vector format
return vecData;

} catch (IOException ioe) {
System.out.println("IOException occured!!");
ioe.printStackTrace();
return new Vector();
}
}
}

Export Table To CSV File

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
* Export employee table to csv file.
*

*
*/
public class JDBCTest3 {

// used for db connection
private static Connection conn;

private static BufferedWriter bwFile;

private static final String CSV_FILE_NAME="C:\\emp_csv.txt";

// at loading of class, connects to DB
static {
// make connection with Employee Database
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// connectivity 1 : Require DSN named : dsn
// conn = DriverManager.getConnection("jdbc:odbc:dsn" ,"","");

// connectivity 2 : No requirement of DSN, direct connectivity to
// input file name
String sDBQ = "D:\\emp.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="
+ sDBQ + ";DriverID=22;READONLY=true";
conn = DriverManager.getConnection(database, "", "");

// connection to file to write data
bwFile = new BufferedWriter(new OutputStreamWriter(
new FileOutputStream(CSV_FILE_NAME)));

} catch (ClassNotFoundException cnfe) {
System.out.println("Class Not Found Exception");
cnfe.printStackTrace();
} catch (SQLException sqle) {
System.out.println("SQL Exception");
sqle.printStackTrace();
} catch (FileNotFoundException fnfe) {
System.out.println("FileNotFound Exception");
fnfe.printStackTrace();
}
}

/**
* execution begins from here
*
* @param args
* command line arguments
*/
public static void main(String[] args) {
getEmpData();
}

/**
* gets employees table data records and calls the writeDataInFile() to
* store data
*
*/
public static void getEmpData() {
try {
Statement stmtObj = conn.createStatement();
ResultSet rsObj = stmtObj.executeQuery("select * from employees");

// to store header
Vector vecData = new Vector();

// display header
for (int i = 1; i <= rsObj.getMetaData().getColumnCount(); i++) {
vecData.add(rsObj.getMetaData().getColumnName(i));
}

// store header in file
writeDataInFile(vecData);

// display data
while (rsObj.next()) {
// clear vector
vecData.clear();
for (int i = 1; i <= rsObj.getMetaData().getColumnCount(); i++) {
// add field data of records in vector
vecData.add(rsObj.getString(i));
}
// write to file
writeDataInFile(vecData);
}

System.out.println("Data Write at " + CSV_FILE_NAME + " file successfully!!" );

// close file and db connectoin
bwFile.close();
conn.close();
} catch (SQLException sqle) {
sqle.printStackTrace();
} catch (IOException ioe) {
ioe.printStackTrace();
} finally {
conn = null;
}
}

/**
* writes DB table data in csv file
*
* @param vecData
* input vector that would be stored in csv file
*/
public static void writeDataInFile(Vector vecData) {

try {
for (int i = 0; i < vecData.size(); i++) {
bwFile.write(vecData.elementAt(i));
if (i < (vecData.size() - 1))
bwFile.write(",");
else
bwFile.newLine();
}
bwFile.flush();
} catch (FileNotFoundException fnfe) {
System.out.println("File Not Found!!");
fnfe.printStackTrace();
} catch (IOException ioe) {
System.out.println("IOException occured!!");
ioe.printStackTrace();
}
}
}