When building automated banking reconciliations and MIS reporting pipelines, extracting structured spreadsheets and persisting them directly into an enterprise Oracle database is a common operational requirement.
You can accomplish this reliably in PHP using the PHPExcel or PhpSpreadsheet library together with Oracle's native OCI8 extension. Below is the production-tested code sample that loops through rows and executes structured inserts:
import_excel_to_oracle.php
<?php
/**
* Author: Rajan Maharjan
* Website: https://rajanmaharjan.com.np/
* Description: Automated import of Excel spreadsheet rows into Oracle DB table
*/
require_once 'Classes/PHPExcel.php';
// Define Oracle connection credentials
$dbUser = "DB_USER";
$dbPass = "DB_PASSWORD";
$dbConnStr = "192.168.1.100:1521/ORCL_SERVICE";
// Establish Oracle OCI8 connection
$conn = oci_connect($dbUser, $dbPass, $dbConnStr);
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$inputFileName = 'data_import.xlsx';
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Prepare Oracle INSERT statement
$sql = "INSERT INTO BANK_TRANSACTION_STAGING
(TXN_ID, ACCOUNT_NO, TXN_DATE, AMOUNT, REMARKS)
VALUES (:txn_id, :acct_no, TO_DATE(:txn_date, 'YYYY-MM-DD'), :amount, :remarks)";
$stmt = oci_parse($conn, $sql);
// Loop through Excel rows starting from row 2 (skipping header row)
for ($row = 2; $row <= $highestRow; $row++) {
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE)[0];
$txnId = $rowData[0];
$acctNo = $rowData[1];
$txnDate = $rowData[2];
$amount = (float)$rowData[3];
$remarks = $rowData[4];
// Bind parameters
oci_bind_by_name($stmt, ':txn_id', $txnId);
oci_bind_by_name($stmt, ':acct_no', $acctNo);
oci_bind_by_name($stmt, ':txn_date', $txnDate);
oci_bind_by_name($stmt, ':amount', $amount);
oci_bind_by_name($stmt, ':remarks', $remarks);
// Execute with automatic commit
$res = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
if (!$res) {
$err = oci_error($stmt);
echo "Row {$row} failed: " . $err['message'] . "<br>";
}
}
oci_free_statement($stmt);
oci_close($conn);
echo "Import completed successfully.";
?>Performance Considerations for High Volume
When processing files with tens of thousands of rows:
- Batch Commits: Instead of committing on every single row (
OCI_COMMIT_ON_SUCCESS), execute in blocks of 500 or 1,000 rows and issue an explicitoci_commit($conn). - Memory Optimization: Use
setReadDataOnly(true)on the PHPExcel reader so that cell styling and conditional formats are ignored, drastically cutting RAM overhead. - Sanity Validation: Pre-validate date formats and numeric precision before binding to prevent ORA-01861 or numeric overflow exceptions.