Connecting Excel 2013 to SSRS 2012 to use as a datasource?

Question

I am trying to set up a Data Source using an Excel 2013 file for the information to feed a report. Here are the steps I'm following: 1. Click on Data Source 2. Add new Data Source 3. Change Type to ODBC 4. Select Edit for Connection String 5. Choose Excel as System Data Source Name 6. Select Use Connection String- (First issue- find Excel, but does not give me an option for XLSX files.) Only XLS files. Any suggestions. Thanks, Scott

Answer 1

It seems like you're encountering an issue with Excel 2013 not providing an option for XLSX files when setting up an ODBC data source. This limitation might be due to the version of the ODBC driver installed on your system. Here are a few suggestions to address the problem:

Install the latest version of the Microsoft Access Database Engine: This driver includes the necessary components to connect to Excel files. You can download it from the official Microsoft website. Make sure to choose the appropriate version (32-bit or 64-bit) based on your Excel and operating system configuration.

Check the ODBC driver settings. Open the ODBC Data Source Administrator tool on your computer. You can find it in the Control Panel or by searching for "ODBC" in the Start menu. In the tool, navigate to the "Drivers" tab and verify if the Microsoft Excel driver is listed. If it's missing or outdated, you might need to update or reinstall the appropriate driver.

Try alternative methods: Instead of using ODBC, you can consider other approaches to connecting Excel data as a data source. For example, you can use the "From File" option in your reporting tool (e.g., Excel, Power BI, Tableau) to directly import data from your Excel file. This method bypasses the need for ODBC configuration.

Upgrade to a newer version of Excel: If possible, upgrading to a more recent version of Excel (e.g., Excel 2016 or later) can provide better compatibility and support for XLSX files when setting up ODBC data sources.

Remember to back up your files and consult your organization's IT department or a technical expert for further assistance if needed.