How to insert excel data into database using java

Question

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

1 someone told me that I need to convert the excel file extension to .csv to fit unix. I created a CSV file for each sheet (I have 12) with a macro. the problem is that he changed the date format from DD-MM-YYYY to MM-DD-YYYY. how to avoid this?

2- I used the LOAD DATA command to insert data from CSV files into my database. there is a date-date colony, which is optionally specified in the excel file. therefore, in CSV it becomes, therefore, the download data does not work (argument is needed). How can i fix this? thanks for the help

Answer 1

Save the EXCEL file in CSV format (comma separated values). This will simplify reading and analysis with the fairly simple use of StringTokenizer.

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

Here is the CSVEnumeration class that 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 split the lines of a CSV file with split and then put them one by one in the CSVEnumeration class, you can go through the fields. Or here is some code that I was lying around that uses a StringTokenizer to parse strings. csv is a string containing the entire contents of a 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;
        }
    }
}

Answer 2

Excel Apache POI. , , CSV.

Answer 3

MySQL , , .

:

• excel . Excel CSV, LOAD DATA MySQL.

• , , Apache POI. , , , Excel .

Answer 4

SQLite java. , .