history.html - HXTT Excel JDBC Drivers for Microsoft Excel (MS Excel) 95/97/98/2000/2001/XP/2002/2003/2004/2007/2010/2013/2016 v5.0

Released Version Log

Latest feature:

2023-05-25 supports Named Ranges created by MS Excel, which are visible through DatabaseMetaData.getTables method and can also be quoted directly in SQL like sheet name.

2023-04-01 supports SWITCH function.

2022-11-04 supports to detect integer type from double value for XLS file when that value is bigger than 65535.

2022-04-16 supports FTP with explicit SSL/TLS encryption (FTPES).

2022-04-16 supports FTP with implicit SSL/TLS (FTPS).

2022-02-14 supports CTE syntax (common table expression).

2022-01-26 provides SAMBA support for Azure file share.

2021-12-15 provides emptiesAreVisible connection property to filter empty rows.

2021-12-15 opens endRowHasNames connection property for complicated table header detection.

2021-12-15 provides function SPLIT_PART(string, delimiter,field_number) .

2021-05-24 support some customer date format in Chinese.

2021-04-17 needn't wait total sheet data load for cloneMergedCellValues=true.

2021-03-24 provides dynamical column, ILIKE pattern, expression calculation for PIVOT table and UNPIVOT table.

2021-02-09 provides hyphenInColumName connection property to format special character(_,-, space) in column name.

2021-02-08 supports seamlessly files and directories in Seven Zip file formats(.7z) in jdbc url and sql.

2021-01-16 provides new connection property(dataStartRow,dataEndTag) for ETL.

2021-01-15 firstRowHasNames=n has supported complicated table header more than one row for ETL. Headers and Sub-headers can be used as column name. Parellel tables can be recognise.

2021-01-06 provides new table object(XML_TABLE, JSON_TABLE, VALUES_TABLE) for ETL.

2020-11-11 supports COPY DATABASE sql and CLONE DATABASE sql.

2020-10-21 quicken parse speed for XLSX format.

2020-10-14 won't parse total sheet data for DatabaseMetaData.getColumns for XLSX format.

2020-10-14 supports DATE type for cells in XLSX Strict format.

2020-10-12 supports Strict Open XML format.

2020-09-01 accepts some special non-empty directory entries without associated name for XLS file.

2020-08-19 COLLATE(string1[,collation]) supports 'Excel', a special collation for ORDER BY clause, which can detect numeric value from string value, and sort like MS Excel.

2020-08-12 Excel v6.1 JDBC4.3 Package(1778KB) JDBC4.2 Package(1782KB) JDBC4.1 Package(1769KB) JDBC4.0 Package(1765KB) JDBC3.0 Package(1627KB) JDBC2.0 Package(1577KB) JDBC1.2 Package(1605KB)
2020-07-16 provide database information about corrupted file or encryted file, through "select * from information_schema.DATABASES;".

2020-05-28 provides readOnlyMode connection property to skip formula parse and calculable cell listen.

2020-05-20 supports update/insert formula in SQL for XLS/XLSX file, which should be _Style_;formula=aFormula. BTW, _Style_;formula=SUM(Sheet1!C2:C5);borderType=all;borderLineStyle=DASH_DOT_DOT;borderColour=LIME;rowHeight=deafult;rowCollapsed=true; has been supported for XLS format without document for more than 10 years, but is not advisable feature. If you need those feature in your project, you should contact us.

2020-04-22 will strip trailing zeros for decimal value.

2020-04-22 will show integer value (not scientific notation) for double value in XLS format file.

2020-03-16 provides cloneMergedCellValues=true connection property to clone values for all merged cells for data migration.

2020-03-10 fixed a transaction bug for TRANSACTION_READ_COMMITTED level since 2019-12-3

2020-02-03 provides refreshIntervalfor http/https protocol

2019-11-24 supports SMB2 (2.02 protocol level) for SAMBA URL.

2019-11-20 supports using the IP address on the HTTPS URL.

2019-11-8 rounds numeric precision according to the limitation of Microsoft Excel.

2019-11-07 supports user/password BASIC authorization for url database connection based on HTTP/HTTPS protocol.

2019-10-29 discards only relative index search result for update sql.

2019-10-26 provides aggregate functions(PRODUCT, STDEV, STDEVP, VAR, and VARP).

2019-10-26 For formula calculation, SUBTOTAL function will be converted into standard SQL functions. SUMIF(range,criteria,sum_range) is supported, but not fully test.

