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

[jansch]

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

         

          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

         

          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

          OData-Service

         

          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.