Splitting data into multiple columns during loading in SQL Loader.

12 Oct 2023

In the world of data processing, there are often cases where a single column in a data file needs to be split into multiple columns during the loading process. One common scenario is when a single column contains values that are separated by a delimiter, such as a comma or a tab, and you want to load each value into a separate column in the database.

To achieve this, SQL Loader provides a feature called “field concatenation”, which allows you to split a single input field into multiple output columns. This can be accomplished by specifying multiple field specifications in the control file.

Let’s say we have a data file called data.txt with the following content:

John,Smith,25
Jane,Doe,30

Each line in the file represents a record with three values: first name, last name, and age. We want to load this data into a table with the following columns:

first_name | last_name | age

To split the data in the data.txt file into these columns, we can create a control file called control.ctl with the following content:

LOAD DATA
INFILE 'data.txt'
INTO TABLE employees
FIELDS TERMINATED BY ","
(
    first_name,
    last_name,
    age
)

In the control file, we specify that the fields in the input file are terminated by a comma. Then, we provide three field specifications, one for each column in the table.

When we run SQL Loader with the control file, it will split the data in each line of the data file based on the specified delimiter and load it into the respective columns in the table.

sqlldr username/password control=control.ctl

After running the SQL Loader command, the data will be loaded into the employees table with the values correctly split into separate columns.

This approach can be extended to handle more complex scenarios where the delimiter is different for each record or when there are varying numbers of values in each record.

Splitting data into multiple columns during loading in SQL Loader is a powerful feature that allows for efficient and flexible data processing. Using the field concatenation technique in the control file, you can easily split data into multiple columns based on delimiters and load it into your database tables.

#sqlloader #dataprocessing