2019-10-25 For XLS format, supports embedded EMF( enhanced metafile format ), WMF(Windows Metafile Format), Macintosh PICT, JPEG (Joint Photographic Experts Group) format, DIB (device-independent bitmap), and TIFF. If you want more complicated sql feature, you should contact us.

2019-10-24 supports range table. For instance, select * from EPOM."Sheet1!$A$19:$n$269";
insert into EPOM_Template."Sheet1!$A$17:$N$18" select * from EPOM."Sheet1!$A$19:$n$269"; /* Warning: only used it in TRANSACTION_NONE or TRANSACTION_READ_UNCOMMITTED mode if you wish to insert after a range table */

2019-10-17 provides EXPLAIN SQL anySQL for SQL syntax analysis feature like INFORMATION_PARSER.sql table.

2019-10-17 provides EXPLAIN anySQL to show how the tables involved in the statement will be scanned by index scanned or sequential scan.
2019-10-11 beside embedded user/password in ftp/sftp/http/https url, it supports
         1. Connection getConnection(String url, String user, String password); 
         2. Properties prperties=new Properties();
            prperties.put("user","ftpdemo1");
            prperties.put("password","ftp12345");            
            Connection con = DriverManager.getConnection( url,prperties );
to avoid possible jdbc url log.
2019-10-07 supported embedded PNG(Portable Network Graphics) format for XLS format. If you want more complicated sql feature, you should contact us.

2019-10-01 fixed a seldom invalid font bug for XLS format.

2019-09-27 HXTT Excel supports formula evaluation. It doesn't complement all of MS Excel functions or test fully for formula evaluation. You can use Extend SQL functions, or contact us to support your project.

2019-09-27 HXTT Excel doesn't modify automatically formula except for recalculating automatically relative cells with formula. It will be unexpected result if you delete some relative data rows

2019-09-27 Run once FLUSH DATABASE sql will recalculate all obsoleted values in cells with formula evaluation. Use it only when you get an obsoleted file. HXTT Excel can recalculate automatically relative cells for itself data modification.

2019-09-24 fixed a bug for parsing TXO record of XLS.

2019-09-23 three functions (DATE, TIME, and TIMESTAMP) will utilize connection properties(dateFormat, timeFormat, and timestampFormat) like CAST(expression AS data_type) and CONVERT(value1, SQLtype1).

2019-09-12 supports multiple-table UPDATE statement.

2019-08-09 fixed a stylex format bug since 2019-04-23.

2019-06-25 provides _values_ as a pseudo name for all values in a data row.

2019-04-23 format numeric value according to style index for XLSX.

2019-04-18 detects big integer number as BigInteger, not Double for XLSX.

2018-11-25 supports JDBC4.3 for Java 9, Java 10, and Java 11.

2018-11-25 Excel v6.0 JDBC4.3 Package(1670KB) JDBC4.2 Package(1673KB) JDBC4.1 Package(1662KB) JDBC4.0 Package(1659KB) JDBC3.0 Package(1499KB) JDBC2.0 Package(1450KB) JDBC1.2 Package(1476KB)

2018-11-25 supports JDBC4.3 for Java 9, Java 10, and Java 11.

2018-11-3 provides FLUSH DATABASE sql.

2018-10-22 supports aggregate function ARRAY_AGG([DISTINCT] expression), STRING_AGG([DISTINCT] expression[, delimiter]).

2018-10-07 provides dbo as a pseudo name for the current schema for JasperSoft tool. The old "." name can still be used too.

2018-09-21 supports JSON, JSONB, XML, and ARRAY.

2018-09-21 will add the missed sharedStrings information for XLSX file.

2018-08-30 provides _CURRENT_ as a pseudo name for the current catalog for JasperSoft tool. The old "." name can still be used too.

2018-04-12 needn't to encode special character for http/https url.

2018-04-12 returns raw value if withFormat=false, returns formatted value if withFormat=true. For older package, it will always return formatted value for numeric value

2018-02-22 will fix some wrong dimension information for XLSX file.

2018-02-02 can split multivalue column into rows through special subquery table.

2017-08-02 Excel v5.0 JDBC1.2 Package(1411KB) JDBC2.0 Package(1387KB) JDBC3.0 Package(1436KB) JDBC4.0 Package(1588KB) JDBC4.1 Package(1591KB) JDBC4.2 Package(1602KB)

