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:
Navigate to Space Management in SAP DatasphereSelect the relevant spaceClick on Database AccessCreate a new user with read accessCopy 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 DatasphereExecutes queries on each listed viewSaves data from each view into a separate Excel fileStores 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 effortimproves consistency and saves time.ideal for recurring tasks or scheduled jobs.
For setup support or customization, feel free to connect.
Thanks
Vikas Parmar
IntroductionCurrently, 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-hanaCreating a Database User in SAP DatasphereTo enable Python connectivity, create a database user:Navigate to Space Management in SAP DatasphereSelect the relevant spaceClick on Database AccessCreate a new user with read accessCopy the host, port, username, and passwordHelpful 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 CapabilitiesEstablishes secure connection to SAP DatasphereExecutes queries on each listed viewSaves data from each view into a separate Excel fileStores all files in a defined folderOnly connection details and view names need to be updated. The script handles the rest. One-Click Execution with a .bat FileTo 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)
pauseExampleBefore Execution Double Click on “RunExcel.bat” filePost ExecutionConclusionThis automation simplifies data exports from SAP Datasphere, especially when working with multiple views.It reduces manual effortimproves consistency and saves time.ideal for recurring tasks or scheduled jobs.For setup support or customization, feel free to connect.ThanksVikas Parmar Read More Technology Blog Posts by Members articles
#SAP
#SAPTechnologyblog
