Uploading Tab Delimted Text files (Continued)

[Palli]

A while back i posted this same question as you can see here

http://www.webdeveloper.com/forum/sh...d.php?t=191859

So i created a webpage that would allow users to upload tab - delimited text files to a mysql database using servlets. The servlet would "dissect" the text file and construct appropriate sql statements. So each line of the text file would be converted into a string, and when all had been created the INSERT statement is used.

This proved to work well for smaller files. But some users have data files with 100000 entries and 20 columns each. So it takes a VERY long time to upload it all, convert it and store it in the DB.

My new question is, is there a better, and more to the point, quicker and more efficient way of doing it compared to the way i do it now. Any technologies welcome!

Thanks

[chazzy]

you can use mysql's load data infile command.

[Palli]

can you call load data infile from a file that is saved in the memory. if so how would i call it then?

This is how i store the file:

	private void processUploadFile(HttpServletRequest request){

	
	ServletFileUpload servletFileUpload = new ServletFileUpload(new DiskFileItemFactory());
	List fileItemsList;
	
	try {
		fileItemsList = servletFileUpload.parseRequest(request);

		DiskFileItemFactory diskFileItemFactory = new DiskFileItemFactory();
		
		File repositoryPath = new File("/temp");
		diskFileItemFactory.setRepository(repositoryPath);
	
		} catch (FileUploadException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

[Palli]

i assume it would be along the lines of this:

	private void insertDataIntoTable(){

				
		String insertData = "load data infile '\\temp'" +
				" into table testtable" + 
				"fields terminated by '\\t' " +
				"lines TERMINATED by '\\n'";
		
		// insert all the data into the table
		
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(insertData);	

		}

		catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());

		}
			
	}

But how would i reference the file as its in the temp...?????

[Palli]

anyone ......

[chazzy]

temp files still have a path. there's no reason why you can't call getAbsolutePath() on your temp file.

also you should use the LOCAL modifier, unless you're uploading this file to the db server.

[Palli]

Im probably being slow. I tried this:

File repositoryPath = new File("//temp");
diskFileItemFactory.setRepository(repositoryPath);
	
String path = repositoryPath.getAbsolutePath();

-------------------------------------------

String insertData = "load data LOCAL infile '"+path+"'"+
				" into table " + upFile.getNoteContent() + 
				" fields TERMINATED by '\\t' " +
				"lines TERMINATED by '\\n' IGNORE 1 LINES";

But get this error.

SQLException: Unable to open file '\temp'for 'LOAD DATA LOCAL INFILE' command.Due to underlying IOException:

** BEGIN NESTED EXCEPTION **

java.io.FileNotFoundException
MESSAGE: \temp (The system cannot find the file specified)

STACKTRACE:

java.io.FileNotFoundException: \temp (The system cannot find the file specified)

[Palli]

Ok im nearly there,

I create the temp file:

File repositoryPath = null;
		try {
			repositoryPath = File.createTempFile("tempFile",".txt");
			path = repositoryPath.getAbsolutePath();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	System.out.println(path);

----------------------

then run the sql with the path name

System.out.println(path);
		String insertData = "load data LOCAL infile '"+path+"'"+
				" into table " + upFile.getNoteContent() + 
				" fields TERMINATED by '\\t' " +
				"lines TERMINATED by '\\n' IGNORE 1 LINES";

However when do syso(repositoryPath.getAbsolutePath()); it prints

C:\Program Files\Apache Software Foundation\Tomcat 5.5\temp\tempFile45123.txt

But when the sql is run i get the error:

SQLException: Unable to open file 'C:Program FilesApache Software FoundationTomcat 5.5	emp	empFile45123.txt'for 'LOAD DATA LOCAL INFILE' command.Due to underlying IOException:

How to replace the \ with \\ as path.replace("\", "\\"); shows an error

[chazzy]

try changing java.io.tmpdir to something not so deep. the issue is the spaces rather than the slashes. or instead of creating a temp file just make a regular file and delete it when done.

[Palli]

yeah i have managed to get it to work fine when saving the file and deleting it after. However files larger than 15mb throw a java heap memory error.

[chazzy]

Try upping your vmargs then

Or

Can you be more specific? What about uploading to the db server?

[Palli]

all sorted now chazzy, cheers for your help