insert CSV values from file to MySQL

so i have to build an application where i have to read values from a csv file(excel)and insert them in mySQL.i can do the reading part however I'm fairly stumped by how do i insert the values to the database.
Any help regarding this matter is apprecaited...

Do you have a table in the database with a structure that can hold the CSV data?

yes i have tables that reflect the same structure as the CSV files.

In that case there are tools that will bulk load it but if your building application yourself, you can just parse the CSV file line by line, and construct the insert using PreparedStatement as needed. I also recommend using the batch options in the PreparedStatement API, since they are effective when loading large chunks of data at once.

yes im building the application myself and being new to java i feel its particularly hard to do database stuff,is there any example that i can refer to?..thanks for the help.

You should start with a simple JDBC tutorial then.

Note- There are open source CSV libraries that will parse the CSV file for you, but as this can be accomplished in a few lines of code, I'm not sure they are really needed in a lot of situations.

Scott Selikoff wrote:There are open source CSV libraries that will parse the CSV file for you, but as this can be accomplished in a few lines of code, I'm not sure they are really needed in a lot of situations.

Since we have no idea what's in that CSV file we should not assume they're of the variety that's "easy" - so I would still recommend to use such a library. Since the post talks about "CSV (Excel)" it sounds to me as of the file is originally really an Excel file from which a CSV file is derived. If that's the case I'd just read the Excel file (using the Apache POI library) instead of exporting to a CSV at all.

There are some subtleties involving escaping characters, quoted characters, changing the deliminator from a comma to "something else", but in many situations a few lines of code is all you need to read and parse a CSV file.

In fact, this example for reading lines of a file plus the String.split() command will get you a long way, assuming you don't have a lot of complexities such as using quotes.

I think im not ambiguous here.what i meant that i need import values from an excel file to an database.actually its a folder that has a number of excel files all of them need to be stored in the database.but im working on transferring values from only 1 file as of now.

this is the original program that i use to parse and it works well: however now im trying to insert the values in the db instead of displaying them on the console
package database;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.util.StringTokenizer;

public class MySQLConnect {

/**
*
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
String fileName="C:/Users/Sama/Desktop/dod001_fragmented_drive_20120130.csv";
try {
BufferedReader br = new BufferedReader( new FileReader(fileName));
String strLine = null;
StringTokenizer st = null;
int lineNumber = 0, tokenNumber = 0;

while( (fileName = br.readLine()) != null)
{
lineNumber++;

//break comma separated line using ","
st = new StringTokenizer(fileName, ",");

while(st.hasMoreTokens())
{
//display csv values
tokenNumber++;
System.out.println("Line # " + lineNumber +
", Token # " + tokenNumber
+ ", Token : "+ st.nextToken());
}

//reset token number
tokenNumber = 0;

}
}

catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}

Your code will fail if a quoted string contains a comma (,)
e.g: 1,2,3,"Mushtaq, Sama",5,6,"Other String"

Look at: http://opencsv.sourceforge.net/

Annotate or use Hibernate to map the Class to underlying DB table/columns, the save() it.

WP

As I already said, there are some situations it will fail, but many it will do fine, such as if the CSV does not contain quotes. Either way, String.split() takes a regular expression, not a single comma, so you can get creative (or just ask Google). It all depends on how much control you have on the inputted data.

i was writing the code specifically for comma separated values hence the code.

Scott, why are you insisting on the simple way when there is no evidence one way or the other whether it will suffice? I'll happily grant that simpler is better, but pointing out that simple may not do is perfectly fine IMO. In fact, the latest post by Sama seems to indicate that he/she is not really aware of the intricacies of CSV. So a general solution seems to be appropriate.

Well, we're a bit off topic but... I'm a big proponent of open source libraries but if I can do the same thing with 5-6 lines of code, I don't bother with them. It's that simple.

I fully agree - once I know that those few lines of code will be up to the job. Which in this case we don't (yet). Maybe Sama will tell us some more about the underlying data so that we know for sure.

the files that Im working on are excel worksheets.I tried uploading one as an example but apparently I cant attach a .csv file to my reply.

I'm confused - Excel files are .xls or .xlsx, not .csv. As I said before, if the files in question are actual Excel files, I'd use Apache POI to deal with them instead of exporting them to CSV.

the type of the files in the file properties is "Microsoft Excel Comma Separated Values File (.csv)"..it opens with MS-excel..

OK, so it is a CSV file. You've got two possibilities: write the CSV parsing code yourself (which is perfectly feasible if the contents are simple, like if they only contain numbers), or use a library that does that for you. Both options have been discussed at length above. Which route do you want to go?

@ Tim i already wrote the code for that and have included that in a previous post..

Okay, so now that the CSV issue is sorted out, your question is about how to insert the data into the database. Well, the short answer to that is that you would use an SQL INSERT statement. I expect you probably knew that, but at any rate you haven't said what specifically your problem is regarding JDBC. So how about if you clarify just what it is that you don't know, or can't do?

On the other hand I wouldn't be writing Java code to do this database import, not as my first choice. I would be starting from this page: mysqlimport — A Data Import Program. There's a post attached to that page which starts

If you are one of the many people trying to import a CSV file into MySQL using mysqlimport under MS-Windows...

my problem at this point of time is that i dont understand how to insert the values in a pre-defined table in the db.the code displays the values on the console so should i store it in an object/intermediate structure or simple embed the code for parsing in the JDBC?

Sama Mushtaq wrote:the code displays the values on the console so should i store it in an object/intermediate structure or simple embed the code for parsing in the JDBC?

Sorry, that doesn't make any sense. "Parsing" is where you convert a line of CSV into a list of fields. That has nothing to do with JDBC at all.

At any rate if you're stuck with writing Java code for this task, here's the pseudo-code for how to do that:

Don't try to lump those two sub-tasks into a big ball of spaghetti -- just write code to do one and then the other.

thanks this is helpful..i will try to implement this in my code..

Here is some php csv import scripts. You can view source code: http://viewphp.com/Miscellaneous%20Scripts/Data%20Import/

Sama Mushtaq wrote:so i have to build an application where i have to read values from a csv file(excel)and insert them in mySQL.i can do the reading part however I'm fairly stumped by how do i insert the values to the database.
Any help regarding this matter is apprecaited...

If you don't mind, can you give me the names of the columns in your CSV file ?
It should be doable.

Tim Moores wrote:Scott, why are you insisting on the simple way when there is no evidence one way or the other whether it will suffice? I'll happily grant that simpler is better, but pointing out that simple may not do is perfectly fine IMO. In fact, the latest post by Sama seems to indicate that he/she is not really aware of the intricacies of CSV. So a general solution seems to be appropriate.

Tim, you may be right. But it is also possible that sama has to do a simple extract and dump. In that case, a few lines of code will do the job. Learning how to use new API for that will be a steep
learning curve.