Good morning,

I'm facing dificulties to understand what i'm doing wrong!
I'm trying to send the contents of a file...


SPI01
184-25-03-T-01-R.F.T.1.47e85ab1spicsv, 4609, 2008.03.25, 09:51:45 AM
184-25-03-T-01-R.F.T.1.47e8966dspicsv, 0, 2008.03.25, 14:06:38 PM
184-25-03-T-01-R.F.T.10.47e863easpicsv, 48, 2008.03.25, 10:31:06 AM
184-25-03-T-01-R.F.T.10.47e897f4spicsv, 0, 2008.03.25, 14:13:08 PM

into a database "spimon" with a table LogSPI01 with 4 fields but later will have more...
the source code is here:

package spilog;
/*
 * 1.Read the file line by line,
 * 2.Split the line into multiple words using StringTokenizer and Tab ("\t") as delimeter.
 * 3.Create insert query (if the table is existing, otherwise create it) and
 * add the query to a batch using, statement.addBatch(query);
 * 4.Finally execute the batch, statement.executeBatch();
 * */

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

public class test {

		public static void main (String[] args) {
			String TABLE_NAME = "LogSPI01",
			HOST = "jdbc:odbc:spimon",
			DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver",
			FILENAME = "F:\\SPI01.log";
			try {
				// connect to db
				Class.forName(DRIVER).newInstance();
				Connection con = DriverManager.getConnection(HOST,"","");
				PreparedStatement ps = null;
				// open text file
				BufferedReader in = new BufferedReader(new FileReader(FILENAME));
				String line=in.readLine();
				while((line=in.readLine()) != null) {
					System.out.println(line);
					// read and parse a line
					StringTokenizer tk = new StringTokenizer(line,"\t");
					if(tk.countTokens()>=10) {
												
						String PCB  	= tk.nextToken(),
						FAIL_COUNT		= tk.nextToken(),
						DATE 			= tk.nextToken(),
						TIME 			= tk.nextToken();
						
						// execute SQL insert statement
						//String query = "INSERT INTO 1 VALUES(FailureID_CSV,Fail_Qty,DATE_OCURRANCE,TIME_OCURRANCE)";
						String query = "INSERT INTO LogSPI01 VALUES(?,?,?,?)";
						ps = con.prepareStatement(query);
						ps.setString(1,PCB);
						ps.setString(2,FAIL_COUNT);
						ps.setString(3,DATE);
						ps.setString(4,TIME);
						
						ps.executeUpdate(query);
					} 
					else {
						System.out.println("Error:");
					}
				}
				in.close();
				con.close();
			} catch( Exception e) {
				e.printStackTrace();
			}
		}
	}

Can anyone help me?

Thanks in advance,
Regards
Jorge

We might, if we knew what you meant by "difficulties".

Is it not compiling?
Are you getting Exceptions when running?
Are you not getting the expected Results?

We need some more (actually, alot more) detailed information.

We might, if we knew what you meant by "difficulties".

Is it not compiling?
Are you getting Exceptions when running?
Are you not getting the expected Results?

We need some more (actually, alot more) detailed information.

Thank you for replying!

The program is compiling without errors
I don't have any exceptions when is running!
In fact the program is accessing the txt file, is reading every line but is not spliting the text as it should be...

i think it has to be with structure of the text...

184-22-03-T-01-E.F.T.#1.47f023f1.spi.csv, 0, 2008.03.31 , 07:36:17 AM

my idea is to split this line in:
184-22-03-T-01-E.F.T.
.#1.47f023f1.spi.csv
0
2008.03.31
07:36:17 AM

and insert each block of text in each field of the table...

And the program is not performing this at all

Thank you in advance

Rather than StringTokenizer try using split with the regex "\\s*,\\s*"

package spilog;


