How to Load Excel Files into Oracle Database with OML4Py

Let’s be honest: How much of your “Data Science” time is actually just spent moving .csv or .xlsx files around? You have a brilliant forecasting model in mind. You know which model you want to use. But your raw data is sitting in a spreadsheet on a server, and your machine learning engine is inside the Oracle Database (OML4Py).

Do you download, extract, transform, load, and then finally code? No.

With Oracle Machine Learning for Python (OML4Py), you can use the database to reach out, grab that file, clean it using pandas, and store it as a native database table—all in one seamless workflow.

In this first part of our OML4Py Forecasting Series, we are going to build the foundation. We will take a raw Excel file (online_retail.xlsx) available at /home/oracle path and transform it into a robust Oracle table, ready for serious modeling.

 

Step 1: Defining the “Ingest & Clean” Script

The magic of OML4Py is that you can wrap standard Python code inside a database script. We are going to create a function called create_online_retail_table.

This script does three critical jobs that are annoying to do in pure SQL:

1. Reads the Excel file directly from the file system.
2. Cleans the Data by dropping empty rows (Prophet library hates missing data).
3. Enforces Data Types to ensure Python’s “loose” types match Oracle’s strict schema .

 

The Python Script

Run this PL/SQL block to store your Python logic inside the database:

 

BEGIN

sys.pyqScriptCreate(‘create_online_retail_table’,

‘def create_online_retail_table():

import pandas as pd

import numpy as np

 

# 1. Read the Excel file from the server path

df = pd.read_excel(“/home/oracle/online_retail.xlsx”)

 

# 2. Clean the data: Replace empty strings and drop NaNs

df_cleaned = df.replace(“”, np.nan)

df_final = df_cleaned.dropna(how=”any”)

 

# 3. Explicit Type Conversions to match the Oracle Signature

# This prevents type mismatch errors later on!

df_final[“CustomerID”] = df_final[“CustomerID”].astype(int)

df_final[“Quantity”] = df_final[“Quantity”].astype(int)

df_final[“UnitPrice”] = df_final[“UnitPrice”].astype(float)

 

for col in [“InvoiceNo”, “StockCode”, “Description”, “Country”]:

df_final[col] = df_final[col].astype(str)

 

return df_final’,

FALSE, TRUE); — V_GLOBAL, V_OVERWRITE

END;

/

 

Why did we cast the types? Notice lines like .astype(int)? We do this because OML4Py needs to map the Python DataFrame columns to specific Oracle SQL types (like NUMBER or VARCHAR2). If we don’t be explicit here, we might run into conversion errors when the database tries to save the table

Step 2: Executing and Persisting

Now that the logic is stored, we need to run it. But we don’t just want to run it; we want to save the output permanently. For this we will use the pyqEval function. This specific function is perfect when you have a Python script that generates data from scratch (or an external source) rather than reading an existing database table. We wrap pyqEval in a CREATE TABLE … AS SELECT statement. This effectively creates a pipeline:

Excel File -> Python Cleaning -> Oracle Table.

 

The Execution Command

CREATE TABLE ONLINE_RETAIL AS

SELECT * FROM pyqEval(

NULL, — No additional parameters to pass

— Define the Output Schema (JSON Format)

‘{“InvoiceNo”:”VARCHAR2(20)”,

“StockCode”:”VARCHAR2(20)”,

“Description”:”VARCHAR2(100)”,

“Quantity”:”NUMBER(10, 0)”,

“InvoiceDate”:”TIMESTAMP”,

“UnitPrice”:”NUMBER(10, 2)”,

“CustomerID”:”NUMBER(10, 0)”,

“Country”:”VARCHAR2(50)”}’,

‘create_online_retail_table’ — The script we created in Step 1

);

 

 

What Just Happened?

1. Data Ingestion: The database executed your Python script, which pulled the Excel file from /home/oracle/.
2. Schema Mapping: The JSON string in the SQL command mapped your Python columns to Oracle data types (e.g., UnitPrice became NUMBER(10, 2)).
3. Persistence: The result was saved immediately as the ONLINE_RETAIL table in your database

 

You now have a clean, typed, and accessible table called ONLINE_RETAIL residing comfortably in your database. You didn’t have to manually import a CSV or write complex SQL loaders. But raw data is just the beginning.

In Part 2, we are going to turn this raw transaction data into a machine learning engine. We will look at how to feature engineer this table, split it into training and testing tables, and train a powerful Prophet time-series model—all without the data ever leaving the database.

See you in part 2!

---

 

If you require any assistance regarding automation, please feel free to contact us.

---