CODEWITHSUNDEEP
javasqlfilecsvconverters
MatBanik
MatBanik

Reputation: 26860

Simpler CSV to SQL java file converter

Is there simpler way to convert CSV files to SQL file than this?:

BufferedReader input = new BufferedReader(new FileReader(new File("c:\\file.csv")));     
Writer output = new BufferedWriter(new FileWriter(new File("c:\\file.sql")));

try {
  String line = null;
  String[] st = null;

  while ((line = input.readLine()) != null) {
    st = line.replace("\"","").split(",");

    output.write("INSERT INTO `table` "
                    + "(`column_id`, `column1`, `column2`) VALUES "
                    + "(" + st[2] + ", " + st[0]  + ", " + st[1] +")"
                    + ";"
                    + "COMMIT;\n");

  }
} finally {
  input.close();
  output.close();
}

Upvotes: 3

Views: 7891

Answers (2)

James Anderson
James Anderson

Reputation: 27478

Use one of the readily available csv parser libraries like this one. Otherwise you will need a small state machine to deal with commas inside quoted strings etc.

I also see that you are not executing the SQL but writing the sql statements to a file. Most databases come with a load utility which will accept a csv file as input.

Upvotes: 1

javamonkey79
javamonkey79

Reputation: 17765

If you don't find a simpler solution be wary of doing a simple split on csv data. Speaking from experience this is valid csv that will break a simple split solution:

"field 1a, field 1b", field2, "field 3a, field3b", field4

I would suggest looking at a library such as opencsv to handle csv parsing for you.

Upvotes: 1

Related Questions