Sometimes I need to use data from a csv (comma separated values) in an oracle procedure. The best way to do that is inserting the data into a db table using sqlloader and then reading it from the PL/SQL.
In this post I will show you how to use sql loader to load a csv file exported from excel into an oracle table.
Suppose that you got a XLS spreadsheet with contact data: first name, last name, security number, phone number.
First, create a table where the file will be loaded:
(first_name varchar2(200)
,last_name varchar2(200)
,security_number number
,phone_number varchar2(50)
);
In my xls (people.xls) I have the following info:
| First Name | Last Name | Secutiry Number | Phone Number |
| John | Doe | 00000001 | 11111123456 |
| John 01 | Doe | 00000002 | 11111123457 |
| John 02 | Doe | 00000003 | 11111123458 |
| John 03 | Doe | 00000004 | 11111123459 |
| John 04 | Doe | 00000005 | 11111123460 |
| John 05 | Doe | 00000006 | 11111123461 |
| John 06 | Doe | 00000007 | 11111123462 |
Save the data as a comma delimited file.
File -> Save As
Change File name to people_csv
Select ‘Text CSV ’ in the File type drop down box.
If you open the people_csv.csv file in a text editor (vi in my case), you will see it in a comma separated format. Two commas will denote fields without a value, these will be loaded as nulls into the Oracle table.
CSV file:
John ,Doe,00000001,11111123456
John 01,Doe,00000002,11111123457
John 02,Doe,00000003,11111123458
John 03,Doe,00000004,11111123459
John 04,Doe,00000005,11111123460
John 05,Doe,00000006,11111123461
John 06,Doe,00000007,11111123462
Create the following sql loader control file (people_ctrl.txt)
INFILE ‘people_csv.csv’
INTO TABLE people_from_csv
FIELDS TERMINATED BY ‘,’
( first_name, last_name , security_number, phone_number)
at last run the sql loader to put the info into the db table.
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Jan 13 17:26:53 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 7
Now I can use the data from sql with a simple query:
PHONE_NUMBER
————————————————–
11111123456
11111123457
11111123458
11111123459
11111123460
11111123461
11111123462
7 rows selected.
Entries (RSS)