Convert a text file to a sql file

Question

I am working on an assignment that open a text file and convert/save as sql file. I got how to open a text file but I am stuck on how to convert to sql file.

Here is code that I got for reading the text file

    public static void main(String[] args) {

        // The path to open the file.
        String fileName = "input.txt";
        String line = null;

        try {
            // The file reads text files 
            FileReader file = new FileReader(fileName);

            // Wrap file in BufferedReader
            BufferedReader bufferedReader = new BufferedReader(file);

            while ( (line = bufferedReader.readLine()) != null ) {
                System.out.println(line);
            }

            // Close files
            bufferedReader.close();
        } catch ( FileNotFoundException ex ) {
            System.out.println("Unable to open file '" + fileName + "'");
        } catch ( IOException ex ) {
             ex.printStackTrace();
        }
    }

Can you anybody give me some hint how to save a text file as sql file after reading the text file?

Thank you so much!

Answer 1

Modify your try block like below. There are two functions added List<String> parseLine(String line) and String createInsert(List<String> headers, List<String> rowData)

Implement the first using simple String tokenization and then createInsert is used by simple string concatenations.

try {
        // The file reads text files 
        FileReader file = new FileReader(fileName);

        // Wrap file in BufferedReader
        BufferedReader bufferedReader = new BufferedReader(file);
        List<String> headers;
        String line = bufferedReader.readLine();
        if( line !=null ) { //got the row header
             headers = parseLine(line);
        }
        List<String> rowData;
        while ( (line = bufferedReader.readLine()) != null ) {
            rowData = parseLine(line);
            createInsert(headers, rowData);
        }
        // Close files
        bufferedReader.close();
    } catch ( FileNotFoundException ex ) {
        System.out.println("Unable to open file '" + fileName + "'");
    } catch ( IOException ex ) {
         ex.printStackTrace();
    }

Answer 2

Find below a working snippet with explanation of the several steps

public static void main(String[] args) throws Exception {
    // read all lines into a list of strings
    List<String> lines = Files.readAllLines(Paths.get("data.txt"),
            StandardCharsets.UTF_8);
    // use the first string to generate the INSERT statement
    // without the values
    String insertLine = createInsertLine(lines.get(0));
    try (BufferedWriter bw = Files.newBufferedWriter(Paths.get("data.sql"),
            StandardCharsets.UTF_8)) {
        bw.write(insertLine);
        bw.write(System.lineSeparator());
        int lastValueId = lines.size() - 1;
        for (int i = 1; i <= lastValueId; i++) {
            // create from each following line the VALUES part
            // of the INSERT statement
            String valueLine = createValueLine(i, lines.get(i));
            // write the lines into the file
            bw.write(valueLine);
            bw.write(i == lastValueId ? ';' : ',');
            bw.write(System.lineSeparator());
        }
    }
}

private static String createValueLine(int id, String line) {
    // split the values in the line on the '|' character
    // including leading and trailing blanks
    String[] columns = line.split(" *\\| *");
    // construct the VALUES line
    StringBuilder valueLine = new StringBuilder();
    // start with the ID value
    valueLine.append("(").append(id).append(", ");
    // append all column values, except for the last column
    for (int i = 0; i < columns.length - 1; i++) {
        valueLine.append('\'')
                .append(columns[i])
                .append("', ");
    }
    // append the last column value and the end of the line
    valueLine.append('\'')
            .append(columns[columns.length - 1])
            .append("')");
    return valueLine.toString();
}

private static String createInsertLine(String line) {
    String[] columns = line.split(" *\\| *");
    StringBuilder insertLine = new StringBuilder();
    insertLine.append("INSERT INTO 'events' ('id', ");
    for (int i = 0; i < columns.length - 1; i++) {
        insertLine.append('\'')
                .append(columns[i])
                .append("', ");
    }
    insertLine.append('\'')
            .append(columns[columns.length - 1])
            .append("') VALUES");
    return insertLine.toString();
}

assuming the file data.txt contains

Selected    |   Status  |   Event   |   File
Yes |   Listed  |   Birthday   | bday.pdf
No |   Not Listed  |   Gifts   | gifts.pdf

the generated data.sql will be

INSERT INTO 'events' ('id', 'Selected', 'Status', 'Event', 'File') VALUES
(1, 'Yes', 'Listed', 'Birthday', 'bday.pdf'),
(2, 'No', 'Not Listed', 'Gifts', 'gifts.pdf');