import java.io.*;
import java.sql.*;
import java.io.BufferedReader;
import java.io.FileReader;

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

        
		Database myprog = new Database();
		myprog.readFile();
		System.out.println("done");
	} // end main

	void readFile() {
		
		String record;
	
	try {

		BufferedReader inputStream = new BufferedReader(new FileReader("F:\\SPI01.log"));
		record = new String();


		while ((record = inputStream.readLine()) != null) {
			String st = record;
			String []splitSt = st.split(",");
			System.out.println("string : " +st);
					System.out.println("length :" + splitSt.length);
					int i;
					
					//Connecting to the database
					//String URL =
					//"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=E:\\public\\Log\\Results\\spimon.mdb;}",
					String HOST = "jdbc:odbc:spimon",
					DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
					Class.forName(DRIVER).newInstance();
					Connection myConn = DriverManager.getConnection(HOST,"","");
					//Connection myConn = DriverManager.getConnection(URL,"","");
					
					
					//using the database 
					Statement stat = myConn.createStatement();
					PreparedStatement updaterecord;
					String query="INSERT INTO logspi01 VALUES(?,?,?,?)"; //tem que ter o mesmo numero de campos da DB
					updaterecord=myConn.prepareStatement(query);
		

					for (i = 0; i < splitSt.length; i++ )
					{
						updaterecord.setString ((i+1), splitSt[i]);
					}
					updaterecord.executeUpdate();

					stat.close();
					myConn.close(); 
		}//end while
	} catch (IOException e) {
//		catch possible io errors from readLine()
		System.out.println("IOException error: ");
		e.printStackTrace();
	}
	catch (Exception ex) 
	{ 
		ex.printStackTrace();
	}
	} 
}

Thank you Very much... for the tip... during the weekend i've tryed to use split... not so complicated as the previous way... but let me ask you another thing... to avoid having duplicates in the database... there is anyway to avoid that using java.....

After exporting the data to access db... i would like to delete all the records that were duplicated...

Is it possible?!

Regards,

Jorge

Keep an ArrayList of the "keys" (those fields which iniquely identify the record), then use contains before adding the record.

Another way, would be to define a "primary key" in the table, then simply attempt to insert the records and catch the "duplicate key" SQLException.

Keep an ArrayList of the "keys" (those fields which iniquely identify the record), then use contains before adding the record.

Another way, would be to define a "primary key" in the table, then simply attempt to insert the records and catch the "duplicate key" SQLException.

