Conveting a Text File to a Table

hi,

I am trying to extract a bunch of data from a text file and insert it into a table. To do this i am using MySQL and java (netbeans). I have the code and it works but only for some entries from the text file. The entries it does not work for are the ones that have a space in the title, since i am using the space character as the delimiter. Another issue i have noticed is the fact that some of the titles have another single quote in them therefore it throws an error because it ruins the syntax of the SQL query. Below is all my code, hopefully somone can help me out with this:

These are some of the entries from the text file:
1000000103 50 4.5 "#1 Single" (2006)
2...1.2.12 8 2.7 "$1,000,000 Chance of a Lifetime" (1986)
11..2.2..2 8 5.0 "$100 Taxi Ride" (2001)
....13.311 9 7.1 "$100,000 Name That Tune" (1984)
3..21...22 10 4.6 "$2 Bill" (2002)
30010....3 18 2.7 "$25 Million Dollar Hoax" (2004)
2000010002 111 5.6 "$40 a Day" (2002)
2000000..4 26 1.6 "$5 Cover" (2009)
.0..2.0122 15 7.8 "$9.99" (2003)
..2...1113 8 7.5 "$weepstake$" (1979)
0000000125 3238 8.7 "'Allo 'Allo!" (1982)
1....22.12 8 6.5 "'Allo 'Allo!" (1982) {A Barrel Full of Airmen (#7.7)}
.....422.2 5 7.2 "'Allo 'Allo!" (1982) {A Bun in the Oven (#8.0)}
......4112 7 7.9 "'Allo 'Allo!" (1982) {A Fishy Sendoff (#9.5)}

Code:

---


import java.sql.*;
import java.io.*;
import java.util.*;

public class Main {
  public static void main(String args[]){

    try {
      FileInputStream fstream = new FileInputStream("C:/Users/Tudor/Documents/CMPT-354/rating.txt");
      //FileInputStream fstream = new FileInputStream("C:/Users/Tudor/Documents/CMPT-354/employees.txt");
      DataInputStream dstream = new DataInputStream(fstream);
      BufferedReader bf = new BufferedReader(new InputStreamReader(dstream));
      String data = null;
      Statement stmt;
      ResultSet rs;

      //Register the JDBC driver for MySQL.
      Class.forName("com.mysql.jdbc.Driver");

      //Define URL of database server for database named mytable on the localhos twith the default port number 3306.
      String url =
            "jdbc:mysql://localhost:3306/mytable";

      //Get a connection to the database
      Connection con =
                    DriverManager.getConnection(
                        url,"root", "turf238");

      //Display URL and connection information
      System.out.println("URL: " + url);
      System.out.println("Connection: " + con);

      //Get a Statement object
      stmt = con.createStatement();
      try{
        stmt.executeUpdate("DROP TABLE myTable");
      }catch(Exception e){
        System.out.print(e);
        System.out.println(
                  "No existing table to delete");
      }//end catch

      //Create a table in the database named mytable
      stmt.executeUpdate("CREATE TABLE mytable(distribution char(20)," + "votes int," + "rank float," + "title char(100));");
      while((data = bf.readLine()) != null){
        StringTokenizer stoken = new StringTokenizer(data," ");
        String distribution = stoken.nextToken();
        String v = stoken.nextToken();
        int votes = Integer.parseInt(v);
        String r = stoken.nextToken();
        float rank = Float.parseFloat(r);
        String title = stoken.nextToken();
        stmt = con.createStatement();
        //int row = stmt.executeUpdate("INSERT into mytable VALUES('"+distribution+"' , "+votes+""+" , "+rank+",'"+title+"'" + ");");
        int row = stmt.executeUpdate("INSERT into mytable VALUES('"+distribution+"',"+votes+","+rank+",'"+title+"');");
      }
      //Query the database, storing the result in an object of type ResultSet
      rs = stmt.executeQuery("SELECT * " +
                "from myTable");

      //Use the methods of class ResultSet in a loop to display all of the data in the database
      System.out.println("Display all results:");
      while(rs.next()){
        float f = rs.getInt("rank");
        String t = rs.getString("title");
        System.out.println("\tRank= " + f + "\tTitle = " + t);
      }//end while loop

      //Display the data in a specific row
      //System.out.println("Display row number 2:");
      //if( rs.absolute(2) ){
      //  int f = rs.getInt("rank");
      //  String t = rs.getString("title");
      //  System.out.println("\tRank= " + f + "\tTitle = " + t);
      //}//end if statement

      stmt.executeUpdate("DROP TABLE myTable"); //delete the table
      con.close();  //close the connection to the database
    }catch( Exception e ) {
      e.printStackTrace();
    }//end catch
  }//end main
}

---

the error i get is this
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Allo')' at line 1

i have no idea how to fix my code to allow for more quotations to appear in the title and not throw off the syntax of the SQL query

Thanks for any help
Tudor

well the problem where spaces and single quotes come in is inside the quoted string of the title.
could you do two kinds of parsing with substring, such as first find the index of the double quote ("), and take the substring before this index, and run it through the string tokenizer like you are already doing, but then take the string after the index of the quote up until the second double quote (") and consider it as all one string (allow spaces, don't tokenize on them that is),
and on this title string, perform escaping of single quotes, replacing all occurences 'f single quote with two single quotes, something like the replaceAll method of string

Code:

---

int firstQuoteIndex = data.indexOf("\"");
int lastQuoteIndex = data.lastIndexOf("\");
String title = data.substring(firstQuoteIndex,lastQuoteIndex);
title = title.replaceAll("'","''"); // now use this as the title instead of extracting from string tokenizer below.
StringTokenizer stoken = new StringTokenizer(data.substring(0,firstQuoteIndex," ");
.. and the other stuff here

---

And if the year and stuff after the second " is actually part of the title, then maybe build title as data.substring(firstQuoteIndex); (go to the end of string) then,

thank a lot for the help, thats exactly what i needed. Before you posted i actually went and did the whole thing using substrings rather than tokens and rand into the same problem with the apostophes and then i did it using regular expressions and i seem to have the same problem.

The way you explained makes sense but as you said the year and the following information is also part of the title and im not sure i understand how you are saying to have those included into the title variable? Could you help me out with that?:)

Also is it bettter/efficient to use regular expressions rather than tokens?

Thanks again
Tudor

Note also that you should be using FileReader/BufferedReader or Scanner to read text files.

Quote:

---

thank a lot for the help, thats exactly what i needed. Before you posted i actually went and did the whole thing using substrings rather than tokens and rand into the same problem with the apostophes and then i did it using regular expressions and i seem to have the same problem.

The way you explained makes sense but as you said the year and the following information is also part of the title and im not sure i understand how you are saying to have those included into the title variable? Could you help me out with that?

Also is it bettter/efficient to use regular expressions rather than tokens?

Thanks again
Tudor

---

actually i think i just fixed it by taking the lastQuoteIndex out of the substring, now i get the whole title including the year and everything. thanks a lot again.

The .txt file i am using is about 20MB and has a lot of records in but when i run my java program it always just keeps running and never prints anything. When i use a smaller file it works fine. Any ideas to what this problem is?

EDIT: i let the program run for about 3 minutes and now i get the following error
java.lang.StringIndexOutOfBoundsException: String index out of range: -1

EDIT AGAIN: sorry to edit again but i think i have found the problem some of the titles in the text file do not have quotation (") so when the program reaches those titles it gives an error since it cannot replace anything, so i tried removing those records that dont have the quotations("), now i have a heap space error, saying that java ran out of memory.

Any ideas how to fix these problems? Thanks a lot for any help

This is the error:
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space

I wonder if it is using up memory from each statement.
the while loop that reads the file appears to do a new statement every iteration

Code:

---

      while((data = bf.readLine()) != null){
  // the existing stuff that is parsing the line into pieces (this now works right)

// new try. block surrounding stmt. so we , open use, and close the connection each while iteration
      try {
  // the statement we had before
        stmt = con.createStatement();
        int row = stmt.executeUpdate("INSERT into mytable VALUES('"+distribution+"',"+votes+","+rank+",'"+title+"');");

    // new finally part to close the statement inside the while loop
    } finally {
        try {
          stmt.close();
        }
        catch (SQLException ex) { /* empty */ }
    }
 } // end existing while loop

---

Where I guess you don't even need to use the try..finally. I don't catch SQLException here so as to allow any errors to flow up to the caller method, as that is probably where they are currently handled.

Without closing the stmt object, it would rely on the java vm to garbage collect it to free up the memory it internally used. And this could be any unknown amount time before it gets to doing this, of perhaps it is that the resources that represented the row object that we updated, or in the connection handle itself cannot be reclaimed at all until they are closed. Actually i'm surprised it didn't fail with a database related exception, something like no more cursors to allocate..

For the problem with single quotes, you should be using a PreparedStatement so you don't have to worry about escaping such things.

You should not concatenate your SQL together like this.

You're also creating lots of statements and never closing them, which is a Bad Thing. Statement creationg and a close() should be paired up, with the close() in a finally block. Same goes for connections and resultsets. You will leak resources otherwise.

To do this properly (and neatly) you'll almost certainly have to restructure your code so you have methods handling individual tasks.

alright, i just tried what both of the above posts said (closed my statements and everything) but i still get the error:

java.lang.StringIndexOutOfBoundsException: String index out of range: -1

and its at line:

String title = data.substring(firstQuoteIndex);

Any ideas? i dont understand how this can go out of range, i removed the lastQuoteIndex so that it would print all of the remaining text after the quotations

Put System.out.println statements to see the values of data and firstQuoteIndex. You will then be able to work out for yourself what is actually happening. System.out.printlns can save lives.