Importing a .csv or .xlsx file into mysql DB using Java?

Question

YES, it sounds like a duplicate.

I'm practicing abit of Java on Intellij and tried writing a program to import a .xls excel file into a mysql database. Duplicate question, yes, but trawling the internet didnt yield much.

My code below currently does the job of importing any xls file perfectly. Unfortunately, it doesnt do anything for a .csv file nor an xlsx file.

When i try with a .csv file, the following error is thrown:

Invalid header signature; read 0x6972702C74786574, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document

When a xlsx file is used, the following is instead thrown as an error:

Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:152)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:140)
at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:302)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:85)
at FileExport.main(FileExport.java:21)

My code:

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;




public class FileExport {

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

    try {

        Class forName = Class.forName("com.mysql.jdbc.Driver");
        Connection con = null;
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "root");
        con.setAutoCommit(false);
        PreparedStatement pstm = null;
        FileInputStream input = new FileInputStream("/Users/User/Desktop/Email/Test.xls");
        POIFSFileSystem fs = new POIFSFileSystem(input);
        Workbook workbook;
        workbook = WorkbookFactory.create(fs);
        Sheet sheet = workbook.getSheetAt(0);
        Row row;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            row = (Row) sheet.getRow(i);
            String text = row.getCell(0).getStringCellValue();
            int price = (int) row.getCell(1).getNumericCellValue();


            String sql = "INSERT INTO testtable (text, price) VALUES('" + text + "','" + price + "')";
            pstm = (PreparedStatement) con.prepareStatement(sql);
            pstm.setString(1, text);
            pstm.setInt(2, price);
            pstm.execute();
            System.out.println("Import rows " + i);
        }
        con.commit();
        pstm.close();
        con.close();
        input.close();
        System.out.println("Success import excel to mysql table");
    } catch (IOException e) {
    }
}

}

Any suggestions on how to tweak this code to import .csv or xlsx files are greatly appreciated.

Answer 1

You should use PreparedStatement as it was intended for:

String sql = "INSERT INTO testtable (text, price) VALUES(?, ?)";       
pstm = (PreparedStatement) con.prepareStatement(sql);
pstm.setString(1, text);
pstm.setInteger(2, text)
pstm.execute();

I guess it doesn't work because there is some punctuation in your text. Also it is prone to SQL-injection.

Also you should close your closeable objects with try-with-resources or finally (if you stuck to java version prior version 7), as it done here:

https://stackoverflow.com/a/26516076/3323777

EDIT: ah yes, the empty catch block as Alex said. Don't do that, either.

EDIT2:

Apache POI was never designed to call on CSV files.

https://stackoverflow.com/a/1087984/3323777

There is another project for CSV at Apache:

http://commons.apache.org/proper/commons-csv/

Answer 2

Do you see the "Import rows" messages which prove that you actually have some rows to import?

Also the reason for not seeing any errors might be a "catch" block which does nothing. Add any output inside and observe new results, i.e.

 System.out.println(e.getMessage());

Answer 3

Tweaked for read from .csv

public class FileExport {

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

try {

    Class forName = Class.forName("com.mysql.jdbc.Driver");
    Connection con = null;
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "root");
    con.setAutoCommit(false);
    PreparedStatement pstm = null;
    FileInputStream input = new FileInputStream("/Users/User/Desktop/Email/Test.csv");
     BufferedReader reader = new BufferedReader(new InputStreamReader(input));
     String row = reader.readLine();
     String text = row.split(";")[0];
     String price = row.split(";")[1];
     reader.close();

        String sql = "INSERT INTO testtable (text, price) VALUES('" + text + "','" + price + "')";
        pstm = (PreparedStatement) con.prepareStatement(sql);
        pstm.execute();
        System.out.println("Import rows " + i);
    }
    con.commit();
    pstm.close();
    con.close();
    input.close();
    System.out.println("Success import excel to mysql table");
} catch (IOException e) {
}

}

Answer 4

Try using 'poi-ooxml' for creating a Workbook object, its gradle dependency signature is provided below:

compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.14'

Below code may help you

InputStream inputStream = new FileInputStream("/Users/User/Desktop/Email/Test.xls");
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(0);