java - import from excel to mysql

Question

i have excel file (.xlsx) with column

Column1     Column2      Column3      Column1
   A           50          30           40
   B           45          40           20
   .......................................

I need to import this excel in MySQL database. I use following code:

 public static void main(String[] args) throws IOException {

    FileInputStream fis = new FileInputStream(new File("Data.xlsx"));
    XSSFWorkbook workbook = new XSSFWorkbook (fis);
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator ite = sheet.rowIterator();
    while(ite.hasNext()){
        Row row = (Row) ite.next();
        Iterator<Cell> cite = row.cellIterator();
        while(cite.hasNext()){
            Cell c = cite.next();
            System.out.print(c.toString() +";");
        }
        System.out.println();
    }
    fis.close();
}

I need to get in list all row. This code show in console all data from row, but I don't now to add that data in list and list import to MySQL.

Any idea?

Rishabh madan · Accepted Answer · 2018-12-04 11:42:34Z

I can think of few approaches in order to write this data into the MySQL database but, I will give you the most basic approach to achieve what you desire. You are using Apache POI library in order to log all the data to console.

while (rows.hasNext()) {
    String[] rowsVal = new String[Number_of_Columns];
    if (row.getCell(0).getStringCellValue().equalsIgnoreCase("Column_Name")) {
                    row = rows.next();
     }
    while (cells.hasNext()) {
        keyVal[j] = cells.next().getStringCellValue();
        j++;
    }
}

Above code will store the content of the first row in the keyVal[] array. Now, you need to include the packages containing the JDBC classes needed for database programming. Following code snippet will be useful to insert records into the table:

String sql = "INSERT INTO Table " + "VALUES ("keyVal[0] + "," + keyVal[1] + "," + keyVal[2] + "," + keyVal[3])";

The above code snippet will help you in inserting the data into the database.

Links for Reference: https://www.tutorialspoint.com/jdbc/jdbc-insert-records.htm

Accidentally wrote rowsVal as keyVal. You can simply replace keyVal[j] in the 7th line with rowsVal[j]. And also in the SQL query snippet.
ok, and variable rows in first row... how to declare, and cells in while loop
Iterator<Row> rows = sheet.iterator(); outside of the primary while loop. Iterator<Cell> cells = row.iterator(); int j = 0; right above the secondary while loop.

Tim Biegeleisen · Accepted Answer · 2018-12-04 11:13:41Z

This is not a direct answer to your question, but may in fact be an even better approach than what you are considering. You may try exporting your Excel worksheet as a CSV file. This means the lines would look like:

Column1,Column2,Column3,Column4
A,50,30,40

With this CSV file in hand, you may then use MySQL's LOAD DATA tool, which allows for rapidly importing CSV or other regular data:

LOAD DATA INFILE 'C:/path/to/Data.xlsx' 
INTO TABLE yourTable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

The motivation for possibly avoiding your current approach is that it is costly to use an Apache library to open an Excel workbook, and then read it. Even after doing this, you still would need to use JDBC to write that data to MySQL.

Collectives™ on Stack Overflow

java - import from excel to mysql

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related