Exporting Excel Files to SQL*Plus: Complete Tutorial
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
Learn how to efficiently transfer data from Excel spreadsheets to SQL Plus using different methods. We'll cover both manual and automated approaches suitable for different data volumes.
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:
- • Column names should match SQL table columns
- • Remove any formatting, formulas, and special characters
- • 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 (Comma delimited)
Create Control File
Create a SQL*Loader control file (.ctl) to define the data mapping:
LOAD DATA
INFILE 'your_data.csv'
INTO TABLE your_table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
column1,
column2,
column3 DATE "YYYY-MM-DD"
)
Run SQL*Loader
Execute SQL*Loader from command line:
sqlldr userid=username/password@database control=your_control.ctl log=output.log
Method 2: Using SQL Developer
Open SQL Developer
Connect to your database and navigate to the import tool:
Tools > 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 ('"&A2&"', '"&B2&"', '"&C2&"');"
Drag formula down to generate INSERT statements for all rows
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