CODEWITHSUNDEEP
mysqlexceljsp
KVK
KVK

Reputation: 1289

how to upload excel data's into mysql table

I am developing web application with database in that application user can upload .csv file to database it works some time it has problem with data upload. so i need to know how can i upload excel file into Mysql table below code 

 conv = new Connectivity();
                               con = conv.setConnection();
                               st = con.createStatement();
                               query = "LOAD DATA LOCAL INFILE \"" + filename2 + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ',' IGNORE 1 LINES";

                               st.executeUpdate(query);
                               PrintWriter obj1 = response.getWriter();
                               obj1.println("Row (1) inserted");

                               HttpSession sval = request.getSession();
                               sval.setAttribute("UpdatedCorret", "yes");

Upvotes: 1

Views: 678

Answers (2)

panofish
panofish

Reputation: 7889

Try Excel2MySQL. It has a simple user interface for manual imports and it also includes a command line interface for automating uploads and can read any excel file directly. No need to export as a csv file. The Excel sheetname determines the destination MySQL table name. The table can be automatically created or simply appended to. All fields can also be automatically optimized to the proper MySQL type. The program is free to try and I am it's author.

-h, --help, display help message and exit
-f file, Excel file to convert (include full path) (REQUIRED)
-s sheet, Excel sheet to convert (omit to convert all)
-z host, mysql server hostname or IP address (omit will default to localhost)
-d database, mysql database name (REQUIRED)
-u user, mysql user name (REQUIRED)
-p password, mysql password
-x port, mysql port number (omit will default to 3306)
-r, replace existing table (omit to append to existing table)
-n, first row is not a header (omit to indicate first row contains header)
-a, allow spaces in table & field names (omit to change spaces to underscores)
-m, use myisam database storage engine (omit to use innodb)
-k, keep blank rows & columns (omit to remove)
-c, Unmerge merged cells
-v, define all fields as varchar type on tables (omit to optimize field types)

Here is an example command line to upload an excel file with sheet name of "address":

excel2mysqlCLI.exe -f "c:\my excel.xls" -d mydb -u myid -p mypass -s address

Upvotes: 0

Gopal00005
Gopal00005

Reputation: 2151

This is demo code for using Poi API to parse excel file...

File source = jFileChooser.getSelectedFile();

InputStream input = new BufferedInputStream(new FileInputStream(
                source.getCanonicalPath()));

POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator<?> rows = sheet.rowIterator();
while (rows.hasNext()) {
    HSSFRow row = (HSSFRow) rows.next();
        Iterator<?> cells = row.cellIterator();
        while (cells.hasNext()) {
            HSSFCell cell = (HSSFCell) cells.next();
            try {
                System.out.print(new BigDecimal(cell
                    .getNumericCellValue()).toPlainString());
            } catch (IllegalStateException ex) {
                System.out.print(cell.getStringCellValue());
            } catch (Exception ex) {
                ex.printStackTrace();
        }
    }
}

Hope it works for you...

Upvotes: 1

Related Questions