Help reading data from file into postgreSQL table

[Daniel Chivelly]

Hello

I am trying to write a method to insert data from a text file into a postgresql table.

data follows a structure as follows:

ABR,Aberdeen Regional Airport,Aberdeen,SD
ABI,Abilene Regional Airport,Abilene,TX
ADK,Adak Airport,Adak Island,AK

My idea was to read the file into a dynamic 2D array and then convert it to a string to be converted to a statement to execute, which I am unsure of how to do

Below is my current method that gives the error message for the first column from the above data example:

 org.postgresql.util.PSQLException: ERROR: value too long for type character varying(3)

I get this error message even though as you can see from the example data I gave the characters for the first column are only length of 3 (ABR,ABI,ADK)

public static int insertIntoTableFromFile(Connection connection, String table, String file) {

    BufferedReader br = null;
    int retVal = 0;
    try {
      PreparedStatement newStatement = connection.prepareStatement();
      String thisLine, lineArr[], finalLine = "";
      br = new BufferedReader(new FileReader(file));

      while ((thisLine = br.readLine()) != null) { // Insert each line to the DB
        lineArr = thisLine.split("\t"); // split the string with tab
        finalLine = "INSERT INTO " + table + " VALUES (";
        int i;

        for (i = 0; i < lineArr.length - 1; i++) {
          finalLine += "'" + lineArr[i] + "',";
        }
        finalLine += "'" + lineArr[i] + "')";
        retVal = newStatement.executeUpdate(finalLine);
      }

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

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {

      try {

        if (br != null)
          br.close();

      } catch (IOException ex) {
        ex.printStackTrace();
      }
    }
    return retVal;
  }

Any help with either my idea for the dynamic 2d array or my existing method would be hugely appreciated!

[Tim Moores]

Post the SQL declaration of the table in question.

Also, print out the SQL you generate (the "finalLine" variable) - that's hugely helpful during debugging.

I note that you split the string at tab characters, but the example data you posted seems to be comma-separated.

The approach is wide open to SQL injection attacks - can you vouch for the integrity of the data? Or is this just a manual one-off task?

[Campbell Ritchie]

Daniel Chivelly wrote:. . . a dynamic 2D array and then convert it to a string  . . .

Nonononono! Don't mess about with arrays, least of all with nesting, and don't use Strings when you could write a proper datatype. Create an airport class with code and name and location fields. Consider verifying those fields before creating the objects, so the code field would have three capital letters. Depending on how experienced you are, you can test whether the code matches a suitable regular expression, or you can test its length (=3) and that each of its constituent characters is an upper‑case letter. If not, throw an illegal argument exception and work out what is happening. Maybe airports are allowed to have four letters in their codes (Wikipedia page). Anyway, it appears that you are trying to put something longer than three letters into an SQL field taking three letters.

How are you splitting the line? Why are you using \t? I can see no tabs in what you showed; the text seems to be separated by commas. Splitting on tabs won't work, and won't produce a bit of text three letters long.
* * * * * * *
Don't try reading the file and inserting into the database in the same place if you can help it. Separate the concerns and separate the problems which might arise. That isn't the right way to create and close a buffered reader any more.
Use BufferedReader reader = Files.newBufferedReader(Paths.get(textToPath));
Don't call close(): use try with resources instead:-

(BufferedReader reader = Files.newBufferedReaderPaths.get(textToPath)))
{
   String line;
   while ((line = reader.nextLine()) != null)
   {
      Airport a = new Airport(line);
      // ...
   }
}
/* Not necessary nor advisible to call close() */
catch (IOException | SQLException ex)
{
   // ...
}

Since you are doing the same for both kinds of exception, try a multi‑catch instead (as above).
Avoid += on Strings; the problem isn't as bad as it used to be, but you are risking slow execution. Use a prepared statement, or use a StringBuilder object to build your query text.

[Daniel Chivelly]

haha I apologise if what I suggested had made you split hairs. All your points are very helpful as usual so thank you, This is only for a university project so the requirements aren't crazy extensive and I don't feel comfortable trying things for this particular assignment.

I'm required to load data from a file and insert that data into a table thats created in another section, I was splitting using \t as I thought that would split each row/record, i've realised now that the statement >thisLine = br.readLine()< already splits it by line, I've replaced the .split("\t") with .split(",") and it works fine for one of my calls to the method which tells me the other issue isn't to do with this method! I will look to implement your other advice about bufferedreader etc also.

Campbell Ritchie wrote:

Daniel Chivelly wrote:. . . a dynamic 2D array and then convert it to a string  . . .

Nonononono! Don't mess about with arrays, least of all with nesting, and don't use Strings when you could write a proper datatype. Create an airport class with code and name and location fields. Consider verifying those fields before creating the objects, so the code field would have three capital letters. Depending on how experienced you are, you can test whether the code matches a suitable regular expression, or you can test its length (=3) and that each of its constituent characters is an upper‑case letter. If not, throw an illegal argument exception and work out what is happening. Maybe airports are allowed to have four letters in their codes (Wikipedia page). Anyway, it appears that you are trying to put something longer than three letters into an SQL field taking three letters.

How are you splitting the line? Why are you using \t? I can see no tabs in what you showed; the text seems to be separated by commas. Splitting on tabs won't work, and won't produce a bit of text three letters long.
* * * * * * *
Don't try reading the file and inserting into the database in the same place if you can help it. Separate the concerns and separate the problems which might arise. That isn't the right way to create and close a buffered reader any more.
Use BufferedReader reader = Files.newBufferedReader(Paths.get(textToPath));
Don't call close(): use try with resources instead:-

(BufferedReader reader = Files.newBufferedReaderPaths.get(textToPath)))
{
   String line;
   while ((line = reader.nextLine()) != null)
   {
      Airport a = new Airport(line);
      // ...
   }
}
/* Not necessary nor advisible to call close() */
catch (IOException | SQLException ex)
{
   // ...
}

Since you are doing the same for both kinds of exception, try a multi‑catch instead (as above).
Avoid += on Strings; the problem isn't as bad as it used to be, but you are risking slow execution. Use a prepared statement, or use a StringBuilder object to build your query text.

[Tim Holloway]

That's a CSV format-file.

The psql command-line utility has a command to load CSV data directly, but if you want to do it in Java, there is a library that can read CSV data and extract the elements.

We should mention that it's better to construct a PreparedStatement to do your insertion. Aside from the usual SQL Injection attack  warnings - which presumably aren't a problem when using your own files - one person named O'Brian in the data can muck up hand-assembled SQL.

[Campbell Ritchie]

Daniel Chivelly wrote:haha I apologise

You have done nothing to apologise for.

if what I suggested had made you split hairs. . . . thank you,

That's a pleasure

This is only for a university project so the requirements aren't crazy extensive and I don't feel comfortable trying things for this particular assignment.

You aren't at University simply to learn things, but to learn how to learn things. When I was an undergraduate, we learnt from lectures, practicals, books, papers, and one another. Now you also have the opportunity to learn from experienced people on fora; if we try to help you, please accept that help.

. . . , i've realised now that the statement >thisLine = br.readLine()< already splits it by line . . .

You would have known that if you had read the documentation beforehand. Unless you are using something you are very familiar with, read its documentation. But your comment makes me wonder whether you understand where the splitting problem is occurring. Remember that lines aren's terminated by tabs.

Please don't quote the whole of the preceding post.