2017-08-02 will calculate the missing dimension element for XLSX file.

2017-06-30 PreparedStatement.executeBatch suupports RETURN_GENERATED_KEYS.

2016-09-30 provides Pseudo Table INFORMATION_PARSER.sql for SQL syntax analysis feature.

2016-05-16 supports Pack Table sql.

2016-03-23 provides refreshIntervalfor ftp/sftp protocol

2016-03-23 supports DatabaseMetaData.getTables for ftp/sftp protocol

2016-02-04 supports escape character '\' in pattern for DatabaseMetaData.

2015-12-09 fixed a bug for cell reference format on XLS file.

2015-09-12 quicken load speed for XLSX big shared string table.

2015-04-30 supports write sftp protocol data file.

2015-04-27 can write XLSX file on ftp site.

2015-04-08 fixed an INSERT hang up bug for large file.

2014-12-02 supports empty string in SST for XLS file.

2014-11-13 fixed a bug on customer cell format of XLS file.

2014-11-12 will use default format for Date object when use INSERT sql on XLS file.

2014-09-02 supports write ftp protocol data file.

2014-07-12 fixed an UNION ALL bug for firstRowHasNames=true without WHERE clause since 2013-05-02.

2014-05-08 v5.2.001 supports JDBC 4.2.

2014-05-08 Excel v4.2 JDBC1.2 Package(1382KB) JDBC2.0 Package(1359KB) JDBC3.0 Package(1408KB) JDBC4.0 Package(1532KB) JDBC4.1 Package(1535KB) JDBC4.2 Package(1546KB)

2014-05-08 v4.2.001 supports JDBC 4.2.

2014-02-19 fixed a delete bug for XLSX files.

2013-12-13 fixed an update bug for XLSX files.

2013-10-09 supports NTLM Authentication for SAMBA url or SAMBA absolute path.

2013-08-27 fixed an ArrayIndexOutOfBoundsException bug on system function since 2013-08-09.

2013-08-14 optimize spped for XLSX format.

2013-08-09 supports update batch column list syntax. For instance, UPDATE "UIP_Target" SET ("key_string","key_int","data_string","data_int","data_decimal" ) = (SELECT COALESCE(DP3475."key_string", "UIP_Target"."key_string"),COALESCE(DP3475."key_int", "UIP_Target"."key_int"),COALESCE(DP3475."data_string", "UIP_Target"."data_string"),COALESCE(DP3475."data_int", "UIP_Target"."data_int"),COALESCE(DP3475."data_decimal", "UIP_Target"."data_decimal") FROM DP3475 WHERE "UIP_Target"."key_string"=DP3475."KEY208" AND "UIP_Target"."key_int"=DP3475."KEY572") WHERE EXISTS (SELECT 'X' FROM DP3475 WHERE "UIP_Target"."key_string"=DP3475."KEY208" AND "UIP_Target"."key_int"=DP3475."KEY572")

2013-08-02 fixed a cache bug for In subqueryTable expression which subqueryTable contains parameter since 2013-02-25.

2013-06-29 fixed a match partly bug on FULL OUTER JOIN.

2013-06-26 fixed a condition bug for where clause since 2013-06-23.

2013-06-06 supports to insert Timestamp(Date, Time) value into XLSX format file.

2013-06-04 fixed an insert bug for XLSX format file.

2013-05-03 fixed a NullPointerException bug for date convertion function since 2013-04-11.

2013-05-02 fixed a bug for Union ALL with where-clause.

2013-04-19 v4.1.128 fixed a bug, which will ignore where-clause when subquery table is UNION sql sometimes.

2013-04-11 v4.1.124 fixed a bug for XLSX format, which failed sporadically to load all rows for big XML file sometimes.

2013-04-06 LIKE/ILIKE supports complicated expression as string pattern.

2013-03-11 v4.1.115 allows omittance [CONSTRAINT constraint_name] for UNIQUE in constraint clause.

2013-03-07 provides how to install HXTT DB Server as system service on MS Windows 7, Windows 2008, and Vista for remote connection and remote control

2013-01-05 v4.1.099 fixed a bug for XLSX format, which can't detect date type when there's no numFmts element.

2013-01-04 v4.1.098 supports data mining on XLSX file in other compressed file.

2012-12-11 v4.1.094 provides SPLIT(expression,delimiter) function.

2012-11-11 v4.1.091 fixed a NPE exception bug since v4.1.088.

