Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 7 of 7

Thread: Import text file to mysql

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    19
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default 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!!


  2. #2
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    25,042
    Thanks
    63
    Thanked 2,708 Times in 2,658 Posts

    Default Re: Import text file to mysql

    Please explain what problems you are having.

    Please edit your post and wrap your code with code tags:
    [code=java]
    <YOUR CODE HERE>
    [/code]
    to get highlighting and preserve formatting.
    If you don't understand my answer, don't ignore it, ask a question.

  3. The Following User Says Thank You to Norm For This Useful Post:

    jayraj (April 6th, 2013)

  4. #3
    Junior Member
    Join Date
    Apr 2013
    Posts
    19
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default Re: Import text file to mysql

    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

  5. #4
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    25,042
    Thanks
    63
    Thanked 2,708 Times in 2,658 Posts

    Default Re: Import text file to mysql

    Is this a java programming problem or a SQL problem?
    If you don't understand my answer, don't ignore it, ask a question.

  6. #5
    Junior Member
    Join Date
    Apr 2013
    Posts
    19
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default Re: Import text file to mysql

    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..

  7. #6
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    25,042
    Thanks
    63
    Thanked 2,708 Times in 2,658 Posts

    Default Re: Import text file to mysql

    its create table but fields it should be create automatically in table
    This looks like a SQL problem.

    Are you getting a compiler error with the sql String?
    create table is a SQL command/statement and has nothing to do with java programming.
    If you don't understand my answer, don't ignore it, ask a question.

  8. #7
    Junior Member
    Join Date
    Apr 2013
    Posts
    19
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default Re: Import text file to mysql

    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.

Similar Threads

  1. open multiple text file to input to mysql
    By csharp100 in forum JDBC & Databases
    Replies: 2
    Last Post: November 26th, 2012, 09:58 PM
  2. Import Data from Excel to Mysql Database
    By StarRocks in forum JDBC & Databases
    Replies: 2
    Last Post: August 29th, 2012, 11:40 PM
  3. Replies: 0
    Last Post: December 15th, 2011, 01:14 PM
  4. Import data form text into Jtable
    By redpower1989 in forum AWT / Java Swing
    Replies: 10
    Last Post: November 24th, 2011, 08:35 AM
  5. java program to copy a text file to onother text file
    By francoc in forum File I/O & Other I/O Streams
    Replies: 3
    Last Post: April 23rd, 2010, 03:10 PM