0

I need to upload data in an excel sheet to the database using java.

The database will be oracle.

The excel sheet will be uploaded once in a month by a user using a web application (Servlets and JSP).

The excel sheet will be having thousands of records/rows e.g. around 15000 or more.

What is the fastest way to upload this huge data in database? We are using simple JDBC (Spring's JDBC Template). How do we handle transaction sand errors as there can be errors while uploading data in which case the partly uploaded data will be useless? We need to able to notify the user of the error so that he can correct the excel sheet and try again? Please help/

vivek singh
  • 21
  • 6

1 Answers1

0

To import excel sheet data into mysql, first convert it to csv and then use following code to import it in database

Login to mysql

mysql -u root -p
<type password>

Import data to table using this query

LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

here the data.csv file must be located at valid path specified by mysql default configuration, typically you have to save this data.csv at /var/tmp/ path. If you wish to specify custom path where file is located e.g. /home/ubuntu/data.csv then make sure that secure-file-priv is disabled.

Chintan7027
  • 7,115
  • 8
  • 36
  • 50
  • Thankyou for your answer sir. But i do not directly want to upload the excel to the database.I have a web application and through that web application i want to upload the data of excel into the database. – vivek singh May 09 '18 at 11:40
  • You have to run LOAD DATA .... query in any server side scripting language, e.g. PHP – Chintan7027 May 09 '18 at 11:45