Thread: Input file parsing to insert into DB

Hi,

I have a sample input file as follows:

++++++++++ 2009-09-28 15:10:58 ++++++++++++++
++++++++ DAILY SCORES +++++++++++++++++
2009-09-28 15:10:58|AAAA|0
2009-09-28 15:10:58|BBBBB|0
2009-09-28 15:10:58|ACCCC|0
++++++++ MONTHLY SCORES +++++++++++++++++
2009-09-28 15:10:58|AAAA|30
2009-09-28 15:10:58|BBBBB|50
2009-09-28 15:10:58|ACCCC|60

I should ignore first line and then look for "DAILY SCORES" or "MONTHLY SCORES"string. This determines which MySql table has to be used.
Then read the following lines delimited by pipe and insert them into MySql table

Table schema is as follows:
------------------------------------------------------------
dateandtimestamp | program | score
------------------------------------------------------------

Please suggest the best approach to do this.

Thanks,
Mona.

1. Loop through each line in the file
2. Do some checks to see if it contains DAILY SCORES or MONTHLY SCORES
3 .Do a split on each line after the above on the pipe |
4. Create your object or just simply insert into your database

Fairly simple I guess. Is this just some program you'd like to be run every so often on a certain file/folder then I take it?

// Json

Thanks for your reply. I am looking for more elaborated answer. Can you please help me with the code? Yes, this program has to be run every one hour and each time there is going to be a new file with updated scores.

I created this in a couple of minutes as a test, it shows you the string reading and parsing but no database connection stuff.

This code opens a file called c:/test.txt but you can change that to whatever you wish.

package uk.co.cdl.testing;
 
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
 
public class ReadAndParseFile {
 
    public enum State {
        PARSING,
        DAILY_SCORES,
        MONTHLY_SCORES;
    }
 
    private static State state = State.PARSING;
 
    public static void main(String... arguments) throws IOException {
        final String pathToFile = "c:/test.txt";
        final BufferedReader bufferedReader = new BufferedReader(new FileReader(new File(pathToFile)));
        String line = null;
 
        while ((line = bufferedReader.readLine()) != null) {
            if (line.contains("DAILY SCORES")) {
                state = State.DAILY_SCORES;
            } else if (line.contains("MONTHLY SCORES")) {
                state = State.MONTHLY_SCORES;
            }
 
            addToDataBase(line);
        }
 
        if (bufferedReader != null) {
            bufferedReader.close();
        }
    }
 
    private static void addToDataBase(final String line) {
 
        if (!State.PARSING.equals(state)) {
            final String[] split = line.split("\\|");
 
            if (split.length >= 3) {
                final String date = split[0];
                final String program = split[1];
                final String score = split[2];
 
                if (State.DAILY_SCORES.equals(state)) {
                    // TODO: Add the scores to the daily scores table
                } else if (State.MONTHLY_SCORES.equals(state)) {
                    // TODO: Add the scores to the monthly scores table
                }
 
                System.out.println("Found line with date [" + date + "] program [" + program + "] score [" + score + "] on state [" + state.toString() + "]");
            }
        }
    }
}

My output.

Found line with date [2009-09-28 15:10:58] program [AAAA] score [0] on state [DAILY_SCORES]
Found line with date [2009-09-28 15:10:58] program [BBBBB] score [0] on state [DAILY_SCORES]
Found line with date [2009-09-28 15:10:58] program [ACCCC] score [0] on state [DAILY_SCORES]
Found line with date [2009-09-28 15:10:58] program [AAAA] score [30] on state [MONTHLY_SCORES]
Found line with date [2009-09-28 15:10:58] program [BBBBB] score [50] on state [MONTHLY_SCORES]
Found line with date [2009-09-28 15:10:58] program [ACCCC] score [60] on state [MONTHLY_SCORES]

Let us know if you need more help.

// Json