How to Import the data from an excel sheet to the

Question

I got a requirement to develop a Java app to load the data from an excel sheet to a MySQL database table everyday.

My actual requirement is when ever the user opens the application, it should automatically load the data from Excel -> MySQL database table.

I do not have idea to import the data from excel to MySQL table.

Can anyone help me in this?

Thanks in advance.

Answer 1

Create a dummyTable for daily load

Clear it before you begin

Save your excel in same column structure as dummyTable in csv format, comma delimited

Run:

LOAD DATA INFILE '/path/theFile1.csv' 
INTO TABLE dummyTable
    FIELDS TERMINATED BY ',' 
           OPTIONALLY ENCLOSED BY '"'
    LINES  TERMINATED BY '\n'

Proceed to use it (dummyTable). Clear it.

Answer 2

Yes, you can generate the mysql queries from excel using PHP.

error_reporting(E_ALL ^ E_NOTICE);
require_once 'query_generator.php';
$data = new Spreadsheet_Excel_Reader("example.xls");

Output:

INSERT INTO table_name VALUES ( '101', 'Narendra Modi', 'Cabinet Ministers', 'Personnel, Public Grievances and Pensions, Department of Atomic Energy, Department of Space, All important policy issues and all other portfolios not allocated to any Minister', 'NULL', 'NULL', 'NULL');
INSERT INTO table_name VALUES ( '102', 'Rajnath Singh', 'Cabinet Ministers', 'Home Affairs', 'NULL', 'NULL', 'NULL');
INSERT INTO table_name VALUES ( '103', 'Sushma Swaraj', 'Cabinet Ministers', 'External Affairs, Overseas Indian Affairs', 'NULL', 'NULL', 'NULL');
INSERT INTO table_name VALUES ( '104', 'Arun Jaitley', 'Cabinet Ministers', 'Finance, Corporate Affairs and Defence', 'NULL', 'NULL', 'NULL');
......

I have written PHP code for generate all queries from excel with sngle click for our production system

Answer 3

You can import the data from excel using MySQL LOAD DATA INFILE command..

For automation you have to write a function to run the MySQL command on load..