www.webdeveloper.com
Results 1 to 12 of 12

Thread: Uploading Tab Delimted Text files (Continued)

  1. #1
    Join Date
    Sep 2008
    Posts
    53

    Uploading Tab Delimted Text files (Continued)

    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

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    you can use mysql's load data infile command.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Sep 2008
    Posts
    53
    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:

    Code:
    	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();
    		}
    	}

  4. #4
    Join Date
    Sep 2008
    Posts
    53
    i assume it would be along the lines of this:

    Code:
    	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...?????

  5. #5
    Join Date
    Sep 2008
    Posts
    53
    anyone ......

  6. #6
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  7. #7
    Join Date
    Sep 2008
    Posts
    53
    Im probably being slow. I tried this:

    Code:
    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)
    Last edited by Palli; 11-17-2008 at 06:12 AM. Reason: updated error msg

  8. #8
    Join Date
    Sep 2008
    Posts
    53
    Ok im nearly there,

    I create the temp file:

    Code:
    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

    Code:
    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
    Code:
    C:\Program Files\Apache Software Foundation\Tomcat 5.5\temp\tempFile45123.txt
    But when the sql is run i get the error:
    Code:
    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

  9. #9
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Last edited by chazzy; 11-17-2008 at 08:13 AM.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  10. #10
    Join Date
    Sep 2008
    Posts
    53
    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.

  11. #11
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Try upping your vmargs then

    Or

    Can you be more specific? What about uploading to the db server?
    Last edited by chazzy; 11-17-2008 at 09:47 AM.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  12. #12
    Join Date
    Sep 2008
    Posts
    53
    all sorted now chazzy, cheers for your help

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles