Java: How to Load CSV file into Database

by Viral Patel · November 2, 2012

Loading CSV file into Database can be cumbersome task if your Database provider does not offer an out of box feature for this. Most of the time you’ll spend up in creating valid insert statements and putting up values escaping all special characters. Importing CSV files gets a bit complicated when you start doing things like importing files with description fields that can contain punctuation (such as commas or single-double quotation marks). So here’s a simple Java Utility class that can be used to load CSV file into Database. Note how we used some of the best practices for loading data. The CSV file is parsed line by line and SQL insert query is created. The values in query are binded and query is added to SQL batch. Each batch is executed when a limit is reached (in this case 1000 queries per batch).

Import CSV into Database example

Let’s us check an example. Below is the sample CSV file that I want to upload in database table Customer. employee.csv – Sample CSV file:

EMPLOYEE_ID,FIRSTNAME,LASTNAME,BIRTHDATE,SALARY
1,Dean,Winchester,27.03.1975,60000
2,John,Winchester,01.05.1960,120000
3,Sam,Winchester,04.01.1980,56000
Code language: CSS (css)

The Table customer contains few fields. We added fields of different types like VARCHAR, DATE, NUMBER to check our load method works properly. Table: Customer – Database table

CREATE TABLE Customer (
  EMPLOYEE_ID  NUMBER,
  FIRSTNAME    VARCHAR2(50 BYTE),
  LASTNAME     VARCHAR2(50 BYTE),
  BIRTHDATE    DATE,
  SALARY       NUMBER
)
Code language: SQL (Structured Query Language) (sql)

Following is a sample Java class that will use CSVLoader utility class (we will come to this shortly). Main.java – Load sample.csv to database

package net.viralpatel.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {

	private static String JDBC_CONNECTION_URL = 
			"jdbc:oracle:thin:SCOTT/TIGER@localhost:1500:MyDB";

	
	public static void main(String[] args) {
		try {

			CSVLoader loader = new CSVLoader(getCon());
			
			loader.loadCSV("C:\\employee.sql", "CUSTOMER", true);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static Connection getCon() {
		Connection connection = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(JDBC_CONNECTION_URL);

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

		return connection;
	}
}
Code language: Java (java)

In above Main class, we created an object of class CSVLoader using parameterized constructor and passed java.sql.Connection object. Then we called the loadCSV method with three arguments. First the path of CSV file, second the table name where data needs to be loaded and third boolean parameter which decides whether table has to be truncated before inserting new records. Execute this Java class and you’ll see the records getting inserted in table.

The CSV is successfully loaded in database. Let’s check the Utility class now. I strongly recommend you to go through below tutorials as the Utility class combines the idea from these tutorials.

The utility class uses OpenCSV library to load and parse CSV file. Then it uses the idea of Batching in JDBC to batch insert queries and execute them. Each CSV value is checked if it is valid date before inserting. CSVLoader.java – Utility class to load CSV into Database

package net.viralpatel.java;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

import au.com.bytecode.opencsv.CSVReader;

/**
 * 
 * @author viralpatel.net
 * 
 */
public class CSVLoader {

	private static final 
		String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
	private static final String TABLE_REGEX = "\\$\\{table\\}";
	private static final String KEYS_REGEX = "\\$\\{keys\\}";
	private static final String VALUES_REGEX = "\\$\\{values\\}";

	private Connection connection;
	private char seprator;

	/**
	 * Public constructor to build CSVLoader object with
	 * Connection details. The connection is closed on success
	 * or failure.
	 * @param connection
	 */
	public CSVLoader(Connection connection) {
		this.connection = connection;
		//Set default separator
		this.seprator = ',';
	}
	
	/**
	 * Parse CSV file using OpenCSV library and load in 
	 * given database table. 
	 * @param csvFile Input CSV file
	 * @param tableName Database table name to import data
	 * @param truncateBeforeLoad Truncate the table before inserting 
	 * 			new records.
	 * @throws Exception
	 */
	public void loadCSV(String csvFile, String tableName,
			boolean truncateBeforeLoad) throws Exception {

		CSVReader csvReader = null;
		if(null == this.connection) {
			throw new Exception("Not a valid connection.");
		}
		try {
			
			csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error occured while executing file. "
					+ e.getMessage());
		}

		String[] headerRow = csvReader.readNext();

		if (null == headerRow) {
			throw new FileNotFoundException(
					"No columns defined in given CSV file." +
					"Please check the CSV file format.");
		}

		String questionmarks = StringUtils.repeat("?,", headerRow.length);
		questionmarks = (String) questionmarks.subSequence(0, questionmarks
				.length() - 1);

		String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
		query = query
				.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
		query = query.replaceFirst(VALUES_REGEX, questionmarks);

		System.out.println("Query: " + query);

		String[] nextLine;
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = this.connection;
			con.setAutoCommit(false);
			ps = con.prepareStatement(query);

			if(truncateBeforeLoad) {
				//delete data from table before loading csv
				con.createStatement().execute("DELETE FROM " + tableName);
			}

			final int batchSize = 1000;
			int count = 0;
			Date date = null;
			while ((nextLine = csvReader.readNext()) != null) {

				if (null != nextLine) {
					int index = 1;
					for (String string : nextLine) {
						date = DateUtil.convertToDate(string);
						if (null != date) {
							ps.setDate(index++, new java.sql.Date(date
									.getTime()));
						} else {
							ps.setString(index++, string);
						}
					}
					ps.addBatch();
				}
				if (++count % batchSize == 0) {
					ps.executeBatch();
				}
			}
			ps.executeBatch(); // insert remaining records
			con.commit();
		} catch (Exception e) {
			con.rollback();
			e.printStackTrace();
			throw new Exception(
					"Error occured while loading data from file to database."
							+ e.getMessage());
		} finally {
			if (null != ps)
				ps.close();
			if (null != con)
				con.close();

			csvReader.close();
		}
	}

	public char getSeprator() {
		return seprator;
	}

	public void setSeprator(char seprator) {
		this.seprator = seprator;
	}

}
Code language: Java (java)

The class looks complicated but it is simple :) The loadCSV methods combines the idea from above three tutorials and create insert queries. Following is the usage of this class if you want to use it in your project: Usage

CSVLoader loader = new CSVLoader(connection);
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true);
Code language: Java (java)

Load file with semicolon as delimeter:

CSVLoader loader = new CSVLoader(connection);
loader.setSeparator(';');
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true);
Code language: Java (java)

Load file without truncating the table:

CSVLoader loader = new CSVLoader(connection);
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", false);
Code language: Java (java)

Hope this helps.

Download Source Code

Load_CSV_Database_Java_example.zip (2.05 MB)

Tags: Java java 5 java code

73 Comments

Mohammad Tariq says:
7 November, 2012, 10:59
Hi,
is there or could there be a way by which we can show to the user the progress of the database operations performed so far ???
Reply
Vipul says:
28 November, 2012, 10:01
Hi, Viral
Please change argument from following line :
.loadCSV(“C:\\employee.sql”, “TABLE_NAME”, false);
instead of C:\\employee.sql ,it should be C:\\employee.csv.
It will be very useful for other people to directly understand your code without confusion.
Reply
- Viral Patel says:
  28 November, 2012, 13:55
  Opps :) Thanks for that. I updated the code.
  Reply
Fuad says:
29 November, 2012, 7:19
What if the database had a password for it? I am using Postgresql as my database…
Reply
- Viral Patel says:
  29 November, 2012, 13:47
  Hi Faud, The CSVLoader class takes Connection object as input. You can get connection object to Postgresql like below:
```
Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection(
   "jdbc:postgresql://hostname:port/dbname","username", "password");
//..
CSVLoader loader = new CSVLoader(connection);
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true);
```
  Hope this helps.
  Reply
Zack says:
29 November, 2012, 12:37
I am getting error (java.sql.BatchUpdateException: Batch entry 0 INSERT INTO ) when running the Main.java. Why is that happening?
Reply
- Viral Patel says:
  29 November, 2012, 13:52
  Hi Zack, This exception comes whenever there is error in batch update operation. The count indicates how many queries were executed successfully. In this case 0. Please check if the database table is present and you are able to connect properly.
  I couldn’t get more details from this error message.
  Reply
  - Zack says:
    29 November, 2012, 22:10
    Lets say I have a CSV file that contain 100 records and 22 column. Is using batch update is suitable or is there another way to insert the data, 1 record per times,update it until it finish? I hope you understand what I want to say.Sorry for the trouble.
    Reply

Zack says:

30 November, 2012, 14:59

Here is my schema in the table database and the column inside the CSV files is the same.

CREATE TABLE patient_registration
(
  id bigint NOT NULL,
  fullname character varying(255),
  icnumber character varying(100),
  gender character varying(10),
  placeofbirth character varying(100),
  citizenship character varying(100),
  religion character varying(50),
  race character varying(50),
  sector character varying(50),
  occupation character varying(100),
  maritalstatus character varying(50),
  mobileno character varying(25),
  officeno character varying(25),
  phoneno character varying(25),
  email character varying(255),
  address1 character varying(100),
  address2 character varying(100),
  address3 character varying(100),
  address4 character varying(100),
  postcode character varying(100),
  state character varying(100),
  CONSTRAINT patient_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE patient_registration
  OWNER TO postgres;
GRANT ALL ON TABLE patient_registration TO postgres;

I think I need to change a bit in this code in CSV Loader but I’m still cant figure it out.Hope you can help.

final int batchSize = 1000;
			int count = 0;
			Date date = null;
			while ((nextLine = csvReader.readNext()) != null) {

				if (null != nextLine) {
					int index = 1;
					for (String string : nextLine) {
						date = DateUtil.convertToDate(string);
						if (null != date) {
							ps.setDate(index++, new java.sql.Date(date
									.getTime()));
						} else {
							ps.setString(index++, string);
						}
					}
					ps.addBatch();
				}
				if (++count % batchSize == 0) {
					ps.executeBatch();
				}
			}
			ps.executeBatch(); // insert remaining records
			con.commit();
		} catch (Exception e) {
			con.rollback();
			e.printStackTrace();
			throw new Exception(
					"Error occured while loading data from file to database."
							+ e.getMessage());
		} finally {
			if (null != ps)
				ps.close();
			if (null != con)
				con.close();

			csvReader.close();
		}
	}

Rocky says:
5 December, 2012, 9:42
What if my primary key is a bigInt type? The Java error says that my column ‘id’ in the database is a type of bigInt, while the value that been passed in the Java code from the CSV file is recognised as character varying type… The value in the column id is like this: id, 1,2,3
Reply
Sanket Garg says:
20 December, 2012, 16:10
Viral if i use Derby at back end… package my java application in JAR format and den deploy it in client environment den do we have to install Derby on systems on which we intend to use our java desktop application or will that go embedded into JAR…
Reply
abhijeet says:
11 January, 2013, 11:33
I am getting error in reading the data when data field has “\ “. can you help in this ?
Reply
- Sandesh says:
  13 January, 2016, 12:31
  Hi Viral,
  I am also getting same error . “\” get replace by blank while inserting into DB.
  Can you pls help on this.
  thanks for such good article.
  Reply
sarthak says:
4 March, 2013, 12:58
Cant we create the table automatically from the java code itself by reading the first line of csv file? obviously taking all columns as strings. This would be generalized to all csv files then..
Reply
Arif says:
7 March, 2013, 10:06
I am getting the following error while running the Main Class
Exception in thread “main” java.lang.UnsupportedClassVersionError: Main (Unsuppo
rted major.minor version 50.0)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
Pls help me
Reply
sandhya patil says:
26 March, 2013, 18:27
i have a schema in mysql with the system date as one field. i want to add the system date from the CSV into the mysql . i tried it using CURDATE() but its in vain.
can u please, suggest me.
thanks in advance :)
Reply
Claire says:
9 April, 2013, 6:06
Hi, I tried to type my own code. But when I’m typing
import org.apache.commons.lang.StringUtils;
import au.com.bytecode.opencsv.CSVReader;
It always show an error.
I downloaded the java project, and it works fine. Do you know why it’s like this?
Reply
- Satyajit says:
  24 June, 2016, 11:35
  Search the package of those files, and add those jars in your build path
  Reply
Sharocks says:
22 May, 2013, 15:46
Hi, in my csv file, I have date column in the format “m/d/yyyy h:mm” and the corresponding column in DB is “datetime” datatype. When I import using this program, date is importing properly, but hh:mm:ss is 00:00:00 in DB. Can you please provide an input on how to import exactly the same data?.
Reply
Manohar says:
25 May, 2013, 20:32
lol.. he s a supernatural fan :)
Reply
Manohar says:
25 May, 2013, 20:36
For exporting entire database into CSV file and vice versa you can see my project
https://sourceforge.net/projects/databasetocsv/
Source:
https://github.com/overtomanu/Database7
Reply
vinn says:
4 July, 2013, 13:32
public class Emp {
public static void main (String ar[]) throws Exception{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con= DriverManager.getConnection
(“jdbc:oracle:thin:@localhost:1521:xe”,”scott”,”tiger”);
Statement stmt = con.createStatement();
stmt.executeUpdate(“&eno,&ename,&salary”);
}
}
[/code
why v r getting error: invalid sql stmt
Reply
pankaj says:
24 July, 2013, 9:44
Its a very good tutorial but what if i want load 2 CSV’s can u please help me ?
Reply
surajtikoo says:
7 August, 2013, 20:32
java.lang.Exception: Not a valid connection.goodbye
at net.viralpatel.java.CSVLoader.loadCSV(CSVLoader.java:55)
at net.viralpatel.java.Main.main(Main.java:22)
Dear sir as per the changes given by you for csv file i done it but it is not able to connect this time
as the program is not able to call the getconn method.Can u please help me..
Following are the changes which i done as given by u..
CSVLoader loader = new CSVLoader(connection);
loader.loadCSV(“C:\\employee.csv”, “TABLE_NAME”, true);
Reply
surajtikoo says:
7 August, 2013, 21:30
Query: INSERT INTO DAY_STATUS_NEW(DAY_STATUS_ID,CODE_R,STOCK_ID,CLOSE) VALUES(?)
my csv file not able to iterate to fetch all the rows it is fetching only the first row.. Please help me
Reply
Sharadha says:
14 August, 2013, 0:04
Hi Viral,
Thanks for the Program. I implemented ur code it worked fine and Now i need to update the timestamp column in the db2 table simultaneously when .csv file is extracted to db.
This timesatmp column keep the track when the .csv file is imported to db.
Example: In Db2 table column
Eng Social Maths TimeStamp
In .CSV file has only 3 columns Eng Social Maths . When .csv file is imported to db2 all the columns fields are updated except TimeStamp. So, how to Update the TimeStamp column with System timestamp simultaneously ? Please help
Reply
Siddharth says:
26 August, 2013, 19:29
Nyc Supernatural example!!!!
Reply
- jowiat says:
  15 February, 2017, 21:20
  I was hoping I wasn’t the only one that noticed this
  Reply
Maulik says:
28 August, 2013, 19:10
Hi Viral
Thanks for the detailed blog, I was wondering is there any way to programatically know what is the encoding (UTF-8, UTF-16, Japanese SHIFT JIS, etc) of the CSV file.
I am getting a CSV file from some where and I need to process it. While processing I make the assumption of using UTF-8 encoding, but if the user gives me file which is special character (non-english letters) my processing barfs out with exception. Hence I was wondering is there a way to know the encoding of CSV file or open it in only UTF-8 encoding.
Thanks
Reply
anjaneyulu says:
14 September, 2013, 17:11
cna not find CSVLoader loader = new CSVLoader(getCon()); in java class
Reply
anjaneyulu says:
14 September, 2013, 17:17
can not find CSVLoader loader = new CSVLoader(getCon()); in java class
is any class path settings for CSVLoader class loader or if any
Reply
Fadli says:
30 September, 2013, 12:11
halo Viral,
how I can use semicolon ( ; ) as delimiter for value,
i was try your code
```
CSVLoader loader = new CSVLoader(getCon());
loader.setSeprator(';');
loader.loadCSV("C:\\Log\\Logtima.csv", "coreservice", true);
```
but that’s not work for me,
can you fix it??
Reply
Fadli says:
30 September, 2013, 13:57
Hallo Viral,
I want to make a semicolon (;) as demiliter like you wrote on your blog,
CSVLoader loader = new CSVLoader(getCon());
loader.setSeprator(‘;’);
loader.loadCSV(“C:\\Log\\Logtima.csv”, “coreservice”, true);
but this code not work for me,
I got this error ~
uery: INSERT INTO coreservice(MODULE_NAME,SERVICE_NAME,COUNTER,LOG_DATE,UPDATE_DATE) VALUES(?)
java.sql.BatchUpdateException: Column count doesn’t match value count at row 1
can you fix it,
thanks in advance, dude :)
Reply
sham kumar says:
27 November, 2013, 23:41
lovely webpage,
beautifully explained
i am going to try this now :-), thanks a lot.
Reply
amit says:
9 December, 2013, 12:46
The Delete from table is done in a single statement… is it better to do it in a BATCH ??
Reply
Javed says:
29 January, 2014, 12:02
Query: INSERT INTO CUSTOMER(EMPLOYEE_ID,FIRSTNAME,LASTNAME,BIRTHDATE,SALARY) VALUES(?)
java.sql.BatchUpdateException: ORA-00947: not enough values
its not working.
Reply
hema says:
2 February, 2014, 0:25
hey i want to know what happens with thess line?
String questionmarks = StringUtils.repeat(“?,”, headerRow.length);
questionmarks = (String) questionmarks.subSequence(0, questionmarks
.length() – 1);
String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
query = query
.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, “,”));
query = query.replaceFirst(VALUES_REGEX, questionmarks);
System.out.println(“Query: ” + query);
Reply
neha says:
2 February, 2014, 17:04
package net.viralpatel.java;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class DateUtil {
// List of all date formats that we want to parse.
// Add your own format here.
private static List
dateFormats = new ArrayList() {
private static final long serialVersionUID = 1L;
{
add(new SimpleDateFormat(“M/dd/yyyy”));
add(new SimpleDateFormat(“dd.M.yyyy”));
add(new SimpleDateFormat(“M/dd/yyyy hh:mm:ss a”));
add(new SimpleDateFormat(“dd.M.yyyy hh:mm:ss a”));
add(new SimpleDateFormat(“dd.MMM.yyyy”));
add(new SimpleDateFormat(“dd-MMM-yyyy”));
}
};
/**
* Convert String with various formats into java.util.Date
*
* @param input
* Date as a string
* @return java.util.Date object if input string is parsed
* successfully else returns null
*/
public static Date convertToDate(String input) {
Date date = null;
if(null == input) {
return null;
}
for (SimpleDateFormat format : dateFormats) {
try {
format.setLenient(false);
date = format.parse(input);
} catch (ParseException e) {
//Shhh.. try other formats
}
if (date != null) {
break;
}
}
return date;
}
}
Reply
neha says:
2 February, 2014, 17:05
hey could you plz explain the above code.What is happening bcoz of dis…
Reply
neha says:
2 February, 2014, 17:08
private static Connection getCon() throws SQLException
{
Connection connection = null;
try {
String driver = “com.mysql.jdbc.Driver”;
Class.forName(driver);
String url = “jdbc:mysql://localhost:3306/”;
String dbName = “hema”;
String userName = “hema”;
String password = “hema”;
connection = DriverManager.getConnection(url+dbName,userName,password);
}
i need to connect to mysql…is this code correct?
Reply
code util says:
2 February, 2014, 23:13
package take;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class DateUtil {
// List of all date formats that we want to parse.
// Add your own format here.
private static List
dateFormats = new ArrayList() {
private static final long serialVersionUID = 1L;
{
add(new SimpleDateFormat(“M/dd/yyyy”));
add(new SimpleDateFormat(“dd.M.yyyy”));
add(new SimpleDateFormat(“M/dd/yyyy hh:mm:ss a”));
add(new SimpleDateFormat(“dd.M.yyyy hh:mm:ss a”));
add(new SimpleDateFormat(“dd.MMM.yyyy”));
add(new SimpleDateFormat(“dd-MMM-yyyy”));
}
};
/**
* Convert String with various formats into java.util.Date
*
* @param input
* Date as a string
* @return java.util.Date object if input string is parsed
* successfully else returns null
*/
public static Date convertToDate(String input) {
Date date = null;
if(null == input) {
return null;
}
for (SimpleDateFormat format : dateFormats) {
try {
format.setLenient(false);
date = format.parse(input);
} catch (ParseException e) {
//Shhh.. try other formats
}
if (date != null) {
break;
}
}
return date;
}
}
Reply
Amit Patil says:
4 February, 2014, 11:05
Hello ,
I am Amit. I need code for loading data from csv to mysql using servlet.That is by uploading csv file
Reply
Rohit says:
14 February, 2014, 15:33
There is a problem in loading the csv file data into the table with primary key using the code above. The csv file data is loaded only once when the table is empty.Trying it again causes a batch failure error. Please Help and thanks in advance
Reply
Ashok says:
21 February, 2014, 11:32
Hi Viral,
First of all i have to thank for you valuable posts.
Here is the problem i have faced while inserting csv file value line by line , insert into postgres table using java. Where i have the lakhs of records in my csv file. The postgres table headers was not the exact match of csv headers so i have parse each value and populated the table. This process will take very long time to insert single file into the table. Any suggestions would be greatly appreciated.
thank you.
Reply
subhash says:
4 March, 2014, 13:39
Hi Viral,
loader.loadCSV(“C:\\INAVY_201312_UPDATE.csv”, “DETAIL_MASTER”, false);
for this line m getting error
java.sql.BatchUpdateException: Duplicate entry ‘1000’ for key ‘PRIMARY’
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)
at com.CSVLoader.loadCSV(CSVLoader.java:114)
at com.Main.main(Main.java:17)
java.lang.Exception: Error occured while loading data from file to database.Duplicate entry ‘1000’ for key ‘PRIMARY’
at com.CSVLoader.loadCSV(CSVLoader.java:122)
at com.Main.main(Main.java:17)
I inserted one CVS and I want to add another csv. is it possible? how can i take last primary key from table and add new csv?please help
Reply
Pallavi says:
17 March, 2014, 12:31
Hi,
I tried your piece of code which has been shared. It is working good. But am facing some issue if i try to load more than 15 records it is not getting uploaded into database and throws error as “HTTP Status 500 – javax.servlet.ServletException: java.lang.Exception: Error occured while loading data from file to database.ORA-00001: unique constraint “. How to resolve?
Reply
Jitendra says:
17 April, 2014, 14:53
Hello Viral,
I am try to csv file data import in derby data base from java code. can you help me how to import CSV data in Derby database.
Reply
- goritala ramana says:
  13 July, 2014, 1:05
  it is also same like that
  Reply
ramana says:
13 July, 2014, 1:04
where it wii get the value ?–>${table}(${keys}) VALUES(${values
Reply
Prem Pratick Kumar says:
9 October, 2014, 19:43
Thanks for the code. How to manage Chinese chacters??? UTF-8 is not working
Reply
- laugen says:
  14 January, 2015, 22:04
  Just replace
```
csvReader = new CSVReader(new FileReader(csvFile), seprator); 
```
  by
```
csvReader = new CSVReader(new InputStreamReader(new FileInputStream(csvFile), &quot;UTF-8&quot;), seprator); 
```
  Reply
Shaik Rizwan says:
26 December, 2014, 19:16
Hi Viral-Patel,
Your example is too good. Really helped me a lot.!!!!!
Thanks for your help and such a smooth example.
Reply
Ghulam Hyder Butt says:
17 February, 2015, 22:08
Hello Viral,
Thanks for sharing with us. Could you please also share HTML code or complete source code link.
Thanks.
G. Hyder
Reply
niraj kumar says:
31 March, 2015, 19:38
how can add jar ‘au.com.bytecode.opencsv.CSVReader ‘ file in play fraemwork,
please can suggest me ,i am great full to u
Reply
Mallesh says:
23 April, 2015, 17:22
Query: INSERT INTO Shopping_Cart.CUSTOMER(PK
Reply
Mallesh says:
23 April, 2015, 17:24
hi viral i tried your code with my sql but it gives an sql exception that is
Error occured while loading data from file to database.Parameter index out of range (3 > number of parameters, which is 1).
I dont get solution for this exception so please help me..
Reply
Mallesh says:
23 April, 2015, 17:25
hi viral i tried your code with my sql but it gives an sql exception that is
Error occured while loading data from file to database.Parameter index out of range (2 > number of parameters, which is 1).
sorry it s 2>..
I dont get solution for this exception so please help me..
Reply
LEE says:
12 June, 2015, 6:28
Hey ,please share me convertToDate method code I have tried I’m getting parsing issues ?[ public static Date getDate(String inputDate){
SimpleDateFormat sdfmt =new SimpleDateFormat(“dd/MM/yyyy HH:mm”);
Date utilDate=null;
try{
if(inputDate==null){
return null;
}
sdfmt.setLenient(false);
utilDate =sdfmt.parse(inputDate);
return utilDate;
}catch(Exception ex){
ex.printStackTrace();
return new java.sql.Date(0);
}
}]
Reply
- LEE says:
  12 June, 2015, 6:31
  My input values are [Object value =row[i].toString();Date date =DateUtil.getDate(value.toString());]
  I’m getting pasring convertion because my Object values are like [1,’HAI’,’5/2/2015′] so I need to stored each values based on instance of data type ?
  Reply
kanthraj says:
27 October, 2015, 11:37
Hi,
i have a suitation like want to upload 500000 Records in csv into DB2 through java as bulk upload.
also download in other scenario.
request some assistance in this.
Reply
dany says:
17 June, 2016, 12:17
hi,
i want to read file in xlsx format and write another file in create table using schema in the xlsx file
..
Reply
Akshay says:
20 June, 2016, 13:20
HI,
how to create Screen for User to upload the csv file
Reply
Siva says:
24 September, 2016, 10:03
Hi,
I am getting the following error pls help,
java.sql.SQLException: Invalid state, the Connection object is closed.
Reply
Laksh says:
24 November, 2016, 12:06
Hi Patel,
The code is working fine with small table.
when I am trying with a table of 36 rows and 98 rows, but there i got this error
java.lang.Exception: Error occured while loading data from file to database.Data truncation: Incorrect date value: ” for column ‘Due_date’ at row 1.
Note: For small table i tried with same date value and it work, but not with other one.
Please suggest if i need to do any changes in code.
Thanks
Reply
shu says:
9 January, 2017, 0:29
please send link for csvreader jar file and stringutil and dateutils
Reply
Naveen says:
28 February, 2017, 13:28
Hi please send the link of CSVLoader class file or CSVloader jar file…
Reply
rAJ says:
17 October, 2017, 3:42
can we update a value thats retrieved from session parameter and update the table by passing a parameter stored in a variable in CSVLodaer file and update the database.
Reply
sandeep says:
22 February, 2018, 10:58
Exception occuredTypeError: Cannot read property ‘setPKCS11UI’ of undefined
my question help me
for cersai making
Reply
Oki says:
3 September, 2018, 15:13
Hi,
Thanks for this. It is very helpful.
Reply
akza says:
26 November, 2018, 14:25
Hi Viral, thank you for sharing this. It works on me. But, what if, in Main.java, its not a string of the file fullpath? I want to make it as a File type. How to make it? Since you treat the file as a String input.
Reply
Indhumathi says:
31 January, 2019, 10:26
Your code is useful.
I have a scenario in which the data could be stored in any file format but it has to be loaded into the database
Is tehre any way of doing that?
Reply
shalini naula says:
3 May, 2019, 12:41
Hi, i want to load CSV file and in case of duplicate primary key update that row .how can i do this using your code .thank you
Reply
venu says:
16 August, 2019, 17:12
i m geting memory error i want to load bulk records
Reply

Java: How to Load CSV file into Database

Import CSV into Database example

Download Source Code

You may also like...

73 Comments

Leave a Reply Cancel reply

Java: How to Load CSV file into Database

Import CSV into Database example

Download Source Code

You may also like...

Eclipse: This project needs to migrate WTP metadata

How to iterate HashMap using JSTL forEach loop

Inner classes in Java, the mystery within.

How to update JSTL Locale dynamically

73 Comments

Leave a Reply Cancel reply