2012-11-09 v4.1.090 supports AES(128/192/256) for encrypt,decrypt,encode/decode function.

2012-11-02 v4.1.088 provides independent thread for uncompress big XML file in XLSX. For smalll document, HXTT Excel engine will unzip first that Sheetn.xml from XLSX file, check secondly XML syntax, load thirdly all sheet information, and allow SQL random access all rows at last. For big sheet, all operations (unzip file, XML parse, Excel sheet load, sql access) are concurrent now, so that it should be the fastest engine with a smaller memory requirement. Default 32000 rows are cached in memory, and you can ajust the cached row number through maxCacheSize connection property.

2012-11-01 v4.1.087 optimizes load random access speed for XLSX sheet with 1,048,576 rows

2012-11-01 v4.1.087 uses smaller memory to load 1,048,576 rows of XLSX sheet to avoid OutOfMemory Exception.

2012-11-01 v4.1.087 provides thread pool for XLSX parse and load.

2012-10-23 v4.1.084 quicken IN on subquery with more than 100,000 data rows.

2012-10-22 v4.1.083 fixed a bug for showing 1904-based time for XLS file.

2012-10-22 v4.1.083 fixed a bug for writing XLS file.

2012-10-20 v4.1.082 supports NUMBER[(n1[,n2])] for CREATE TABLE sql.

2012-10-06 v4.1.079 will force MS Excel to recalculate formulas on opening xlsx format.

2012-10-04 v4.1.078 supports XML format for SELECT ... INTO OUTFILE syntax.

2012-10-01 v4.1.077 optimizes long varchar type of temporary index.

2012-09-27 v4.1.076 supports seamlessly sftp url database in jdbc url and sql.

2012-07-07 v4.1.061 supports CSV format for SELECT ... INTO OUTFILE syntax.

2012-02-25 v4.1.033 supports Statement.setQueryTimeout.

2012-02-10 v4.1.028 supports query on SCHEMATA, TABLES, COLUMNS of INFORMATION_SCHEMA schema.

2011-11-10 v4.1.008 provides five regular expression functions(REGEXP_COUNT, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR, and REGEXP_SUBSTR)

2011-10-30 v4.1.005 optimizes speed for DISTINCTROW on multi tables.

2011-10-11 Excel v4.1 JDBC1.2 Package(1199KB) JDBC2.0 Package(1178KB) JDBC3.0 Package(1213KB) JDBC4.0 Package(1323KB) JDBC4.1 Package(1327KB)

v4.1.001 supports JDBC 4.1.

v4.0.004 replaces LONGVARCHAR type with VARCHAR type, for date type detect.

v4.0.004 roundes double value according to cell format.

v4.0.003 provides caseInsensitive connection property.

2011-06-02 Excel v4.0 JDBC1.2 Package(1091KB) JDBC2.0 Package(1118KB) JDBC3.0 Package(1150KB) JDBC4.0 Package(1255KB)

v4.0.001 supports CREATE/DROP DATABASE, CREATE/DROP/ALTER TABLE, INSERT, UPDATE, and DELETE, for XLSX format (MS Excel 2006/2010).

v3.0.020 supports PIVOT expression besides column.

v3.0.019 supports [START WITH initial-condition] CONNECT BY [NOCYCLE] recurse-condition. level is a pseudo column that can be used in hierarchical queries.

v3.0.016 provides read-only support for XLSX format (MS Excel 2006/2010).

v3.0.014 adjusts Daylight Saving Time for timestamp show.

v3.0.002 supports CREATE VIEW and DROP VIEW.

2011-04-05 Excel v3.0 JDBC1.2 Package(912KB) JDBC2.0 Package(945KB) JDBC3.0 Package(980KB) JDBC4.0 Package(1075KB)

SELECT ... FOR UPDATE will lock all the selected rows for transaction until transaction commit or rollback even if you close that ResultSet since v2.2.200.

SELECT ... FOR UPDATE will lock all the selected rows so that other users cannot lock or update the rows until you close that ResultSet since v2.2.198.

v2.2.174 supports function decode( expression , search , result [, search , result]... [, default] )

v2.2.156 fixed a fetch reverse bug from the bottom of an Excel's ResultSet.

v2.2.137 provides tmpdir=_memory_ connection property.

v2.2.108 allow firstRowHasNames connection property to use the data row start from the firstRowHasNames+1 row.

