CSVParser.java

/**
 * Main class.
 **/

package uk.ac.qub.programming;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Passes over a CSV file and adds each line to a database.
 * @author Padraig
 *
 */
public class CSVParser {
  
    // Line counter
    static int lines = 0;
        
    // Database stuff
    public static final String URL = "jdbc:mysql://...";
    public static final String USERNAME = "...";
    public static final String PASSWORD = "...";
    
    // CSV file path
    public static final String FILE_PATH = "csv.txt";
    
    // SQL stuff
    private static PreparedStatement addData = null;
    private static Connection connection = null;

    /**
     * Instantiate and run
     * @param args
     */
    public static void main(String[] args) {
        CSVParser obj = new CSVParser();
        obj.run();
    }
    
    /**
     * Connect to the database, read the file, pass it off to be validated
     * and entered.
     */
    public void run() {
        BufferedReader fileReader = null;
        String line = "";
        String csvSplitBy = "\\|";
 
        try {
            // Create the connection to the DB
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // Prepared statement to prevent SQL injection
            addData = connection.prepareStatement(
                    "INSERT INTO CustomerData " +
                    "VALUES (?, ?, ?, ?, ?, ?)" );
            
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
            System.exit(1);
        }
        
        // Try and read in the CSV file
        try {
            fileReader = new BufferedReader(new FileReader(FILE_PATH));
            while ((line = fileReader.readLine()) != null) {
                lines++;
                
                // Use pipe separation
                String[] tokenizedLine = line.split(csvSplitBy);
                // Add every line except the first line
                if(lines != 1) {
                    DataValidator.validateData(tokenizedLine);
                    addData(tokenizedLine);
                }
            }
            System.out.println("Done. "+lines+" wee wet rows added to "+URL+"");
            System.out.println("WARNING! "+ (ErrorLogger.errorNumber-1) +" errors logged.");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileReader != null) {
                try {
                    fileReader.close();
                    ErrorLogger.output.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
 
    /**
     * Add data to the database.
     * @param tokens - One line of the CSV file passed as an array.
     */
    public void addData(String[] tokens) {
        try {
            // Loop over all tokens and add to prepared statement
            for (int counter = 1; counter <= 6; counter++){
                addData.setString( counter, tokens[counter-1]);
            }
            addData.executeUpdate();
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
            System.err.println("Problem adding data.");
            System.exit(1);
        }
    }
}

DataValidator.java

package uk.ac.qub.programming;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DataValidator {

    @SuppressWarnings("unused")
    private static Date date;
    
    /**
     * Array indexes as constants, in case they change
     * Expecting: Surname, Firstname|int|string|date|float|string
     */
    private static final int NAME_INDEX = 0;
    private static final int INT_INDEX = 1;
    private static final int STRING_INDEX = 2;
    private static final int DATE_INDEX = 3;
    private static final int FLOAT_INDEX = 4;
    private static final int ANOTHER_STRING_INDEX = 5;
 
    /**
     * Validates each index of the row.
     * @param tokens - One line of the CSV file passed as an array.
     */
    public static void validateData(String[] tokens){
        
        validateString(tokens[NAME_INDEX]);
        validateInt(tokens[INT_INDEX]);
        validateString(tokens[STRING_INDEX]);
        validateDate(tokens[DATE_INDEX]);
        validateFloat(tokens[FLOAT_INDEX]);
        validateString(tokens[ANOTHER_STRING_INDEX]);
    }
    
    /**
     * Validate the date
     * @param tokens - One line of the CSV file passed as an array.
     */
    public static void validateDate(String dateForValidation) {
        // Validate the date
        try {
            DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
            formatter.setLenient(false);
            // Set the global variable date to pass to getDayOfWeek
            date = formatter.parse(dateForValidation);
        } catch (ParseException e) {
            ErrorLogger.logError("Invalid date in row " +CSVParser.lines+ "\n");
        }
    }
    
    /**
     * Validate any string for Unicode letters (but not symbols or numbers)
     * @param stringForValidation
     */
    public static void validateString(String stringForValidation) {
 
        for(int counter = 0; counter < stringForValidation.length(); counter++){
            if (!Character.isLetter(stringForValidation.charAt(counter))) {
                ErrorLogger.logError("Invalid string in row " 
                        +CSVParser.lines+ ": " +stringForValidation+ "\n");
            }
            
        }
    }
    
    public static void validateInt(String integerForValidation) {
        try {
            @SuppressWarnings("unused")
            int i = Integer.parseInt(integerForValidation);
        } catch (Exception e) {
            ErrorLogger.logError("Invalid integer in row " +CSVParser.lines+ ": "+
                    integerForValidation);
        }
    }
    
    public static void validateFloat(String floatForValidation) {
        try {
            @SuppressWarnings("unused")
            float f = Float.parseFloat(floatForValidation);
        } catch (Exception e) {
            ErrorLogger.logError("Invalid float in row " +CSVParser.lines+ ": "+
                    floatForValidation);
        }
    }
    
    
}

ErrorLogger.java

package uk.ac.qub.programming;

import java.io.FileNotFoundException;
import java.util.Formatter;
import java.util.FormatterClosedException;

public class ErrorLogger {
    
    static Formatter output;
    private static boolean openLog = false;
    static int errorNumber = 1;
    
    /**
     * Add entry to log file.
     */
    public static void logError(String error){
        try {
            if (openLog) {
                output.format(errorNumber+": %s%n", error);
                errorNumber++;
            } else {
                openLogfile(error);
            }
        } catch (FormatterClosedException formatterClosedException) {
            System.err.println("Error writing to log file.");
        }
    }
    
    /**
     * Create a log file.
     */
    public static void openLogfile(String error) {
        try {
            output = new Formatter("log.txt");
            openLog = true;
            logError(error);
        } catch (SecurityException securityException) {
            System.err.println("You do not have write access to the log file.");
        } catch (FileNotFoundException fileNotFoundException) {
            System.err.println("Error opening or creating log file.");
            System.exit(1);
        }
    }
}