Win a copy of High Performance Python for Data Analytics this week in the Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Bear Bibeault
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Jj Roberts
  • Carey Brown
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

Help reading data from file into postgreSQL table

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:



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)




Any help with either my idea for the dynamic 2d array or my existing method would be hugely appreciated!
 
Saloon Keeper
Posts: 6803
162
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Marshal
Posts: 71760
312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:-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
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:-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.

 
Saloon Keeper
Posts: 23060
157
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 71760
312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Get meta with me! What pursues us is our own obsessions! But not this tiny ad:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic