Hammelkeule Hammelkeule - 2 years ago 202
Java Question

Generate Insert SQL Statement from a .csv file

I need some help, ideas, support at the beginning.

I want to write an application in java, which reads a .csv file and creates a .sql file with the appropriate Insert statements.

For Example:

Java Foundation;Java;15
Excel Tutorial;Excel;25


In this case I am a little bit unknowing and don't know how to start properly.

Thanks ahead for your help!

String table = p1.getFileName().toString();
table = table.substring(0,table.indexOf("."));

final String template = "INSERT INTO " +table+ " (%s,%s,%s) VALUES (%s,%s,%s);";
final String delimiter = ";";

Answer Source

You can use the java non-blocking class Files to read all the lines of your file using the readAllLines method and then use a template String and format it with the correct values using the String format method.

You then write your statements to a .sql using the write method in java.nio.Files.

    final String template = "INSERT INTO TABLE(%s,%s,%s) VALUES ('%s','%s',%s);";
    final String delimiter = ";";

    List<String> statements = new ArrayList<>();

    List<String> lines = Files.readAllLines(Paths.get("your-file.csv"));
    String[] columnNames = lines.get(0).split(delimiter);

    for (int i = 1; i < lines.size(); i++) {
        String[] values = lines.get(i).split(delimiter);
        statements.add(String.format(template, columnNames[0], columnNames[1], columnNames[2], values[0], values[1], values[2]));

    Files.write(Paths.get("your-output.sql"), statements);

That is a short and simple way to achieve what you are asking. You will need to swap the word TABLE for your table name of course :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download