When it comes to transfer data from excel worksheet to PostgreSQL, the task is not that easy.
I tried to export file to CSV but it doesn't work because there were some columns which were not containing the data!!!
I tried to export file to CSV but it doesn't work because there were some columns which were not containing the data!!!
And even one problem exist, Column type. In Java using ResultSet I successfully fetched data from excel as ODBC database but in some column where it contains text in some rows while in some rows it contained numbers!! So Java 'getString(column)' method did not worked!!
So finally I imported that excel file into MS-Access and it worked, and then from there I read that data and inserted into PostgreSQL.
To fetch column from excel file as ODBC backend, following code will be useful.
select * from [WorksheetName$]To fetch only some data
select * from [WorksheetName$A7:F10]or
select First_row_data from [WorksheetName$]or if your excel file contains data in all columns then you can export it as CSV file and then can use PostgreSQL's COPY command.
e.g.
COPY FROM FILE 'c:/data/excel.csv' USING DELIMITERS ',';
Hi Sachin
ReplyDeleteHere is one more Way
/*This Is Directly data save into database Program is Partialy Run.But Some versions may be not Run.*/
public class CSVFileSave{
public static void main (String[] args){
Connection conn =null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://192.168.1.150:3306/TestDB","dev","");
conn.setAutoCommit(false);
System.out.println(conn.isClosed());
Statement stmt = conn.createStatement();
String filename = "C:\\Documents and Settings\\SS2\\Desktop\\ProductList.csv";
System.out.println("LOAD DATA INFILE '" +filename+ "' INTO TABLE CSVTest FIELDS TERMINATED BY ','");
stmt.executeUpdate("LOAD DATA INFILE '" +filename+ "' INTO TABLE CSVTest FIELDS TERMINATED BY ','");
System.out.println("THE DATA IS INSERTED PROPERLY ....!!!!");
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
finally{
//conn.close();
System.out.println("Finally the DATA ....!!!!");
try {
if(conn != null)
conn.close();
} catch(SQLException e) {}
}
}
}//class end
Thanks Raj for this information.
ReplyDelete