Databases
StackOverflow2013
Postsdbo
PO"Solved" Query Excel date fields using ODBC return null
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO"Solved" Query Excel date fields using ODBC return null
    text
    copied!<p>I use the following configuration to run a java program to extract data from a excel file. Some of these columns have data with time-stamp context.</p> <ul> <li>Platform: Win 7 (x64)</li> <li>ODBC: Excel (x32) [via Office 2010]; rows to scan = 8</li> <li>Excel schema: "some fields", "date field 1", "date field 2", "date field 3", "other fields"</li> <li>Date format shown in excel: dd/mm/yyyy HH:MM</li> <li>Java 1.6</li> <li>JDBC/ODBC driver: sun.jdbc.odbc.JdbcOdbcDriver</li> </ul> <p>I tried the following (simplified) queries to extract data: </p> <p><code>SELECT [field 1], [date field 1], [date field 2] from [Sheet1$]</code></p> <p><code>SELECT [field 1], [date field 1], [date field 2] from [Sheet1$] where [field 1] = "sample values"</code> </p> <p>I'm sure the "date field 2" contains valid date values (but emptied for the first 20 rows) and the java program always return null for that column. </p> <p>So, my question is will the empty value of [date field 2] of initial rows, says 8 rows, affect behavior of the JDBC/ODBC? If yes, how to avoid it (without sorting the data in the excel file)?</p> <p>-- Edit -- Actually, I'm asking if the empty values of that field in the first few rows (say 8) will make the driver fail to extract the value of that column (even if the subsequent rows contain valid value). I'm also confused if it is implementation-specific that the driver will simply refuse to extract that field, returning null for all conditions, and even not to attempt to treat the column as string-type or return empty string (instead of NULL)</p> <p>-- Edit -- Referring to the information in this KB: <a href="http://support.microsoft.com/kb/141284" rel="nofollow">http://support.microsoft.com/kb/141284</a></p> <p>The empty value of the first few rows in [date field 2] will make the ODBC driver return NULL under all conditions. So the root cause is found and what next is to find alternative JDBC driver for Excel files.</p> <p>Thanks.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload