How to import excel data to mysql?

Question

I have an excel sheet that contains many fields. I have a database that contains many tables which are linked to 1 another. The fields that excel sheet contains get inserted to multiple tables which are linked to 1 another.

Eg sample.xls or .xlsx

id | Name  | Number    | Designation
1  | manan | 987654321 | software eng.

Database : personal table

id | Name  | Number    | Desig_Id
1  | manan | 987654321 | 1

Database : Designation table

Desig_id | Designation Name
1        | software eng.

This is just a sample I have many tables linked at the same time. How shall i import like this type of data?

Since my answer solved your problem, you should mark it as accepted so the thread can be considered closed — Alkis Kalogeris, Sep 08 '15 at 07:02

score 1 · Answer 1 · answered Jul 23 '15 at 14:09

1

The coding itself is a bit complicated but if you want a simple answer; you can interact with a database from Java using JDBC and you can interact with an Excel file from Java using Apache POI (their how-to page is very useful).

answered Jul 23 '15 at 14:09

cbender

2,231
1
14
18

How to grab particular column using Apache POI? – Manan Patel Jul 24 '15 at 05:00
@MananPatel you can loop through the rows and get the cell in that column for each row: `for(int i=0; i – cbender Jul 24 '15 at 13:45
@MananPatel if you use that for loop make sure you use `i <= sheet.getLastRowNum()`. I only wrote `<` – cbender Jul 24 '15 at 15:24

score 0 · Accepted Answer · edited May 23 '17 at 12:14

You can break all your records into multiple tables, as they are in you database. You will create one excel file per table and import it using mysql-excel mechanism. You can also export the excel files as .csv and import it directly either by using MySQL Workbench or by command prompt

Another way, if you don't want to normalize the excel file, is to parse the file and import it programmatically. You can use apache-poi to read the excel file, or you can export the excel file as csv file and parse it as a text file.

import java.io.FileInputStream;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class App {

    public static void main(String[] args) {
        Workbook wb = null;
        InputStream inp = null;
        try {
            // InputStream inp = new FileInputStream("workbook.xls");
            inp = new FileInputStream("workbook.xlsx");
            wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    System.out.println(getCellValue(cell));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try { if(wb != null) wb.close(); } catch (Exception e) {}
            try { if(inp != null) inp.close(); } catch (Exception e) {}
        }
    }

    private static Object getCellValue(Cell cell) {
        switch(cell.getCellType()) {
        case Cell.CELL_TYPE_STRING :
            return cell.getStringCellValue();
        case Cell.CELL_TYPE_NUMERIC : 
            return cell.getNumericCellValue();
        case Cell.CELL_TYPE_BOOLEAN : 
            return cell.getBooleanCellValue();
        case Cell.CELL_TYPE_ERROR : 
            return cell.getErrorCellValue();
        case Cell.CELL_TYPE_FORMULA : 
            return cell.getCellFormula();
        case Cell.CELL_TYPE_BLANK :
            return null;
        default : 
            return null;
        }
    }
}

The excel sample (xlsx)

id  Name    Number  Designation
1   manan_1 9876543211  software eng._1
2   manan_2 9876543212  software eng._2
3   manan_3 9876543213  software eng._3

Dependencies (Maven)

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.12</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.12</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-excelant</artifactId>
    <version>3.12</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-examples</artifactId>
    <version>3.12</version>
</dependency>

Apache-poi should be useful because there are are tons of records and I can't you know individually create different excel sheet for that. How to grab particular column using Apache POI? — Manan Patel, Jul 24 '15 at 05:02

How to import excel data to mysql?

2 Answers2