Trouble inserting dates into Access DB - non existing hour due to timechange

Question

I'm writing a little import routine to insert measurement data from excel to access. In my excel input file I got values for every hour ignoring time change to daylight saving time, so every date will be exactly 1 hour later than the preceding one. No duplicates, no missing dates.

Now I read the data using poi and then try to batch insert the data to the Access database using a prepared statement. But I always get a General Error when executing the batch. Looking at the database I see all data inserted until "2011.03.27 01:00" but next and last date inserted is "2011.03.27 03:00" with the values corresponding to "2011.03.27 02:00" in my input data.

Somehow when inserting the data the date "2011.03.27 02:00" gets converted to "2011.03.27 03:00" implicitely (no Warnings or Errors) and since my date column is marked as primary key the insert fails when trying to insert the real 3'o'clock data.

Is there any way to prevent this conversion from happening? I know that the date "2011.03.27 02:00" does not exist (or at least equals "2011.03.27 03:00") but just for the consistency of my data I need this date to be inserted properly.

Here's my code:

        myDBConn = DriverManager.getConnection("jdbc:odbc:mydb");
        state = myDBConn.prepareStatement("Insert into myTable values(?,?,?)");
        for(int i=0;i<numberOfObjects;i++){
            state.clearBatch();
            for(int j=0;j<numberOfHours;j++){
                state.setString(1,names[i]);
                state.setTimestamp(2,dates[j]);
                state.setDouble(3, data[i][j]);
                state.addBatch();
            }
            state.executeBatch();
        }
        state.close();
        myConn.close();

I already made sure that dates[] contains the right dates and has no duplicates. The conversion really seems to happen when executing the batch...

You probably shouldn't be storing in a time zone specific format, it is more appropriate to use something like UTC as it is universal and will not be affected by things like daylight savings. See: http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices — talegna, Dec 06 '13 at 16:40
I would like to try that, but what kind of format should I store the dates in? I can't use joda.DateTime in my bulk insert statement since it only accepts Date or Timestamp. Is there any way to create a non time zone specific Date or Timestamp inside java? — DCircle, Dec 09 '13 at 13:44

Gord Thompson · Answer 1 · 2013-12-09T17:19:57.630

Probably the most straightforward way to avoid the timezone/DST issue would be to import the Excel data directly into the Access database, bypassing the Java Date complexities altogether. Given an Excel file named "measurements.xls" containing the following data in "Sheet1"

and a new, empty Access database named "myDb.mdb", the following Java code will import the Excel data directly into Access using the good old Microsoft Access ODBC (Jet) driver

import java.sql.*;

public class ImportFromExcelMain {

    public static void main(String[] args) {
        try {
            Connection con = DriverManager.getConnection(
                    "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" + 
                    "DBQ=C:\\__tmp\\test\\measurements\\myDb.mdb;");
            Statement st = con.createStatement();
            st.execute(
                    "SELECT * INTO myTable " +
                    "FROM [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\\__tmp\\test\\measurements\\measurements.xls].[Sheet1$];");
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

Resulting, in Access:

edit re: new information

It probably would still be easier to directly import the raw Excel data (to avoid Date mangling) and then "unpivot" it. The following amended code has been tested with an Excel sheet that looks like this:

and an existing Access table with the following structure

import java.sql.*;
import java.util.*;

public class ImportFromExcelMain {

    public static void main(String[] args) {
        try {
            Connection con = DriverManager.getConnection(
                    "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" + 
                    "DBQ=C:\\__tmp\\test\\measurements\\myDb.mdb;");
            Statement st = con.createStatement();

            try {
                st.execute("DROP TABLE [excelImport]");
            } catch (Exception e) {
                if(!e.getMessage().contains("Table 'excelImport' does not exist."))
                    throw(e);
            }

            // import raw data from Excel directly into Access
            st.execute(
                    "SELECT * INTO [excelImport] " +
                    "FROM [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\\__tmp\\test\\measurements\\measurements.xls].[Sheet1$];");

            // grab an empty ResultSet so we can look at the column names
            st.execute("SELECT * FROM excelImport WHERE False");
            ResultSet rs = st.getResultSet();
            ResultSetMetaData rsmd = rs.getMetaData();
            String timeStampColumnName = rsmd.getColumnName(1);

            // collect column names 2 through n in a list
            ArrayList<String> columnNames = new ArrayList<String>();
            for (int i = 2; i <= rsmd.getColumnCount(); i++) {
                columnNames.add(rsmd.getColumnName(i));
            }

            // "unpivot" the raw data into [myTable]
            st.execute("DELETE FROM myTable");
            for (String columnName : columnNames) {
                st.execute(
                        "INSERT INTO [myTable] (" +
                                "[observed], " +
                                "[objectName], " +
                                "[measurement] " +
                            ") " +
                        "SELECT " +
                            "[" + timeStampColumnName + "], " +
                            "'" + columnName + "', " +
                            "[" + columnName + "] " +
                        "FROM [excelImport]");
            }

            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

The resulting data in [myTable] looks like this:

observed             objectName  measurement
-------------------  ----------  -----------
2013-03-10 01:00:00  Object1             1.1
2013-03-10 01:00:00  Object2             1.2
2013-03-10 01:00:00  Object3             1.3
2013-03-10 02:00:00  Object1             2.1
2013-03-10 02:00:00  Object2             2.2
2013-03-10 02:00:00  Object3             2.3
2013-03-10 03:00:00  Object1             3.1
2013-03-10 03:00:00  Object2             3.2
2013-03-10 03:00:00  Object3             3.3

Thanks for your answer. Unfortunately this does not fix my problem since I'm not just copying the data from Excel to Access but also changing the format. My input is a n time m table while n is number of Timestamps and m the number of objects that have measurement data for each Timestamp. But in my database it should result in an (n*m) times 3 table where each entry looks like "Date, Measured_by, Value" — DCircle, Dec 09 '13 at 13:50
@DCircle So does your Excel data look something like [this](http://i.stack.imgur.com/mgo8B.png)? — Gord Thompson, Dec 09 '13 at 14:21
Yes exactly! And in my database it should look like your first screenshot just with primary key set on "someText" and "someDateTime" while someText would be the ID of the measurement station. — DCircle, Dec 09 '13 at 14:40
I tried your code with my database and excel worksheet but I get this Error: `java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Allgemeiner Fehler Registrierungsschlüssel 'Temporary (volatile) Jet DSN for process 0x1d34 Thread 0x2754 DBC 0x2e64fbc Jet' kann nicht geöffnet werden.` It says the registry key as mentioned above can't be opened. Sorry but I'm a little inexperienced as it comes to odbc drivers. Do I need to somehow "register" the Jet driver first? — DCircle, Dec 10 '13 at 11:33
@DCircle I see that error from time to time, usually after I have made some change to a table structure or edited some other database object. The first thing I do is close Access completely and try running my Java code again. — Gord Thompson, Dec 10 '13 at 11:41

Trouble inserting dates into Access DB - non existing hour due to timechange

1 Answers1