Thread: Import text file to mysql

Java code to Import text file to mysql
1) choose text file from computer
2) read that line by line
3) connect to mysql database
4) create table as an text file name
5) first line in text file is fields in mysql table. (problem here)
6) import data from second line to last line in that table

here is my code.

package com.mkyong.io;
import javax.swing.JFileChooser;
import javax.swing.filechooser.FileNameExtensionFilter;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class choosetextfile11 {

public static void main(String[] args)throws Exception {

JFileChooser chooser = new JFileChooser();
FileNameExtensionFilter filter = new FileNameExtensionFilter ("Text/Java files", "txt", "java");
chooser.setFileFilter(filter);

int returnVal = chooser.showOpenDialog(null);

if(returnVal == JFileChooser.APPROVE_OPTION) {
File f = chooser.getSelectedFile();
BufferedReader br = new BufferedReader (new FileReader(f));
String st = "";
while((st = br.readLine()) != null) {
System.out.println(st);
}

String Driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost/jayraj1";
String uName ="root";
String pwd = "basiqa";
Connection conn=null;

Class.forName(Driver).newInstance();
conn = DriverManager.getConnection(url, uName, pwd);
Statement stmt = conn.createStatement();


//Problem here
[B] String sql = "create table " + f.getName().toString() + "( FIELDS name first line in text file varchar(60),department varchar(5),subject_name varchar(25))";

System.out.println(sql);
stmt.executeUpdate(sql);
System.out.println("CreateTable sucessfully");


}

}

}




please help me!!

Hey Thank you so much for your quick replay

Try to write the code to choose text file and create table as an text file. but i got problem when i create fields in table fields name is in text file first line.. than have to import all data from text file to mysql from second line to end

<
 
package com.mkyong.io;
import javax.swing.JFileChooser;
import javax.swing.filechooser.FileNameExtensionFilter;
 
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class choosetextfile11 {
 
	public static void main(String[] args)throws Exception {
 
		JFileChooser chooser = new JFileChooser();
		FileNameExtensionFilter filter = new FileNameExtensionFilter ("Text/Java files", "txt", "java");
		chooser.setFileFilter(filter);
 
		int returnVal = chooser.showOpenDialog(null);
 
		if(returnVal == JFileChooser.APPROVE_OPTION) {
			File f = chooser.getSelectedFile();
			BufferedReader br = new BufferedReader (new FileReader(f));
			String st = "";
			while((st = br.readLine()) != null) {
				System.out.println(st);
			}
 
			String Driver = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://localhost/jayraj1";
			String uName ="root";
			String pwd = "basiqa";
			Connection conn=null;
 
			Class.forName(Driver).newInstance();
			conn = DriverManager.getConnection(url, uName, pwd);
			Statement stmt = conn.createStatement();
 
 
			//Problem here
			String sql = "create table " + f.getName().toString() + "( //problem here varchar(60),department varchar(5),subject_name varchar(25))";
			System.out.println(sql);
			stmt.executeUpdate(sql);
			System.out.println("CreateTable sucessfully");
 
 
		}
 
	}
 
}
 
>

and this is text file:

"First Name","Last Name","Address Line 1","Address Line 2","City","State","ZIP Code","IM Barcode","Sort Position","Tray Number"
"Stephen","Hight","105 Thurman St","","Carrollton","GA","30117-1955","ADADFTFATTTDADADDFFTAFFFTDDTADDFTFDAFAATDFA AATFATDTFTAAFFAADAFFDT",1,1
"Kimberly","Kendall","104 Thurman St","","Carrollton","GA","30117-1956","DAAFATDATAFTADDDTTTTADTFATFFDDFTAFDDATDDAAD FTATADADFDTFDAFDDATFDA",2,1
"James","Boles","108 Thurman St","","Carrollton","GA","30117-1956","DADFDDTFDAFADTFTATADTTDTFATAAFADTATDFTTFAAF TDDFDAFATDTTDTFTFDTTTT",3,1
"Douglas","Kelley","110 Thurman St","","Carrollton","GA","30117-1956","TFTTTAATDFDAFTDFADFDAFFTTFATATDAAFAATTAFDTD ATFAAFDFAFFTTDTDFFAADD",4,1
"Debora","Wallace","115 Thurman St","","Carrollton","GA","30117-1963","DFAAFTAAAFDFATTDDDFDAAAFFDTATTDFADAADAFFFAD ATDTFTTTFDADTDTAFAAAFF",5,1
"Sadie","Muse","116 Thurman St","","Carrollton","GA","30117-1964","TAADTTDTDFFDATTDFATTADATDTTTATTDFDFFDATDATD FTDFFDADFATADAAFFDTATT",6,1
"Jerrell","Dabney","116 Thurman St","","Carrollton","GA","30117-1964","AADTDFTFFFAADTAADTADTTFDATAFDAFDDAATTFTFTTA TDAATFAATTTFTTTAFTTFAA",7,1

Java programming. I din't finish the code yet but I don't how to create table fields its first line in text file..
so

Problem in this line:

String sql = "create table " + f.getName().toString() + "( //problem here varchar(60),department varchar(5),subject_name varchar(25))";

its create table but fields it should be create automatically in table as in text file first row.
so i don't know how to write the code for that. please help me..

I know how to add fields manually in table. but I want to write the code to create fields itself as in text file first line is columns in table. i don't have to go to code every time and add columns everytime.. suppose i have one text to import in mysql it has 100 fields so i don't want to add 100 fields manually. it will create fields automatically in table.

Well, i know how to create a table in mysql database java code. I already write the code for that. as you seen in my code.
I got problem when I try to create fields in table as a first line in text file. I don't have to type the columns every time.

Please help me.