Importing .csv into SQLite in Java

Question

I have to write a programm that automatically reads out of my .csv and puts the data into my SQLite. This code fills up the first line in my table, which I created before. How to I skip/go to the next line, to fill it up?

I guess its something in the loop, but Im not sure what to change...

(I skipped the first line, because it contains the columns.)

Code:

public static void read()
 {
  String csv = "C:\\Users\\schneider\\Desktop\\Schneider\\Excel\\DienstbarkeitenTabelle.csv";
  BufferedReader br = null;
  String line = "";
  String csvSplitBy = ",";
  int currentLine = 0;
  Connection conn = null;

  try
  {
      conn = DriverManager.getConnection("jdbc:sqlite:C:/sqlite/db/tollerName");
      PreparedStatement prep = conn.prepareStatement("insert into tollerName(Bauprojekt, Bauprojektnummer, Auftragsnummer,Trassierung, JahrSAP, ProjektnummerSPA, Anlagenzahl, AktuelleLeitungslaenge, VorgaengeZumProjekt, VertraegeZumProjekt, Firma, Flurst, Blatt, Verf, Anl, RE, LL, BreiteSS, FlaecheRA, Entsch, Haupteigentuemer, Gemarkung, Grundbuchamt, Strase, Hausnummer, Hausnummerzusatz, Ort, plz, VkWert, prozentBPD, Anlage, GFR, GBA, Empfaenger, Bank, IBAN, BIC, von, an, JahrDerVergabe, Zahlungsgrund, Netto, Aufwand, Umsatzsteuer) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

      br = new BufferedReader(new FileReader(csv));
      while((line = br.readLine()) != null)
      {
          if(currentLine > 0)
          {
              String[] table = line.split(csvSplitBy);
              prep.setString(1, (table[0]));
              prep.setString(2, (table[1]));
              prep.setString(3, (table[2]));
              prep.setString(4, (table[3]));
              prep.setString(5, (table[4]));
              prep.setString(6, (table[5]));
              prep.setInt(7, Integer.parseInt(table[6]));
              prep.setDouble(8, Double.parseDouble(table[7]));
              prep.setInt(9, Integer.parseInt(table[8]));
              prep.setInt(10, Integer.parseInt(table[9]));
              prep.setString(11, (table[10]));
              prep.setString(12, (table[11]));
              prep.setString(13, (table[12]));
              prep.setString(14, (table[13]));
              prep.setString(15, (table[14]));
              prep.setString(16, (table[15]));
              prep.setDouble(17, Double.parseDouble(table[16]));
              prep.setDouble(18, Double.parseDouble(table[17]));
              prep.setDouble(19, Double.parseDouble(table[18]));
              prep.setDouble(20, Double.parseDouble(table[19]));
              prep.setString(21, (table[20]));
              prep.setString(22, (table[21]));
              prep.setString(23, (table[22]));
              prep.setString(24, (table[23]));
              prep.setString(25, (table[24]));
              prep.setString(26, (table[25]));
              prep.setString(27, (table[26]));
              prep.setString(28, (table[27]));
              prep.setDouble(29, Double.parseDouble(table[28]));
              prep.setDouble(30, Double.parseDouble(table[29]));
              prep.setString(31, (table[30]));
              prep.setString(32, (table[31]));
              prep.setString(33, (table[32]));
              prep.setString(34, (table[33]));
              prep.setString(35, (table[34]));
              prep.setString(36, (table[35]));
              prep.setString(37, (table[36]));
              prep.setString(38, (table[37]));
              prep.setString(39, (table[38]));
              prep.setString(40, (table[39]));
              prep.setString(41, (table[40]));
              prep.setDouble(42, Double.parseDouble(table[41]));
              prep.setDouble(43, Double.parseDouble(table[42]));
              prep.setDouble(44, Double.parseDouble(table[43]));
              prep.execute();
              conn.commit();
          }
          currentLine++;
      }
  }

  catch (Exception e)
  {
      e.printStackTrace();
  }

  finally
  {
      if(br == null)
      try
      {
          br.close();
      }

      catch (IOException e)
      {
          e.printStackTrace();
      }
  }

  System.out.println("müsste feddich sein");

}

Why don't you use SQLite directly? https://stackoverflow.com/a/24582022/9455968 — , Mar 27 '18 at 13:31
Surely i could do that, but i have to do that for a number of .csv and my task is to write code for that. — PilotKot, Mar 27 '18 at 13:41
You are only inserting one value in the prepared statement. For the column "Bauprojekt" the first value from the csv file. You need to add a value for every column. It is difficult to help without more information. — Flah, Mar 27 '18 at 14:16
You only insert, if `currentLine > 0`. The first time in your loop `currentLine` is 0, so the firste line will never be inserted. — vanje, Mar 27 '18 at 14:22
@Flah Thats a thing.. How do I get my second line into my SQLite? — PilotKot, Mar 28 '18 at 06:46

score 0 · Answer 1 · answered Mar 27 '18 at 14:23

0

You initialize currentLine to 0, the first time you pass in your loop the condition if(currentLine > 0) is false, that's why the first line is not inserted.

unless you have deleted some important part of your code the test is useless. currenltLine is only incremented starting from 0, it can never be negative nor null.

answered Mar 27 '18 at 14:23

StephaneM

4,779
1
16
33

My first line contains the columns... My problem is to get the other lines in there. sorry for not explaining that properly – PilotKot Mar 28 '18 at 06:48

Importing .csv into SQLite in Java

1 Answers1