SAP Datasphere & Python : One click to export data of multiple views in Excel/CSV

[[{“value”:”

Introduction

Currently, SAP Datasphere only allows data export through Analytical Models. That means for every fact view, one has to create a separate Analytical Model just to download the data. It’s not ideal, especially when you have many views. Exporting them one by one becomes slow and repetitive.

To solve this,  Python script was developed that connects to SAP Datasphere, runs a query on each view from list, and saves the results as Excel files in a local drive path which is predefined. Now  just run the script, and it exports everything in one go—no manual effort needed.

Requirements 

Before running the script, install the following Python packages:

These packages enable secure connectivity to SAP Datasphere and support efficient data handling.

pip install hdbcli
pip install sqlalchemy
pip install sqlalchemy-hana

Creating a Database User in SAP Datasphere

To enable Python connectivity, create a database user:

1. Navigate to Space Management in SAP Datasphere
2. Select the relevant space
3. Click on Database Access
4. Create a new user with read access
5. Copy the host, port, username, and password

Helpful links : Create DB User in Datasphere Space  

Python Script

# 📦 Install required packages (run these in your terminal or notebook)
# pip install hdbcli              # SAP HANA database client
# pip install sqlalchemy          # SQL toolkit and ORM for Python
# pip install sqlalchemy-hana     # SAP HANA dialect for SQLAlchemy

# 📚 Import necessary libraries
import pandas as pd               # For data manipulation and Excel export
from hdbcli import dbapi          # SAP HANA DBAPI for direct connection
import warnings                   # To suppress unnecessary warnings
import os                         # For file path and directory handling

# 🚫 Suppress warnings for cleaner output
warnings.filterwarnings('ignore')

# 🔐 Define SAP Datasphere connection parameters
# 👉 Replace the placeholders below with your actual connection details
db_user = '<your_database_user>'           # User with access to target schema
db_password = '<your_secure_password>'     # Password (handle securely)
db_host = '<your_datasphere_host_url>'     # Host URL (e.g., xyz.hanacloud.ondemand.com)
db_port = 443                               # Default HTTPS port for SAP HANA Cloud
db_schema = '<your_schema_name>'           # Target schema containing views

# 📁 Ensure output folder exists for Excel exports
output_folder = r'C:DatasphereExcel export'  # Update path as needed
os.makedirs(output_folder, exist_ok=True)

# 📋 Define list of views to extract data from
# 👉 Add or modify view names based on your schema
view_list = ['VIEW_1', 'VIEW_2']  # Example views

try:
    # 🌐 Establish secure connection to SAP Datasphere
    connection = dbapi.connect(
        address=db_host,
        port=db_port,
        user=db_user,
        password=db_password,
        encrypt=True,
        sslValidateCertificate=True
    )
    print("✅ Connected to SAP Datasphere")

    cursor = connection.cursor()

    # 🔁 Loop through each view and export its data
    for view_name in view_list:
        try:
            # 📊 Construct and execute SQL query
            sql_query = f'SELECT * FROM "{db_schema}"."{view_name}"'
            print(f"📊 Executing query: {sql_query}")
            cursor.execute(sql_query)

            # 📥 Fetch results and convert to DataFrame
            rows = cursor.fetchall()
            columns = [desc[0] for desc in cursor.description]
            df = pd.DataFrame(rows, columns=columns)

            # 📤 Export DataFrame to Excel
            output_path = os.path.join(output_folder, f'{view_name}.xlsx')
            df.to_excel(output_path, index=False)
            print(f"✅ Data from '{view_name}' saved to: {output_path}")

        except dbapi.Error as view_err:
            print(f"❌ Error querying '{view_name}': {view_err}")

except dbapi.Error as db_err:
    print(f"❌ Database error: {db_err}")
except Exception as ex:
    print(f"⚠️ Unexpected error: {ex}")
finally:
    # 🔒 Ensure connection is closed gracefully
    if 'connection' in locals():
        connection.close()
        print("🔒 Connection closed")

Script Capabilities

• Establishes secure connection to SAP Datasphere
• Executes queries on each listed view
• Saves data from each view into a separate Excel file
• Stores all files in a defined folder

Only connection details and view names need to be updated. The script handles the rest.

 One-Click Execution with a .bat File

To simplify execution, create a  RunExport.bat file to run the Python script with a double-click.

Double-clicking the file will automatically export all views to Excel without opening a terminal

 off
REM Activate Python and run the Export script

REM Change to the script directory
cd /d "C:DatasphereExcel export"

REM Run the Python script
python Export.py

REM Pause to keep the window open (optional)
pause

Example

Before Execution 

Double Click on “RunExcel.bat” file

Post Execution

Conclusion
This automation simplifies data exports from SAP Datasphere, especially when working with multiple views.

• It reduces manual effort
• improves consistency and saves time.
• ideal for recurring tasks or scheduled jobs.

For setup support or customization, feel free to connect.

Thanks

Vikas Parmar

“}]] 

  Read More Technology Blog Posts by Members articles 

#abap