Reading spreadsheets in XLSX format.

Thursday 30 June 2016 @ 11:30 pm

Crystal has a native driver that lets you read data stored in older Excel spreadsheets (XLS). But if you want to read data stored in the current spread format (XLSX) then you will need to get an OLEDB/ODBC driver for Excel. I have had to do this recently for two customers and it took me a while to find the correct driver. So to make it easier for the next time I am going to put the link here.

Part of the problem is that the Excel driver is part of a larger set of drivers included in the Microsoft Access Database Engine 2016 Redistributable (or earlier versions for 2013 or 2010). This includes both ODBC and OLEDB drivers for Access (MDB/ACCDB) and Excel (XLS/XLSX). When you start the download there will be two options:

AccessDatabaseEngine.exe (32 bit)
AccessDatabaseEngine_x64.exe (64bit).

Even though you most likely have a 64-bit PC you might not want the 64-bit driver.  If you have Crystal Reports 2020 you want the 64-bit version, while all prior versions of Crystal Reports need the 32-bit   version.

Once you have installed the correct driver you can read my previous blog post on how to launch the corresponding ODBC Administrator, or you can skip that and connect using OLEDB. The ODBC configuration is much more confusing than it should be.

And thanks to Adam Butt of APB Reports in Norway for showing me how much simpler OLEDB is over ODBC.









Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server