hmmm :( i'm not so good!

Can you provide me an example...???? meanwhile i've managed to create a tricky way avoid duplicates.... that it is

if (fdir.getPath().endsWith(".csv") && fdir.lastModified() > HostFile.lastModified())
				System.out.println(INDENTS[depth] + fdir.getName() +", "+ countline +","+Date.format(nDate));

By placing that if ,automatically i will filter the contents being exported to the log file... and the they can be exported to the database....
but your tips looked more reliable...

please.. help!

Thanks in advance

Keep an ArrayList of the "keys" (those fields which iniquely identify the record), then use contains before adding the record.

Another way, would be to define a "primary key" in the table, then simply attempt to insert the records and catch the "duplicate key" SQLException.

Really i'm lost... i know what primary keys are but... the way that you've explained look really easy, however i have several dificulties on this subject...

Please help me!

regards,

Jorge

Do you know what a try/catch block is (I hope you do if you're using JDBC)? If so, then, simply put the execute statement for the insert inside of its own try/catch block, and when the insert fails due to duplicate keys, simply ignore that record and go on to the next.

Do you know what a try/catch block is (I hope you do if you're using JDBC)? If so, then, simply put the execute statement for the insert inside of its own try/catch block, and when the insert fails due to duplicate keys, simply ignore that record and go on to the next.

Hi again,,,

I'm trying to understand,,,, and i can't

I've tryed something like this

package spilog;


import java.io.*;
import java.sql.*;
import java.io.BufferedReader;
import java.io.FileReader;

class Database {

	public static void main (String args[]) {


		Database myprog = new Database();
		myprog.readFile();
		System.out.println("done");
	} // end main

	void readFile() {

		Connection myConn = null;
		String record;

		try {

			//BufferedReader inputStream = new BufferedReader(new FileReader("F:\\SPI01.log"));
			BufferedReader inputStream = new BufferedReader(new FileReader("E:\\public\\Log\\SPIlog.txt"));
			record = new String();




			while ((record = inputStream.readLine()) != null) {
				//if(record.trim().length() == 0) {
				record=record.trim();
				if (record.equalsIgnoreCase("\n")==false){ 
					
					/* Check for characters '#' and replace with ',' */
					char [][] maps = {{'R', ' '},{'.', ' '},{'S', ' '},{'E', ' '},{'F', ','},};
					for (int i = 0; i<maps.length; ++i)
					{
						record = record.replace(maps[i][0], maps[i][1]);
					}

					String st = record;
					String []splitSt = st.split(",");



					System.out.println("string : " + st );


					System.out.println("length :" + splitSt.length );
					int i;

					//Connecting to the database
					//String URL =
					//"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=E:\\public\\Log\\Results\\spimon.mdb;}",
					String HOST = "jdbc:odbc:spimon",
					DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
					Class.forName(DRIVER).newInstance();
					myConn = DriverManager.getConnection(HOST,"","");
					//Connection myConn = DriverManager.getConnection(URL,"","");


					//using the database 
					Statement stat = myConn.createStatement();
					PreparedStatement updaterecord;
					String query="INSERT INTO logspi01 VALUES(?,?,?,?,?,?,?)"; //tem que ter o mesmo numero de campos da DB
					updaterecord = myConn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);


					for (i = 0; i < splitSt.length; i++ )
					{
						//updaterecord.setString ((i+1), splitSt[i]); /*or*/
						updaterecord.setString((1)," "); 		//machine

						updaterecord.setString((2),splitSt[0]); //package
						updaterecord.setString((3)," ");		//lotnumber
						updaterecord.setString((4),splitSt[1]);	//Fail Location - (primary Key)
						updaterecord.setString((5),splitSt[2]);	//Fail Qty
						updaterecord.setString((6),splitSt[3]);	//Date
						updaterecord.setString((7),splitSt[4]);	//Time
//						updaterecord.setString((1),splitSt[0]);
//						updaterecord.setString((2),splitSt[1]);
//						updaterecord.setString((3)," ");
//						updaterecord.setString((4),splitSt[3]);
//						updaterecord.setString((5),splitSt[4]);
//						updaterecord.setString((6),splitSt[5]);
//						updaterecord.setString((7),splitSt[6]);
					}
					updaterecord.executeUpdate();
					int x = updaterecord.executeUpdate();
					stat.close();
					myConn.close();
					myConn = null;
					System.out.println("  Inserting Data " + x + " row(s)");

				}//end while
			} 
		}
		catch (IOException e) 
		{
			/* catch possible io errors from readLine() */
			System.out.println("IOException error: ");
			e.printStackTrace();
		}
		catch (Exception ex) 
		{ 
			ex.printStackTrace();
		}
		finally
		{
		if (myConn != null) try {myConn.close();
		}
		catch (Exception ignore){}
		}
	}
} 

/**
 * Keep an ArrayList of the "keys" (those fields which iniquely identify the record), then use contains before adding the record.

Another way, would be to define a "primary key" in the table, then simply attempt to insert the records and catch the "duplicate key" SQLException.

 * 
 * 
 * Do you know what a try/catch block is (I hope you do if you're using JDBC)? If so, then, simply put the execute statement for the insert inside of its own try/catch block, and when the insert fails due to duplicate keys, simply ignore that record and go on to the next. 
Java Programmer and Sun Systems Administrator
 */































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

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

//String line = null;
//boolean first = true;
//int count = 51;
//int rowNum = 0;
//String[] fieldlist = new String[count];
//String monTable = "LogSPI1";
//BufferredReader reader = null;
//Connection con = null;
//Statement stmt = null; 
//String url = "jdbc:odbc:Driver={Microsoft Access Driver " +
//"(*.mdb)};DBQ=c:/MyDocu~1/pyxis.mdb";
////HOST = "jdbc:odbc:spimon";

//try {
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//con = DriverManager.getConnection (url, "", ""); 
//stmt = con.createStatement ( );
//} catch (ClassNotFoundException e){
//System.err.println ("Unsuccesful loading : " + e.getMessage ( )); 
//} catch (SQLException ex){ 
//System.out.println ("con object error " + ex.getMessage( )); 
//}


//try{
//reader = new BufferedReader(new FileReader("F:\\SPI01.log"));
//} catch (FileNotFoundException e){ 
//System.out.println ("Check the file name/path.") 
//System.exit (); 
//}

//while ((line = reader.readLine ()) != null ){ 
//if (first) { 
//first = false; 
//rowNum++;
//continue; 
//}

//StringTokenizer tok = new StringTokenizer (line, ",", true ); 
//if( tok.countTokens() != count ) {
//System.out.println("Problem at row number " + rowNum);
//System.exit(); 
//} 
////parse fields 
//for( int field = 0; field < count; field++ ) { 
//String token = tok.nextToken (); 
////remove "," at the end 
//fieldlist[field] = token.substring (0, token.length()-1); 
//} 

////create insert command - handle each field as text 
//String insert = "INSERT INTO monTable values(fieldlist[0]";
//for (int field = 1; field < count; field++){
//insert += "," + fieldlist[field];
//}
//insert += ")";

//try {
//stmt.executeUpdate( insert );
//} catch (SQLException e) {
//System.out.println ("SQL error " + et.getMessage ( ) );
//}
//}

//reader.close ( );
//stmt.close ( );
//con.close ( ); 

//}
//} 

//}*/

but is providing this exception

java.lang.UnsupportedOperationException
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(Unknown Source)
at spilog.Database.readFile(Database.java:70)
at spilog.Database.main(Database.java:15)

I believe it is because of this

Statement stat = myConn.createStatement();
PreparedStatement updaterecord;
String query="INSERT INTO logspi01 VALUES(?,?,?,?,?,?,?)"; //tem que ter o mesmo numero de campos da DB
updaterecord = myConn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);

See http://java.sun.com/products/jdbc/faq.html#15

Get rid of "stat", which you are not using anyway.

I believe it is because of this

Statement stat = myConn.createStatement();
PreparedStatement updaterecord;
String query="INSERT INTO logspi01 VALUES(?,?,?,?,?,?,?)"; //tem que ter o mesmo numero de campos da DB
updaterecord = myConn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);