v2.2.107 supports CREATE DATABASE from any input stream.

v2.2.084 fix a bug for DOUBLE/DATE data detection since v2.2.043.

v2.2.080 supports '[]' to specify a range of characters for LIKE/ILIKE.

v2.2.070 supports full ALTER TABLE sql.

v2.2.038 fixed a bug on LEFT JOIN, which will return empty result for specific condition.

v2.2.036 can read invalid xls file, which is produced by Java Excel API with invalid author information.

v2.2.015 fixed a bug on writing specific xls file since v2.1.015.

v2.2.005 supports DISTINCT parameter for the aggregate functions of COUNT, SUM, and AVG.

v2.2.001 supports JDBC4.0.

2008-09-22 Excel v2.2 JDBC1.2 Package(948KB) JDBC2.0 Package(934KB) JDBC3.0 Package(972KB) JDBC4.0 Package(1065KB)

v2.1.016 fixed a bug which the first row become invisible in MS Excel when that row is frozen.

v2.1.011 fixed a bug on ResultSet.last() since v2.0.046.

v2.1.004 supports WITH ROLLUP, WITH CUBE, and GROUPING(expression).

v2.1.004 supports bitwise logical operator(&, |, ~, ^, <<, >>), BITAND(x, y), and BIT_COUNT(x).

2008-07-23 Excel v2.1 JDBC1.2 Package(944KB) JDBC2.0 Package(932KB) JDBC3.0 Package(969KB)

v2.0.036 supports GROUP_CONCAT function.

v2.0.035 supports to assign more than one extension for otherExtension connection property.

v2.0.034 supports lower(upper) function in LIKE expression.

v2.0.001 supports INTO variable[,...] for SELECT sql.

v2.0.001 supports SET variable = expression [,...].

v2.0.001 supports DECLARE Local Variables.

2007-10-29 Excel v2.0 JDBC1.2 Package(953KB) JDBC2.0 Package(940KB) JDBC3.0 Package(979KB)

v1.1.084 replaced "? Expression" with "SELECT select_list".

v1.1.080 provides SHA1 function besides MD5 and Crypt3.

v1.1.078 can detect data type according to format information for cell, row, column, sheet, and workbook, when there's no data.

v1.1.071 can detect data type from "column format" in excel when it meets empty column.

v1.1.070 can detect data type from "cell display format" in excel when it meets empty cell.

v1.1.069 fixed a bug in detecting date value with customer defined format.

v1.1.068 changed the visibility of columns in JOIN table with parentheses from invisible to visible.

v1.1.068 supports JOIN and subquery in PIVOT and UNPIVOT.

v1.1.066 fixed an update bug when meets mixed blank cell and numeric cell.

v1.1.057 supports seamlessly https url database in jdbc url and sql.

v1.1.010 provides maxScanRows connection property for auto data type detection, but it still supports using different data types for a column.

v1.1.001 supports SAMBA table, which needn't to map or mount driver.

2007-01-11 Excel v1.1 JDBC1.2 Package(931KB) JDBC2.0 Package(918KB) JDBC3.0 Package(955KB)

v1.0.047 optimizes memory occupation for UNION ALL.

v1.0.044 added CP850, CP852, CP866, CROATIAN, HEBREW, and SWEDISH sort for COLLATE function.

v1.0.042 fixed an output bug for compressing multiple number values on a row.

v1.0.025 provides function getNumber, getInt, getLong, and getDouble.

2006-08-15 Excel v1.0 JDBC1.2 Package(918KB) JDBC2.0 Package(905KB) JDBC3.0 Package(942KB)

supports seamlessly url(http, ftp) database in jdbc url and sql.

supports seamlessly memory-only database in jdbc url and sql for internal data processing, applets, or certain special applications.

supports seamlessly files and directories in ZIP, GZIP, TAR and BZ2 file formats(.ZIP, .JAR, .TAR, .BZ2, .TGZ, .TAR.GZ, .TAR.BZ2) in jdbc url and sql.

supports all of four transaction levels.

supports CREATE DATABASE, CREATE CATALOG, CREATE TABLE, DROP TABLE, RENAME TABLE, SELECT, INSERT, UPDATE, and DELETE sql.

Development Documentation is available.

supports JDBC3.0, JDBC2.0 and JDBC1.2.

supports create/query/update Microsoft Excel file version from 95, 97, 98, 2000, 2001, 2002, 2003, to 2004.