Results 1 to 7 of 7
  1. #1
    dcoakley is offline Member
    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0

    Question Writing CSV Data to MySQL

    I've been searching for a method to complete the following task as efficiently as possible: I have a large set of csv files (>500k) contained in individual sub-directories. I want to parse the entire content of these files to a MySQL database.

    I think I have developed a method to cycle through each file in each sub-directory recursively and I am using methods from JavaCSV (CsvReader and CsvWriter) to append this data to a single csv file. While this has worked, it is not very efficient and is taking far too long to complete the process. Is there any quick method of adding the entire contents of a file to a single csv or SQL database without parsing through the file line by line.

    Here is the code I am currently using. Clearly, I'm not a programmer and I've just bashed together snippets of code that worked for my requirements, however, I know there has to be a better method:

    package com.resultscollector;

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

    import com.csvreader.CsvReader;
    import com.csvreader.CsvWriter;
    public class ListFiles {

    public static void main(String[] args) {
    try {
    listFiles(new File("C:/temp"));
    } catch (Exception e) {
    e.printStackTrace();
    }

    }

    public static void listFiles(File dir) throws Exception {
    File[] files = dir.listFiles();
    for (int i = 0; i < files.length; i++) {
    String fileName = files[i].getName();
    // put in your filter here
    if (fileName.endsWith("result.csv")) {
    if (files[i].isFile()) {
    try {

    CsvReader results = new CsvReader("results.csv");

    results.readHeaders();

    while (results.readRecord())
    {
    String Path = files[i].getPath();
    String DateTime = results.get("Date/Time");
    String Result1= results.get("Result1");
    String Result2= results.get("Result2");
    String Result3= results.get("Result3");

    String outputFile = "results.csv";

    // before we open the file check to see if it already exists

    boolean alreadyExists = new File(outputFile).exists();

    try {
    // use FileWriter constructor that specifies open for appending
    CsvWriter csvOutput = new CsvWriter(new FileWriter(outputFile, true), ',');

    // if the file didn't already exist then we need to write out the header line
    if (!alreadyExists)
    {
    csvOutput.write("Path");
    csvOutput.write("Date/Time");
    csvOutput.write("Result1");
    csvOutput.write("Result2");
    csvOutput.write("Result3");
    csvOutput.endRecord();
    }
    // else assume that the file already has the correct header line

    // write out a few records
    csvOutput.write(Path);
    csvOutput.write(DateTime);
    csvOutput.write(Result1);
    csvOutput.write(Result2);
    csvOutput.write(Result3);
    csvOutput.endRecord();


    csvOutput.close();
    } catch (IOException e) {
    e.printStackTrace();
    }

    }

    results.close();

    } catch (FileNotFoundException e) {
    e.printStackTrace();
    } catch (IOException e) {
    e.printStackTrace();
    }
    }
    }
    if (files[i].isDirectory()) {
    listFiles(files[i]);
    }
    }
    }

    }
    As you can see, I am also adding a pathname to the final output file. I would prefer to add the directory in which the current results file is stored rather than the full path. But any way you can suggest this may be sped up, I would really appreciate it. I have been looking at other open csv parsing packages such as opencsv. However, I'm very new to this are so would appreciate any direction. Thanks.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    18

    Default Re: Writing CSV Data to MySQL

    Isn't there an import tool for CSV to MySQL?
    In fact mysqlimport...
    Please do not ask for code as refusal often offends.

  3. #3
    dcoakley is offline Member
    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0

    Default Re: Writing CSV Data to MySQL

    Quote Originally Posted by Tolls View Post
    Isn't there an import tool for CSV to MySQL?
    In fact mysqlimport...
    Thanks for the repl. I should have been clearer in my request. I have used mysqlimport to import a single csv file into MySQL. However, I need to recursively scan files contained in thousands of sub-directories. As far as I know, mysqlimport was not capable of this. Another problem was the fact that I wanted to pass the folder name into the final output file along with the csv contents.

    Results are contained in directories as follows:
    • User/1/results.csv
    • User/2/results.csv
    • User/3/results.csv
    • ...
    • User/50000/results.csv


    I was not able to pass this directory name into mysql using mysqlimport. Therefore, I am using Java to recursively search for results files in each sub-directory, parse it's contents, add a parent folder name and send the results to a single csv file (I just asked about MySQL as it would be an advantage if I could just load the information directly into MySQL and skip outputting to a single csv file).

    Hope all this makes sense. Thanks for your help

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    18

    Default Re: Writing CSV Data to MySQL

    There's not likely to be any tool that does that for you.
    It's pretty specific.
    All you can do is look at how you are doing the processing (which should be a simple read line/prepend(append?) folder/write line).
    Please do not ask for code as refusal often offends.

  5. #5
    dcoakley is offline Member
    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0

    Default Re: Writing CSV Data to MySQL

    Quote Originally Posted by Tolls View Post
    There's not likely to be any tool that does that for you.
    It's pretty specific.
    All you can do is look at how you are doing the processing (which should be a simple read line/prepend(append?) folder/write line).
    Thanks for the reply. I'm really just looking for a more efficient way to reading and writing many csv files. I'm sure theres a better method than the line by line append process I outlined above. i thought about adding each file to an array and outputting the entire array to a file when I finished. However, this might cause problems with Virtual Memory? Perhaps just appending lines to an array and appending the array to the file after each iteration? Surely that would be quicker than looping through each line and appending individually?

    Here is the code as it stands at present:

    package com.resultscollector;

    import java.io.*;
    import java.util.ArrayList;
    import java.util.List;

    import com.codeexamples.FileArray;


    public class ListFiles {

    public static void main(String[] args) {
    // eg java ListFiles c:\
    try {
    listFiles(new File("C:/temp"));
    } catch (Exception e) {
    e.printStackTrace();
    }
    }

    public static void listFiles(File dir) throws Exception {
    File[] files = dir.listFiles();
    FileArray fap = new FileArray();
    for (int i = 0; i < files.length; i++) {
    String fileName = files[i].getName();

    // put in your filter here
    if (fileName.endsWith("results.csv")) {
    if (files[i].isFile()) {
    String Dir = files[i].getParentFile().getName();
    String Path = files[i].getPath();
    String[] lines = fap.readLines(Path);

    List<String> results = new ArrayList<String>();

    for (String line : lines) {
    results.add(Dir + "," + line + "\n");

    }
    System.out.println(results);

    }
    }
    if (files[i].isDirectory()) {
    listFiles(files[i]);
    }
    }
    }

    }
    I've highlighted the section I'm interested in, in red. I want to change the output processing if possible. Any advice is much appreciated. thanks again

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    18

    Default Re: Writing CSV Data to MySQL

    When reading from a file for the sole purpose of outputting to another file (or location) possibly with a minor change per line, then read a line write a line.
    Java Code:
    open FileWriter.
    for each input file
       open FileReader
       for each line read
          modify the line
          write the modified line
       close the FileReader
    close the FileWriter
    That way you will only ever have a line (plus whatever buffering the reader/writer use) in memory at a time.
    It's also the fastest you will get out of Java.
    Please do not ask for code as refusal often offends.

  7. #7
    dcoakley is offline Member
    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0

    Default Re: Writing CSV Data to MySQL

    Thanks for the reply tolls, I currently have a working piece of code below. I may need to adjust it based on your latest comment. However, it seems to work and appends all the resulst to a single csv file.

    Now there are still a few issues:
    1. The csv file is excessively large (cannot be opened in any standard application) and is not really the final intended output - need to export to MySQL
    2. I need to exclude headers from each scanned csv file
    3. I cannot check on collection progress (in case of crash) - this is not really a major issue if the code is stable


    They are the main points I want to address. If I could open a connection to the MySQL database and append lines using the same method, that would be a huge help. I'm happy that it's now working in some form though as it allows me to experiment with different methods.

    Thanks again for all your help.


    Java Code:
    package com.resultscollector;
    
    import java.io.BufferedReader;
    import java.io.FileReader;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    
    public class FileArray {
    
        public String[] readLines(String filename) throws IOException {
            FileReader fileReader = new FileReader(filename);
            BufferedReader bufferedReader = new BufferedReader(fileReader);
            List<String> lines = new ArrayList<String>();
            String line = null;
            while ((line = bufferedReader.readLine()) != null) {
                lines.add(line);
            }
            bufferedReader.close();
            return lines.toArray(new String[lines.size()]);
        }
    }
    
    
    public class ListFiles {  
              
        public static void main(String[] args) {  
            // eg java ListFiles c:\  
                try {  
                    listFiles(new File("Output Files/Results"));  
                } catch (Exception e) {  
                    e.printStackTrace();  
                }  
        } 
        
         
        public static void listFiles(File dir) throws Exception { 
        	boolean append = true;
        	File f1 = new File("Output Files/Results/SimResults.csv");
        	f1.delete();
            File[] files = dir.listFiles(); 
            FileArray SimResults = new FileArray();
            FileWriter list = new FileWriter("Output Files/Results/SimResults.csv", append);
            for (int i = 0; i < files.length; i++) {
            	
            	String fileName = files[i].getName();
            	
                // put in your filter here  
                if (fileName.endsWith("result.csv")) {
                	
                    if (files[i].isFile()) {
                    	String Dir = files[i].getParentFile().getName();
                    	String Path = files[i].getPath();
                    	String[] lines = SimResults.readLines(Path);
                    	
                    	for (String line : lines) {
                    		list.append(Dir + "," + line + "\n");
                    	}
               
                    }  
                }  
                if (files[i].isDirectory()) {  
                    listFiles(files[i]);  
                }  
            }  
           list.close(); 
        }  
          
    }
    Sorry about the blank lines above - I can't seem to get rid of them :)

    So the above program reads files in the Output Files/Results directory (and subdirectories) which are names result.csv and appends each Line + Parent Folder name to a new file called SimResults.csv in the Output Files/Results directory.

    The code is (probably) not very pretty and is sort of mashed together so I could get it to work. I'm not a programmer though and don't really have any background in this sort of thing so getting any sort of output from it was a huge relief
    Last edited by dcoakley; 09-02-2012 at 04:23 PM.

Similar Threads

  1. Writing the data into RandomAccessFile
    By vasavi.singh in forum New To Java
    Replies: 5
    Last Post: 01-31-2013, 07:12 AM
  2. Need Help - Writing Data to File
    By uday.bhagwat7 in forum New To Java
    Replies: 0
    Last Post: 02-17-2011, 04:13 AM
  3. Writing data into a file Please Help!
    By Richman118 in forum New To Java
    Replies: 3
    Last Post: 12-17-2010, 02:28 AM
  4. Writing sound file to mysql
    By mbarandao in forum Java Applets
    Replies: 5
    Last Post: 10-15-2010, 08:57 AM

Tags for this Thread

Posting Permissions

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