Confusion with File instance function in Java: How to import an arbitrary .csv file into mysql instead of a specific one?

by Mack   Last Updated September 14, 2017 08:05 AM

I currently have two programs on hand.

1) CSVReader: One that imports a .csv file from a directory whose path i have to manually specify into a MySQL database.

2) DirectoryWatcher: One that watches that said directory for changes eg. insertion(otherwise known as creation) of new .csv files in that directory and prints out the filename

I need to find a way to link the two programs into a seamless program that combines the two: Program 2) checks a directory for new .csv files THEN sends the filename to Program 1) which imports the file with the filename.

TLDR VERSION: How can i tweak the two codes to make them work together? The below explains another slight issue i had with my attempts at it.

I'll start by listing the codes i used for your reference first:

Program 1) CSVReader

import java.sql.Connection;

import java.sql.Statement;
import java.io.*;

public class ImportCsv {

public static void main(String[] args) {
    ImportCsv.readCsvUsingLoad();
}

public static void readCsvUsingLoad() {

    try (Connection connection = DBConnection.getConnection()) {



        String loadQuery = "LOAD DATA LOCAL INFILE '" + "C:/Users/User/Desktop/Test/upload2.csv" + "' INTO TABLE txn_tbl FIELDS TERMINATED BY ','"
                + " LINES TERMINATED BY '\n'  " + "IGNORE 1 LINES(txn_amount, card_number, terminal_id)";
        System.out.println(loadQuery);
        Statement stmt = connection.createStatement();
        stmt.execute(loadQuery);
        System.out.println("Data import success");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}

Program 2) DirectoryWatcher

import com.sun.xml.internal.ws.wsdl.writer.document.Import;

import static java.nio.file.LinkOption.NOFOLLOW_LINKS;
import static java.nio.file.StandardWatchEventKinds.ENTRY_CREATE;
import static java.nio.file.StandardWatchEventKinds.OVERFLOW;
import static java.nio.file.StandardWatchEventKinds.ENTRY_DELETE;
import static java.nio.file.StandardWatchEventKinds.ENTRY_MODIFY;

import java.io.File;
import java.io.IOException;
import java.nio.file.FileSystem;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.WatchEvent;
import java.nio.file.WatchEvent.Kind;
import java.nio.file.WatchKey;
import java.nio.file.WatchService;
import java.sql.Connection;

import java.sql.Statement;

public class MainWatch {

public static void watchDirectoryPath(Path path) {
    // Sanity check - Check if path is a folder
    try {
        Boolean isFolder = (Boolean) Files.getAttribute(path,
                "basic:isDirectory", NOFOLLOW_LINKS);
        if (!isFolder) {
            throw new IllegalArgumentException("Path: " + path
                    + " is not a folder");
        }
    } catch (IOException ioe) {
        // Folder does not exists
        ioe.printStackTrace();
    }

    System.out.println("Watching path: " + path);

    // We obtain the file system of the Path
    FileSystem fs = path.getFileSystem();

    // We create the new WatchService using the new try() block
    try (WatchService service = fs.newWatchService()) {

        // We register the path to the service
        // We watch for creation events
        path.register(service, ENTRY_CREATE, ENTRY_MODIFY, ENTRY_DELETE);

        // Start the infinite polling loop
        WatchKey key = null;
        while (true) {
            key = service.take();

            // Dequeueing events
            Kind<?> kind = null;
            for (WatchEvent<?> watchEvent : key.pollEvents()) {
                // Get the type of the event
                kind = watchEvent.kind();
                if (OVERFLOW == kind) {
                    continue; // loop
                } else if (ENTRY_CREATE == kind) {
                    // A new Path was created
                    Path newPath = ((WatchEvent<Path>) watchEvent)
                            .context();
                    // Output
                    System.out.println("New path created: " + newPath);

                    String filename = "C:/Users/User/Desktop/Test/newPath";


                    ImportCsv.readCsvUsingLoad();








                } else if (ENTRY_MODIFY == kind) {
                    // modified
                    Path newPath = ((WatchEvent<Path>) watchEvent)
                            .context();
                    // Output
                    System.out.println("New path modified: " + newPath);
                }
            }

            if (!key.reset()) {
                break; // loop
            }
        }

    } catch (IOException ioe) {
        ioe.printStackTrace();
    } catch (InterruptedException ie) {
        ie.printStackTrace();
    }

}

public static void main(String[] args) throws IOException,
        InterruptedException {
    // Folder we are going to watch
    // Path folder =
    // Paths.get(System.getProperty("C:\\Users\\Isuru\\Downloads"));
    File dir = new File("C:\\Users\\User\\Desktop\\Test");
    watchDirectoryPath(dir.toPath());
}
 }

To begin, i broke the problem down into simpler bits and tackled the CSVReader first.

Instead of keying in the file's directory path into the LOAD DATA LOCAL INFILE command, I wanted to turn the filename into a variable and so i created a new file instance as follows:

File file = new File("C:/Users/User/Desktop/Test/upload2.csv");
String path = file.getAbsolutePath();
String loadQuery = "LOAD DATA LOCAL INFILE '" + path + "' INTO TABLE txn_tbl FIELDS TERMINATED BY ','"
                                        + " LINES TERMINATED BY '\n'  " + "IGNORE 1 LINES(txn_amount, card_number, terminal_id)";

This did not work, throwing the FileNotFoundException which i cant figure out why. Basically the program couldnt seem to find my file.

Thanks for any help or advice rendered!



Related Questions



Write xtend code with IntelliJ Idea

Updated May 14, 2015 09:02 AM

How can I debug a JSP?

Updated May 06, 2016 08:02 AM

UI Development on Intelli J

Updated April 16, 2017 21:05 PM

how to write JUnit testing using inteliij idea?

Updated July 02, 2015 16:02 PM