Loading data from an Excel file using SQL Loader.

12 Oct 2023

sqlloader

SQL Loader is a powerful command-line tool that comes with Oracle Database, allowing bulk data to be loaded from various data sources. While it is commonly used for loading data from flat files, it can also be used to load data from Excel files. In this blog post, we will explore how to use SQL Loader to load data from an Excel file into an Oracle Database.

Prerequisites

Before we begin, make sure you have the following prerequisites in place:

Oracle Database installed and running
SQL Loader installed and configured on your machine
An Excel file containing the data you want to load

Steps to load data from Excel file using SQL Loader

Step 1: Prepare the Excel file

Start by opening the Excel file and ensuring that it is properly formatted. Make sure the data you want to load is in a single sheet with the columns aligned correctly. Save the file and note its location on your machine.

Step 2: Create a Control File

The control file is a text file that defines how SQL Loader should interpret and load the data from the Excel file. Create a new text file and save it with a .ctl extension. In the control file, specify the location of the Excel file, as well as the format and layout of the data.

Here is an example of a simple control file for loading data from an Excel file:

LOAD DATA
INFILE "path/to/excel/file.xlsx"
APPEND INTO TABLE employee
FIELDS TERMINATED BY ","
(
  emp_id,
  emp_name,
  emp_salary
)

In the above example, we assume that the Excel file is located at “path/to/excel/file.xlsx” and we want to load the data into a table called “employee”. The FIELDS TERMINATED BY clause specifies the delimiter used in the Excel file (comma in this case), and the columns to load are specified within the parentheses.

Step 3: Load Data using SQL Loader

Open a command prompt or terminal and navigate to the directory where SQL Loader is installed. Run the following command to start the data loading process:

sqlldr username/password@database control=path/to/control/file.ctl log=path/to/log/file.log

Replace username and password with your Oracle Database credentials, and database with the database name or connection string. Specify the path to the control file and log file as well.

Step 4: Verify the Data Load

After the data loading process completes, open the log file specified in the previous step. The log file will provide information about the success or failure of the data loading process. Verify that the data was loaded correctly into the specified table.

Conclusion

By following these steps, you can easily load data from an Excel file into an Oracle Database using SQL Loader. SQL Loader provides a flexible and efficient way to handle bulk data loading tasks, making it a valuable tool for database administrators and developers.

Remember, SQL Loader can handle a variety of data sources and file formats, so feel free to explore its other capabilities for your data loading needs.

#sql #sqlloader