Inserting .csv file into mysql db Java: DATETIME format column not imported successfully

Question

The .csv file that I'm trying to import has a DATETIME column called test_date that's behaving a bit weirdly. The output in the mySQL table is all 00:00:00 00:00.

It's shown in notepad as "2017-03-25 00:00:01" where it seems the "" surrounding the date-time value is the crucial factor here.

Facts of the problem:

MySQL table's corresponding column is of the type DATETIME
I have used +"SET test_date = STR_TO_DATE(@var2, '%d/%m/%Y %k:%i')" to handle incorrect date-time formats
All other .csv docs where the date-time column was stored without "" were successfully imported.

I use the LOAD DATA LOCAL INFILE command on my IDE's Java code as shown:

String loadQuery = "LOAD DATA LOCAL INFILE '" + file
    + "' INTO TABLE source_data_android_cell CHARACTER SET latin1 FIELDS TERMINATED BY ','"
    + "ENCLOSED BY '\"'" + " LINES TERMINATED BY '\n'  " + "IGNORE 1 LINES(@var1...)"
    + "SET test_date = STR_TO_DATE(@var1, '%d/%m/%Y %k:%i')";

I have no control over how the .csv files are created or stored so what are the possible remedies to my code on my end?

UPDATED:

As suggested by Maurice, I endeavoured to find out how to read a .csv file and store it into an array.

Forgive my shameless googling skills, but I've managed to source together the following:

public void processCSVFile(String filePath){
    try(BufferedReader fileReader = new BufferedReader(new FileReader(new File(filePath)))){
        //Create two lists to hold name and height.
        List<String> nameList = new ArrayList<>();
        List<Integer> heightList = new ArrayList<>();

        String eachLine = "";
        /*
         * Read until you hit end of file.
         */
        while((eachLine = fileReader.readLine()) != null){
            /*
             * As it is CSV file, split each line at ","
             */
            String[] nameAndHeightPair = eachLine.split(",");

            /*
             * Add each item into respective lists.
             */
            nameList.add(nameAndHeightPair[0]);
            heightList.add(Integer.parseInt(nameAndHeightPair[1]));
        }

        /*
         * If you are very specific, you can convert these
         * ArrayList to arrays here.
         */
    }catch(IOException e1){
        e1.printStackTrace();
    }
}

The thing is, how do I tweak the code to handle .csv files where info is stored as "...", "..." to handle the "" containment?

Sure: read the csv line by line, and INSERT each row.

Maurice Perry
– Maurice Perry

2017-09-19 10:29:23 +00:00
Commented Sep 19, 2017 at 10:29 — Maurice Perry
– Maurice Perry, Commented Sep 19, 2017 at 10:29
ah damn, i was hoping to avoid having to do that =/

Mack
– Mack

2017-09-19 11:21:58 +00:00
Commented Sep 19, 2017 at 11:21 — Mack
– Mack, Commented Sep 19, 2017 at 11:21

Mack · Accepted Answer · 2017-09-20 02:34:10Z

0

I managed to find an answer to this question here: MySQL CSV import: datetime value

Basically what my code wasn't doing was handling the " separators well. All i needed was to rearrange the command, curiously enough and it worked

Changing my LOAD DATA LOCAL INFILE command to the below seemed to do the trick:

LOAD DATA LOCAL INFILE 'ios_Data.csv'
INTO TABLE ios_import
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
(test_date)

Use of +"SET test_date = STR_TO_DATE(@var2, '%d/%m/%Y %k:%i')" is also not needed, to my surprise.

answered Sep 20, 2017 at 2:34

Mack

1172 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting .csv file into mysql db Java: DATETIME format column not imported successfully

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related