GeeCON Prague 2014*
The moose likes JDBC and the fly likes insert CSV values from file to MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "insert CSV values from file to MySQL" Watch "insert CSV values from file to MySQL" New topic
Author

insert CSV values from file to MySQL

Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
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...
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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


My Blog: Down Home Country Coding with Scott Selikoff
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
yes i have tables that reflect the same structure as the CSV files.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
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.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
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.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
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.
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
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();
}
}

}
William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

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
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
i was writing the code specifically for comma separated values hence the code.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
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.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
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.
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
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.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
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.
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
the type of the files in the file properties is "Microsoft Excel Comma Separated Values File (.csv)"..it opens with MS-excel..
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
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?
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
@ Tim i already wrote the code for that and have included that in a previous post..
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

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?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

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...

Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
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?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

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.
Sama Mushtaq
Greenhorn

Joined: Apr 02, 2012
Posts: 19
thanks this is helpful..i will try to implement this in my code..
Dazz Vaids
Greenhorn

Joined: Jun 14, 2012
Posts: 1
Here is some php csv import scripts. You can view source code: http://viewphp.com/Miscellaneous%20Scripts/Data%20Import/
Lexington Smith
Ranch Hand

Joined: Sep 29, 2012
Posts: 67
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.
Lexington Smith
Ranch Hand

Joined: Sep 29, 2012
Posts: 67
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.
 
GeeCON Prague 2014
 
subject: insert CSV values from file to MySQL