1669329 - How to create a report off MS Excel spreadsheet saved in .xlsx format in Crystal Reports?

SAP Knowledge Base Article - Public

1669329 - How to create a report off MS Excel spreadsheet saved in .xlsx format in Crystal Reports?

Symptom

  • How to create a report using a file with the extension .xlsx
  • How to create a report using MS Excel XML file format. ( .xlsx format ). 
  • Unable to create a report off MS Excel file in Crystal Reports using the Access/Excel (DAO) connection type.
  • How to create a report off MS Excel 2007, MS Excel 2010, MS Excel 2013, or MS Excel 2016 spreadsheet file format in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
          
  • MS Excel 2007
  • MS Excel 2010
  • MS Excel 2013
  • MS Excel 2016

Cause

  • DAO technology cannot be used to access the Microsoft Office 2007 and above file formats: Microsoft Access 2007 .accdb, Microsoft Excel 2007 .xlsx (XML) and .xlsb(Binary). These file formats are supported via the Microsoft 2007 Office System drivers for ODBC and OLEDB.

Resolution

  • To report off a MS Excel spreadsheet saved in the format (.xlsx) in Crystal Reports, using one of the following:
    • An ODBC connection.
    • An OLE DB connection.
             
        
  • To use an ODBC Connection:
             
    1. Launch the 32bit version of the Microsoft ODBC Administrator.
        
      • 1.1  If you are using a 32bit version of MS Windows, launch the MS ODBC Administrator by selecting 
              "Start - Control Panel - Administrative Tools - Data Source (ODBC)"
               
      • 1.2  If you are using a 64bit version of MS Windows, launch the MS ODBC Administrator from the path: 
              C:\windows\sysWOW64\odbcad32.exe
             
              
    2. In the 32bit version of Microsoft ODBC Administrator, under the tab "System DSN", click on the button "Add..."
          
    3. Select  the ODBC Driver: Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)
         
      • If you do not see the ODBC Driver, then it is either because you have a 64bit version of MS Office installed, or do not have MS Office installed. To download and install the 32bit version of the Mircoroft Office ODBC driver, go to the Microsoft website. ( Link for the 32bit version of the Microsoft Office ODBC Driver.)     
                 
    4. Then click "Finish"
            
    5. Add a DSN Name and click on "Select Workbook", to select the MS Excel worksheet you want to report from, and click "OK"
                  
    6. In Crystal Reports, create a new report.
            
    7. In the Database Expert, expand Create New Connection, and double click on ODBC (RDO)
         
    8. Select the ODBC DSN created in the previous steps to report from your MS Excel Spreadsheet.
            
      Note: If the MS Excel sheets are not visible in the Database Expert then: 
      1. Right click on the Connection, and in the contextual menu, select Options
      2. In the Options window, check the option System Tables, and click OK
      3. Back to the Database Expert window, right click on the connection, and select Refresh
      4. The MS Excel sheets will now display. Select the desired MS Excel sheet.

 

  • To use an OLE DB connection
    1. In Crystal Reports, create a new report.

    2. In the Database Expert, expand Create New Connection, and double click on OLE DB (ADO)

    3. Select the Microsoft Office 12.0 Access Database Engine Provider
       
      If you do not see the OLEDB Provider, then it is either because you have a 64bit version of MS Office installed, or do not have MS Office installed. To download and install the 32bit version of the Mircoroft Office ODBC driver and OLEDB Provider, go to the Microsoft website. ( Link for the 32bit version of the Microsoft Office ODBC Driver.)

    4. Change the Office Database type to: Excel

    5. Select the MS Excel file.

    6. Leave UserId and Password blank.

Keywords

.xlsx, import MS Excel 2007, MS Excel 2007, import .xlsx file, .xlsx data source, crystal reports , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016