data parsing from a file into java and then into a mysql database

Question

I have .Data file given in the above format . I am writing a program in java that will take the values from the .data file and put it in the buffer. MY java program is connected to Mysql(windows) via JDBC. So I need to read the values from the file given in the above format and put it the buffer like

Insert Into building values ("--", "---",----)

In this way, i store these values and jdbc will populate the database tables on Mysql(windows). Please tell me teh best way.

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

Check out the answers to this question for reading file lines and splitting them into chunks. I know the question says Groovy: but most answers are Java. Then insert the values you retrieved via JDBC.

Actually, since your data file is obviously CSV, you could also use a CSV libary like OpenCSV to read the values.

edited May 23 '17 at 11:56

Community

1
1

answered Nov 05 '10 at 08:52

Sean Patrick Floyd

292,901
67
465
588

the files are in the .data format. Though, they are seprated by commas, but I cant change the name into .csv files. Please elaborate it – Nov 05 '10 at 09:04
1

The file name is irrelevant, but CSV means Comma Separated Values, which is actually the case here. – Sean Patrick Floyd Nov 05 '10 at 09:06
CSVReader reader = new CSVReader(new FileReader("yourfile.csv")); in this, .csv is used. But in my file, it is .data. I am not supposed to change it. – Nov 05 '10 at 09:08
1

File names are just conventions, nothing more. Do this: `CSVReader reader = new CSVReader(new FileReader("yourfile.data"));` and it will work. – Sean Patrick Floyd Nov 05 '10 at 09:11
ok...let me try...plz be here for some time.I need your help .Or you can give me your email id so taht i can contact you there. Thanks – Nov 05 '10 at 09:13
can I use arg[1] in place of "youfile.csv".....because while executing , I am putting the name of the file.And the name of the file is placed at 2 no. So,arg[1]. Would it work? – Nov 05 '10 at 09:20
sure, it would work. beware: arrays are zero-based. args[1] is the second element of an array. Do you want that? If not, use args[0]. You need to learn some basics. Try the Java Tutorial: http://download.oracle.com/javase/tutorial/ – Sean Patrick Floyd Nov 05 '10 at 09:23
no, I am using args[0] for putting some othe file name that has the host and username, password to connect my mysql with java.But I am wondering, where I am storing the results after reading from the file.The format in which I have given you my data has that types of rows around 10-15. i guess, It would work. – Nov 05 '10 at 09:28
I am learning java basics while doing it. practice is best way to learn. – Nov 05 '10 at 09:28
*I am learning java basics while doing it.* Practice without theory is equally bad as theory without practice. You should really read a book or use the tutorial. Just learning by doing is a very rocky road. – Sean Patrick Floyd Nov 05 '10 at 09:35
actually I have project submission..so I dont hav much time to read it ...so I am just learning by doing it....anyways thanks for ur advice – Nov 05 '10 at 09:39
1

sure. For this assignment, I agree. I'm talking about a larger scope. I learned Java by doing (had programmed PHP, PERL and other hacked up languages before), and it took me years to realize all the mistakes I was making. When you do have the time (perhaps after the assignment), follow the tutorial and read Effective Java by Joshua Bloch. – Sean Patrick Floyd Nov 05 '10 at 09:43

score 1 · Answer 2 · answered Nov 05 '10 at 08:58

1

The data is in CSV format, so use a CSV library to parse the file and then just add some JDBC code to insert this into database.

Or just call MySQL CSV import command from Java:

try {
    // Execute a command with arguments
    String command = "mysqlimport [options] db_name textfile1 [textfile2 ...]";
    Process child = Runtime.getRuntime().exec(command);

} catch (IOException e) {
}

answered Nov 05 '10 at 08:58

Peter Knego

79,991
11
123
154

+1 for MySQL CSV import command (although I'd skip the middle man and call that directly, without any Java) – Sean Patrick Floyd Nov 05 '10 at 09:12

score 1 · Answer 3 · edited May 23 '17 at 12:04

This is the fourth question for the same task... If your data file is well formatted like in the example you provided, then you don't have to split the line into values:

Source:   "AAH196","Austin","TX","Virginia Beach","VA"
Target:   INSERT INTO BUILDING VALUES("AAH196","Austin","TX","Virginia Beach","VA");
      <=> "INSERT INTO BUILDING VALUES(" + Source + ");"

Just take a complete row from you csv file and concatenate a SQL expression.

(see my answer to question 1 of 4 - BTW, if SQL INJECTION is a potential problem, splitting a line of values is not a solution too)

score 0 · Answer 4 · answered Nov 05 '10 at 08:53

0

you can bind your csv with java beans using opencsv. http://opencsv.sourceforge.net/

you can make these beans persistent using an ORM framework, like Hibernate, Cayenne or with JPA which're based on annotations and map your fields to tables easily without creating any sql statement.

answered Nov 05 '10 at 08:53

Erhan Bagdemir

5,231
6
34
40

A bit heavyweight for an introductory programming assignment, no? – user359996 Nov 07 '10 at 17:53

score 0 · Answer 5 · answered Nov 05 '10 at 09:34

0

This would be a perfect job for Groovy. Here's a gist with a small skeleton script to build upon.

answered Nov 05 '10 at 09:34

sfussenegger

35,575
15
95
119

data parsing from a file into java and then into a mysql database

5 Answers5

Linked