Skip to Content

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!


Requirements

– 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


Procedure

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

          Capture.JPG

          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

          otherwise.


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:

http://<server_name>:<port>/path/to/file.xsodata/SomeData/?$format=json

          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:

http://<server_name>:<port>/path/to/file.xsodata

          and klick OK

          Capture4.JPG

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

          click “Connect”

          Capture5.JPG

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

          OData-Service

          Capture.JPG

          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.

  

          Capture3.JPG

          Click “Close & Load”

4. Done

          Your data has been imported and formatted

          Capture2.JPG

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.

To report this post you need to login first.

16 Comments

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

  1. Jatin Oza

    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

    HANA.PNG

    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?

    BR

    Belinda

    (0) 
    1. Jan Schanzenbaecher Post author

      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,

           “authentication”:

                {

                     “method”: “Basic”

                }

      }

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

      (0) 
  2. Roland Bouman

    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.

    (0) 
    1. Jan Schanzenbaecher Post author

      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,

      Jan

      (0) 
  3. Murthy Gunuputi

    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.

    Regards

    Murthy

    (0) 
    1. Jan Schanzenbaecher Post author

      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?

      Regards,

      Jan

      (0) 
      1. Murthy Gunuputi

        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.

        Regards

        Murthy

        (0) 
        1. Jan Schanzenbaecher Post author

          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,

          Jan

          (0) 
      2. dimitri susana

        Very good Blog.

        HANA Export and Import option allows tables, Information models, Landscapes to move to a different or existing system. You do not need to recreate all tables and information models as you can simply export it to new system or import to an existing target system to reduce the effort.

        Thank for sharing!

        (0) 
  4. Ibrahem Ahmed

    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.

    Regards.

    Ibrahem Ahmed.

    (0) 
    1. Jan Schanzenbaecher Post author

      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,

      Jan

      (0) 
  5. Altin Elezi

    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: https://www.cdata.com/drivers/sap/excel/

    https://www.cdata.com/drivers/odata/excel/

    (0) 

Leave a Reply