10 Replies - 1758 Views - Last Post: 07 April 2013 - 06:35 AM Rate Topic: -----

#1 jayraj92  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 13

Import text file data to mysql

Posted 06 April 2013 - 03:55 PM

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

Attached File(s)


This post has been edited by Atli: 06 April 2013 - 04:06 PM
Reason for edit:: Use [code] tags when posting code!


Is This A Good Question/Topic? 0
  • +

Replies To: Import text file data to mysql

#2 g00se  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2560
  • View blog
  • Posts: 10,690
  • Joined: 20-September 08

Re: Import text file data to mysql

Posted 06 April 2013 - 04:17 PM

Why not just use mysqlimport?
Was This Post Helpful? 0
  • +
  • -

#3 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,765
  • Joined: 08-June 10

Re: Import text file data to mysql

Posted 06 April 2013 - 04:22 PM

Hey.

Well, the CREATE query is obviously not going to work like that. But I assume you know this already.

When you say: "first line in text file is fields in mysql table.", what does that mean exactly? How is that line formatted? Comma separated? Spaces? Tabs? Quotes?
(Edit: Didn't notice the appended file. A comma separated, value quoted CSV file it is then :))

And where do the field data types come from? Is that data somehow serialized into that one line? - If you are thinking you'll just use some string type for all of them, then you should really really rethink that strategy. In no way is it acceptable to just store everything as strings in a database like MySQL. Even if the data itself is coming from CSV files, the database structure should be aware whether a field has a number, a string, a date value, etc... So, the database structure has to be set up differently, with all this meta-data along with it.

View Postg00se, on 06 April 2013 - 11:17 PM, said:

Why not just use mysqlimport?

As far as I know, no command like that will create the table from CSV files, only import them into existing tables.

This post has been edited by Atli: 06 April 2013 - 04:24 PM

Was This Post Helpful? 0
  • +
  • -

#4 g00se  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2560
  • View blog
  • Posts: 10,690
  • Joined: 20-September 08

Re: Import text file data to mysql

Posted 06 April 2013 - 04:25 PM

Quote

As far as I know, no command like that will create the table from CSV files, only import them into existing tables.
Well i didn't mean just mysqlimport ;)
Was This Post Helpful? 0
  • +
  • -

#5 jayraj92  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 13

Re: Import text file data to mysql

Posted 06 April 2013 - 04:31 PM

Hey, Thank you so much for you quick replay.

try to write java code to do 1) choose txt file from computer, 2) connect to database mysql, 3) create new table as an text file name in database mysql, 4) Look in Text file, first line is a fields in that table separated by Commas and create that fields in table automatically and
Than import data in created table from second line in text file to last line automatically.

i write code but i don't know how to create a fields in table as a first line in text file.

example:
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","ADADFTFATTTDADADDFFTAFFFTDDTADDFTFDAFAATDFAAATFATDTFTAAFFAADAFFDT",1,1
"Kimberly","Kendall","104 Thurman St","","Carrollton","GA","30117-1956","DAAFATDATAFTADDDTTTTADTFATFFDDFTAFDDATDDAADFTATADADFDTFDAFDDATFDA",2,1
"James","Boles","108 Thurman St","","Carrollton","GA","30117-1956","DADFDDTFDAFADTFTATADTTDTFATAAFADTATDFTTFAAFTDDFDAFATDTTDTFTFDTTTT",3,1
"Douglas","Kelley","110 Thurman St","","Carrollton","GA","30117-1956","TFTTTAATDFDAFTDFADFDAFFTTFATATDAAFAATTAFDTDATFAAFDFAFFTTDTDFFAADD",4,1
"Debora","Wallace","115 Thurman St","","Carrollton","GA","30117-1963","DFAAFTAAAFDFATTDDDFDAAAFFDTATTDFADAADAFFFADATDTFTTTFDADTDTAFAAAFF",5,1
"Sadie","Muse","116 Thurman St","","Carrollton","GA","30117-1964","TAADTTDTDFFDATTDFATTADATDTTTATTDFDFFDATDATDFTDFFDADFATADAAFFDTATT",6,1



--

First line is fields in table. and from second line to last is data to import in created tables and fields

