Home / Tutorials

Intermediate 10 min read Updated 21/09/2026

Exporting Excel Files to SQL*Plus: Complete Tutorial

Learn how to efficiently transfer data from Excel spreadsheets to SQL Plus database using multiple methods. This comprehensive guide covers both manual and automated approaches, suitable for different data volumes and scenarios.

In this tutorial:

CSV Export and SQL*Loader method
SQL Developer import tool
Generate SQL INSERT statements
Data type handling and validation
Troubleshooting common issues

You'll need:

Microsoft Excel
Oracle SQL Plus or SQL Developer
Database connection credentials
Basic SQL knowledge

Export Excel Data to SQL Plus: Step-by-Step Guide

SQL*Plus is a command-line SQL client, not a direct Excel import format. Export a worksheet to CSV and load it with SQL*Loader, or use SQL Developer's import wizard. For a few rows, generate and review INSERT statements before running them in SQL*Plus.

Method 1: Using CSV Export

This is the most common and reliable method, suitable for most data sizes.

Prepare Your Excel Data

Format your Excel data to match SQL table structure:

• Map CSV fields to the target table columns in the control file
• Check exported values, delimiters, quotes, dates and character encoding; formulas export their calculated values
• Ensure data types are consistent
• Remove any empty rows and columns

Export to CSV

Save your Excel file as a CSV file:


                            File > Save As > CSV UTF-8 (Comma delimited)

Create Control File

Create a SQL*Loader control file (.ctl) to define the data mapping:

This example assumes three CSV fields and a header row. Replace the table and column names with your schema. Keep a copy of the CSV before loading.


OPTIONS (SKIP=1)
LOAD DATA
INFILE 'your_data.csv'
APPEND
INTO TABLE your_table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    column1 CHAR,
    column2 CHAR,
    column3 DATE "YYYY-MM-DD"
)

Run SQL*Loader

Execute SQL*Loader from command line:


sqlldr userid=username@database control=your_control.ctl log=output.log

Enter the password at the prompt instead of putting it in shell history. Inspect the log and bad-file records before accepting the import.

Method 2: Using SQL Developer

Open SQL Developer

Connect to your database and navigate to the import tool:


                            In Connections, right-click the target table > Import Data

Import Configuration

Configure your import settings:

• Select Excel as source
• Choose your Excel file
• Select target table
• Map columns
• Choose import method

Method 3: Generate SQL Insert Statements

Excel Formula to Generate SQL:


="INSERT INTO table_name (col1, col2, col3) VALUES ('"&SUBSTITUTE(A2,"'","''")&"', '"&SUBSTITUTE(B2,"'","''")&"', '"&SUBSTITUTE(C2,"'","''")&"');"

For three text columns only: double apostrophes in cell values, fill down, then inspect the generated SQL. This simple example does not handle NULL, dates, numbers or every Unicode edge case; use bind variables or SQL*Loader for production imports.

Data Type Handling:

For dates: TO_DATE('"&TEXT(A2,"yyyy-mm-dd")&"', 'YYYY-MM-DD')
For numbers: Remove quotes from the formula

Best Practices & Tips

Do's:

Always backup your data before importing
Validate data types before export
Test with a small dataset first

Don'ts:

Don't skip data validation
Don't ignore error logs
Don't forget to handle NULL values

Common Issues & Solutions

Data Type Mismatches

Ensure Excel data types match SQL column definitions
Special Characters

Use REPLACE() to clean special characters before export
Large Datasets

Break into smaller chunks or use SQL*Loader with direct path