See http://java.sun.com/products/jdbc/faq.html#15

Get rid of "stat", which you are not using anyway.

package spilog;


import java.io.*;
import java.sql.*;
import java.util.StringTokenizer;
import java.io.BufferedReader;
import java.io.FileReader;

class Database {

	public static void main (String args[]) {


		Database myprog = new Database();
		myprog.readFile();
		System.out.println("done");
	} // end main
	
	
//	static String replaceAllWords1(String original, String find, String replacement) {
//	    String result = "";
//	    String delimiters = "+-*/(),. ";
//	    StringTokenizer st = new StringTokenizer(original, delimiters, true);
//	    while (st.hasMoreTokens()) {
//	        String w = st.nextToken();
//	        if (w.equals(find)) {
//	            result = result + replacement;
//	        } else {
//	            result = result + w;
//	        }
//	    }
//	    return result;}
	



	void readFile() {

		Connection myConn = null;
		String record;

		try {

			//BufferedReader inputStream = new BufferedReader(new FileReader("F:\\SPI01.log"));
			BufferedReader inputStream = new BufferedReader(new FileReader("E:\\public\\Log\\SPIlog.txt"));
			record = new String();




			while ((record = inputStream.readLine()) != null) {
				//if(record.trim().length() == 0) {
				record=record.trim();
				if (record.equalsIgnoreCase("\n")==false){
					
				

					/* Check for characters 'F' and replace with ',' */
					char [][] maps = {{'R', ' '},{'.', ' '},{'S', ' '},{'E', ' '},{'F', ','},};
					for (int i = 0; i<maps.length; ++i)
					{
						record = record.replace(maps[i][0], maps[i][1]);
					}
					
					
					String st = record;
					String []splitSt = st.split(",");

					System.out.println("string : " + st );

					System.out.println("length :" + splitSt.length );
					int i;

					//Connecting to the database
					//String URL =
					//"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=E:\\public\\Log\\Results\\spimon.mdb;}",
					String HOST = "jdbc:odbc:spimon",
					DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
					Class.forName(DRIVER).newInstance();
					myConn = DriverManager.getConnection(HOST,"","");
					//Connection myConn = DriverManager.getConnection(URL,"","");


					//using the database 
					//Statement stat = myConn.createStatement();
					PreparedStatement updaterecord;
					String query="INSERT INTO logspi01 VALUES(?,?,?,?,?,?,?)"; //tem que ter o mesmo numero de campos da DB
					updaterecord = myConn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);


					for (i = 0; i < splitSt.length; i++ )
					{
						//updaterecord.setString ((i+1), splitSt[i]); /*or*/
//						updaterecord.setString((1)," "); 		//machine
//
//						updaterecord.setString((2),splitSt[0]); //package
//						updaterecord.setString((3)," ");		//lotnumber
//						updaterecord.setString((4),splitSt[1]);	//Fail Location - (primary Key)
//						updaterecord.setString((5),splitSt[2]);	//Fail Qty
//						updaterecord.setString((6),splitSt[3]);	//Date
//						updaterecord.setString((7),splitSt[4]);	//Time
						updaterecord.setString((1),splitSt[0]);	//machine
						updaterecord.setString((2),splitSt[1]);	//package
						updaterecord.setString((3)," ");		//lotnumber
						updaterecord.setString((4),splitSt[3]);	//Fail Location
						updaterecord.setString((5),splitSt[4]);	//Fail Qty
						updaterecord.setString((6),splitSt[5]);	//Date
						updaterecord.setString((7),splitSt[6]);	//Time
					}
					updaterecord.executeUpdate();
					int x = updaterecord.executeUpdate();
					//stat.close();
					myConn.close();
				myConn = null;
					System.out.println("  Inserting Data " + x + " row(s)");

				}//end while
			} 
		}
		catch (IOException e) 
		{
			/* catch possible io errors from readLine() */
			System.out.println("IOException error: ");
			e.printStackTrace();
		}
		catch (Exception ex) 
		{ 
			ex.printStackTrace();
		}
		finally
		{
		if (myConn != null) try {myConn.close();
		}
		catch (Exception ignore){}
		}
	}
}

and the error is java.lang.UnsupportedOperationException
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(Unknown Source)
at spilog.Database.readFile(Database.java:89)
at spilog.Database.main(Database.java:16)
done
I«m lost..

Thank you in advance

Try removing that ",Statement.RETURN_GENERATED_KEYS". Your code isn't make use of it anyway.

In the Table i have defined a primary key....

if i remove that - Statement.RETURN_GENERATED_KEYS - it will work but regarding duplicated data... what to do? to prevent that....

regards,

Thank you

Do have an auto-incremented or auto-generated field as your primary key?

If so, you won't be able to prevenet duplicate inserts, only on the base of the primary key. You will need to define an additional unique index (and it can be a single index that spans multiple fields) that actually uses the data you will be inserting.

Do have an auto-incremented or auto-generated field as your primary key?

If so, you won't be able to prevenet duplicate inserts, only on the base of the primary key. You will need to define an additional unique index (and it can be a single index that spans multiple fields) that actually uses the data you will be inserting.

Hi!


I have assigned the field fail location as primary key... is the only field were data won't repeat itself or very seldom...

so in this case what is your advise????

SOrry to disturb you so often,,,,


Regards,

Jorge

This article has been dead for over six months. Start a new discussion instead.