I got problem:
String sql = "create table " + f.getName().toString() + "( FIELDS name are first line in text file varchar(60),department varchar(5),subject_name varchar(25))";



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.

Please help.

This post has been edited by Atli: 06 April 2013 - 04:33 PM
Reason for edit:: [code] tags, man. [code] tags!

Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,765
  • Joined: 08-June 10

Re: Import text file data to mysql

Posted 06 April 2013 - 04:32 PM

View Postg00se, on 06 April 2013 - 11:25 PM, said:

Quote

As far as I know, no command like that will create the table from CSV files, only import them into existing tables.
Well i didn't mean just mysqlimport ;)

Ahh I see :)

You're right, of course, that it would be the easiest way to import the CSV data. Would probably be easier still to use it's SQL command equivalent in this situation; the LOAD DATA INFILE command. Since there is already an open SQL connection in the code.
Was This Post Helpful? 0
  • +
  • -

#7 jayraj92  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 13

Re: Import text file data to mysql

Posted 06 April 2013 - 04:37 PM

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.
Was This Post Helpful? 0
  • +
  • -

#8 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,765
  • Joined: 08-June 10

Re: Import text file data to mysql

Posted 06 April 2013 - 04:48 PM

View Postjayraj92, on 06 April 2013 - 11:31 PM, said:

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.

Like I mentioned before, there is an inherent flaw in this strategy. There is no good way to create the fields without knowing which data type the field stores. Knowing the name of the fields is only part of the information you need to create the fields.

Unless you're planing to go through the data itself and try to infer the data types from the data, then I strongly advise against this!

However, if you are dead set on doing this, the logic is fairly straight forward.

  • Fetch the first line from the text file.
  • Parse that line so that the field names end up in an array. I'm not familiar enough with Java to know if there is some built in CSV parsing classes in the API, but in this case it's not exactly difficult to parse this using normal string manipulation methods.
  • Construct the CREATE query's field list using the field names from the array. You'd have to pick a default data type. (See my earlier notes!)


In simple pseudo code, that might look like:
// To get the field name list
Get the first line.
Split the first line on commas into an array.
Go through each element in the array and strip of the first and last quote marks.

// To create the CREATE query
Set up a string with the first part of the CREATE query, up to and including
the opening of the field definition parenthesis.

FOR each field parsed from the first line of the file:
	Create a field definition string and inject this field name into it.
	Add the field definition to the CREATE query string.
END FOR

Add the end of the CREATE query string, closing the field definition part.

Execute the CREATE query.

// To import the data
Either use LOAD DATA INFILE to load the file into the new table,
or create code to read in the file one by one and follow a similar
process as with the CREATE query to create a INSERT query based on
each line.


Was This Post Helpful? 0
  • +
  • -

#9 jayraj92  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 13

Re: Import text file data to mysql

Posted 06 April 2013 - 04:52 PM

So I can't write a java code. I don't know how create fields in table as first line in text file. command i don't know how to write that code. I know everything else.
Please help me. I need to write java code for that.
Was This Post Helpful? 0
  • +
  • -

#10 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3637
  • View blog
  • Posts: 5,765
  • Joined: 08-June 10

Re: Import text file data to mysql

Posted 06 April 2013 - 07:23 PM

My last post explains how that kind of code can be written. I'm not going to write the Java code for you, but if you go over what I posted in my last post carefully, you should be able to do it on your own.

If something about my description is unclear, I'll be happy to elaborate on it. You just need to tell me what is unclear.
Was This Post Helpful? 0
  • +
  • -

#11 jayraj92  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 06-April 13

Re: Import text file data to mysql

Posted 07 April 2013 - 06:35 AM

Thank you so for your replay. I am not saying to write the code for me. but I want to learn that how can i write the code for that. so I just need help. Or can you tell me the command. Well, I don't know where i need to put the those code. I am gor problem here String

sql = "create table " + f.getName().toString() + "( FIELDS name are first line in text file varchar(60),department varchar(5),subject_name varchar(25))";

Fields name. First line is fields in mysql table. but I don't know where in code i can put those command.. to look over first line and make fields??

Please help me!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1