how to insert excel data in a database with java

i want to insert data from an excel file into a local database in a UNIX server with java without any manipulation of data.

1- someone told me that i've to convert the excel file extension into .csv to conform with unix. i created a CSV file for each sheet (i've 12) with a macro. the problem is it changed the date format from DD-MM-YYYY to MM-DD-YYYY. how to avoid this?

2- i used LOAD DATA command to insert data from the CSV files to my database. there's a date colonne that is optionnaly specified i开发者_如何学Cn the excel file. so in CSV it become ,, so the load data doesn't work (an argument is needed). how can i fix this? thanks for your help

It should be quite easy to read out the values from Excel with Apache POI. Then you save yourself the extra step of converting to another format and possible problems when your data contains comma and you convert to CSV.

Save the EXCEL file as CSV (comma separated values) format. It will make it easy to read and parse with fairly simple use of StringTokenizer.

Use MySQL (or SQLite depending on your needs) and JDBC to load data into the database.

Here is a CSVEnumeration class I developed:

package com.aepryus.util;

import java.util.*;

public class CSVEnumeration implements Enumeration {
    private List<String> tokens = new Vector<String>();
    private int index=0;

    public CSVEnumeration (String line) {
        for (int i=0;i<line.length();i++) {
            StringBuffer sb = new StringBuffer();
            if (line.charAt(i) != '"') {
                while (i <  line.length() && line.charAt(i) != ',') {
                    sb.append(line.charAt(i));
                    i++;
                }
                tokens.add(sb.toString());
            } else {
                i++;
                while(line.charAt(i) != '"') {
                    sb.append(line.charAt(i));
                    i++;
                }
                i++;
                tokens.add(sb.toString());
            }
        }
    }

// Enumeration =================================================================
    public boolean hasMoreElements () {
        return index < tokens.size();
    }
    public Object nextElement () {
        return tokens.get(index++);
    }
}

If you break the lines of the CSV file up using split and then feed them one by one into the CSVEnumeration class, you can then step through the fields. Or here is some code I have lying around that uses StringTokenizer to parse the lines. csv is a string that contains the entire contents of the file.

StringTokenizer lines = new StringTokenizer(csv,"\n\r");
lines.nextToken();
while (lines.hasMoreElements()) {
    String line = lines.nextToken();
    Enumeration e = new CSVEnumeration(line);
    for (int i=0;e.hasMoreElements();i++) {
        String token = (String)e.nextElement();
        switch (i) {
            case 0:/* do stuff */;break;
        }
    }
}

I suggest MySQL for its performance and obviously open source.

Here comes two situations:

1. If you want just to store the excel cell values into the database. You can convert the excel to CSV format, so that you can simply LOAD DATA command in MySQL command.

2. If you have to do some manipulation before the values to get into the tables, I suggest Apache POI. I've used, that works so fine, whatever you're format of Excel you just have to use the correct implementation.

We are using SQLite in our java application. It's serveless, really simple to use and very efficient.