Skip to Content

Export / Import data from SAP HANA to MS Excel using OData

I was looking for an easy-to-use opportunity to quickly get some data from SAP HANA to Excel, but I didn’t find anything usefull. So after all I decided to find a solution and write a document about it to help others using the same solution.

But let’s start!


– Access to the HANA you want to export data from

– A table / view in your HANA that you want to export

– MS Excel (32/64Bit; doesn’t matter)

– “Power Query”-Addon for Excel (can be downloaded here)

– Some basic knowledge about SAP HANA can’t hurt


1. We will create an OData-Service that gives us the data we want to export.

          For this we need a table or view that gives us some data. You can preview this data by right-clicking the

          table / view and select “Open Content” in HANA Studio


          Now create an OData-Service that outputs that data.

          Since I do have a primary key in my table, I can use the following:

service {
     "EXPORT_TO_EXCEL"."EXPORT_DATA" as "SomeData";

          If you do not have a primary key, you would need to generate one since your xsodata file will not be activated


service {
     "EXPORT_TO_EXCEL"."EXPORT_DATA" as "SomeData"
     key generate local "LocalKey";

          Now where we do have a working and activated OData-Service, you can test it in your browser using the

          following link-structure:


          This will display the data in JSON-Format.

2. We need to connect the OData Service to Excel

          As a requirement I listet the “Power Query”-Addon, so for the following steps you need to see a Tab

          “POWER QUERY” in your Excel. If not, please contact Dr. Google for further diagnostics.

          Open an empty Excel-Workbook and choose “POWER QUERY” -> “From other source” -> “From OData Feed”

          In the text-box, insert the link to the xsodata-File:


          and klick OK


          In the following procedure, select “Standard” from the left hand navigation pane, enter your login credentials and

          click “Connect”


3. We need to select what we want to import to Excel

          From now on it won’t be too hard to guess what happens

          Select the Data you want. There is also an option “Select Multiple items” if you have multiple outputs in your



          Here you either can just load the data into the table or edit the data before importing it.


          In the “Edit”-Window you have options to select what to import and format the data as you want.



          Click “Close & Load”

4. Done

          Your data has been imported and formatted


5. Additional Information

          Based on the Excel-Version used, there is a maximum amount of data that can be imported. For example, if

          you are using Office 2013 32Bit the limit would be at about 1 Mio. Rows and 16’000 Columns.

You must be Logged on to comment or reply to a post.
  • Hi Jay

    im trying to download a table into Excel through POWER QUERY, I already built the OData service, I already test the service directly on a browser and is working, but once i try to retrieve the data into excel this message comes up


    I think has to do with the credential prompt, Im not even able to introduce my user or so.

    Do you know if i have to do some special setting on my excel before?



    • Hi Belinda

      As you can see in the error message, your HANA server redirects the request to the login form. This is in HTML format and Excel can't handle that.

      Please edit the access method in your .xsaccess from "Form" to "Basic" so that Excel can handle the login.

      If you don't have one: create a file named .xsaccess in the same directory as your XSoData-File and add the following content:


           "exposed": true,



                     "method": "Basic"



      That should solve it! If it doesn't, just reply again 🙂

  • Nice! Sorry for a shameless self-plug, but in case you need a solution that does not rely on the user having a specific Excel plugin, you might want to check out ODXL

    ODXL - An open source Data Export Layer for SAP/HANA based on OData

    Basically, it allows you to send standard OData requests to a xsjs service, and allows you to return the data as csv or excel workbooks. Multi-sheet workbooks can be created transparently via a OData $batch request.

    If you need other output formats besides csv or excel, you can quite easily implement your own data renderer and plug it into ODXL to download data in custom output formats.

    ODXL is free and open source. See the article for how to download and install.

    I hope it helps! Best regards, Roland.

    • Hi Roland

      This is a great solution too. But I really like the power query, thats why I work with it. It's developed by MS itself and its not only on this case useful but in any other case where it's all about importing some sort of data into Excel. It provides functionality that should be implemented natively in Excel... A must have for every Excel-Poweruser.

      Best Regards,


  • After reading the SAP tutorial I still wasn't able to figure out how to access my OData service through Excel. This step-by-step guide helped to do it.

    Further I have a requirement to write data into table using OData. Have you written any article on this? Any pointers will be very helpful. Thanks Jan.



    • Hi Murthy

      Great that this article helped you out...

      What is the exact scenario? Do you want to write data in an Excel-File into a SAP HANA-Table?



      • Hi Jan

        My requirement is to enable the super-user/business user to load files to HANA directly through excel.
        We can do it through MS-Access but this is not the secured way.

        Please guide. Thanks.



        • Ok, so you are looking for the other way around...

          I don't know anything that works directly through Excel. I do have worked with MS-Access to load data to HANA as you stated and I found it the easiest way! (Maybe you find another ODBC-Client that has the secured way you need)

          I just know of two other solutions: Loading it through SAP HANA Studio or (if you have an OnPrem-System or HEC) loading data using SAP Data Services. Both of them are not through Excel itself though!

          Hope that helps...

          Best Regards,


  • Hi Jan,

    Good blog. But I think OData link will be useful for Google Sheets. For MS Excel, "HANA MDX for MS Excel" can be installed to link SAP HANA with MS Excel pivot table.


    Ibrahem Ahmed.

    • Hi Ibrahem

      You're right, there are several different ways of achieving that. I just wrote a blog about this way since I found it the easyest for the simple goal of getting a HANA Table to Excel.

      You could also use Analysis for Office if you would like to analyse the data and get Pivot Tables and charts...

      Best Regards,


  • Cdata Software has developed the SAP Excel Add-In, which is a powerful tool that allows you to connect with live data from SAP NetWeaver directly from Microsoft Excel. Use Excel to read, search, and aggregate SAP data. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!

    They also have developed The OData Excel Add-In, which is a powerful tool that allows you  to search, aggregate, read, write, and update live OData feeds directly from MS Excel.


    For more info visit: