SAP Knowledge Base Article - Public

1669329 - How to create a report of 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 of MS Excel file in Crystal Reports using the Access/Excel (DAO) connection type?
  • How to create a report of MS Excel 2016, or MS Excel 2019 spreadsheet file format in Crystal Reports?

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
           
  • MS Excel 2013
  • MS Excel 2016
  • MS Excel 2019

Cause

  • Microsoft DAO technology cannot be used to access 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 of a MS Excel spreadsheet saved in the format (.xlsx) in Crystal Reports, use one of the following connection type:
    • ODBC
    • OLEDB
             
        
  • To use an ODBC Connection:
             
    1. Launch Microsoft ODBC Data Source Administrator.
             
      • For Crystal Reports 2011, 2013, 2016: (32bit) 
        Launch the 32bit version of Microsoft ODBC Data Source Administrator, from: C:\windows\sysWOW64\odbcad32.exe
           
      • For Crystal Reports 2020: (64bit) 
        Launch the 64bit version of Microsoft ODBC Data Source Administrator, from: C:\Windows\System32\odbcad32.exe
                    
    2. In the 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)
           
      • For Crystal Reports 2011, 2013 and 2016: ( 32bit )
        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 Microsoft Office ODBC driver, go to the Microsoft website, and search for: "Microsoft Access Database Engine"

      • For Crystal Reports 2020: ( 64bit )
        If you do not see the ODBC Driver, then it is either because you have a 32bit version of MS Office installed, or do not have MS Office installed.
        To download and install the 64bit version of the Microsoft Office ODBC driver, go to the Microsoft website, and search for: "Microsoft Access Database Engine"   

                           
    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 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, right click on the connection, and select "Refresh"
      4. The Excel sheets will now display.

 

  • To use an OLEDB 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 Access Database Engine Provider.

      • For Crystal Reports 2011, 2013 and 2016: ( 32bit )
        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 Microsoft Office ODBC driver and OLEDB Provider, go to the Microsoft website, and search for:
        "Microsoft Access Database Engine"
                         
      • For Crystal Reports 2020: ( 64bit )
        If you do not see the OLEDB Provider, then it is either because you have a 32bit version of MS Office installed, or do not have MS Office installed.
        To download and install the 64bit version of the Microsoft Office ODBC driver and OLEDB Provider, go to the Microsoft website, and search for:
        "Microsoft Access Database Engine"        

    1. Change the Office Database type to: Excel

    2. Select the MS Excel file.

    3. Leave the User ID 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 Business View Manager , How To

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020