Excel to MYSQL: Here's another technique
- Anjana. M
I had to upload pin codes in excel to database. After procrastinating on net for considerable time, I decided to find a quick solution... :) Though I am aware of available excel api’s, I dint want to spend time in reading api docs for breaking a thin twig. This method can be followed if you want to export a single column from excel to a table.
Steps:- So i copied the pin code column from the excel and pasted on to a notepad
- Save the file as ok.txt
- then I wrote program that will read a the file line by line and a simple string operation
- The program will generate a Sql script and will also execute the script and will insert the data into database.
"*********Sample output*********"
insert into tblpincode_zone(pincodes,zone)values(834002,9);
***********************************package beans;
import java.io.*;import java.sql.*;
class FileRead{
public static void main(String args[]){
try{Connection con=null;
Statement pst=null;String str="";
Class.forName("com.mysql.jdbc.Driver");con=DriverManager.getConnection("jdbc:mysql://192.169.70.45/application?user=root&password=root");
// Open the file that is the first// command line parameter
FileInputStream fstream = new FileInputStream("D:/ok.txt");// Get the object of DataInputStream
DataInputStream in = new DataInputStream(fstream);BufferedReader br = new BufferedReader(new InputStreamReader(in));
String strLine;//Read File Line By Line
while ((strLine = br.readLine()) != null) {pst=con.createStatement();
// Print the content on the consoleSystem.out.println ("insert into tblpincode_zone(pincodes,zone)values("+strLine+",9);"); str="insert into tblpincode_zone(pincodes,zone)values("+strLine+",9);";
pst.executeUpdate(str);}
//Close the input streamin.close();
}catch (Exception e){//Catch exception if anySystem.err.println("Error: " + e.getMessage());
}}
}
(The author, Anjana.M, is a Software Engineer at Binary Spectrum).