Pillai’s

Technology and Everything…..

String to Fields – Converting CSV file to Table Data

Posted by Shibu on March 17, 2008

Recently in one of my project’s work I had to read a csv file and insert into Oracle database. Though sql loader is an option I had to use PL/SQL as we have to do some processing before inserting into the table.

So initially each record from the file is read and inserted into a temporary table. This table just has one Varchar2 field and few other fields to hold the metadata. Basically each record from the file is inserted as a Varchar2 field in this generic table.

I created a function which will receive one record as a parameter and then split the string into individual fields and populate an array of strings.

If you are interested in, following is the source code:

Declare
type v_array is table of varchar2(300) index by binary_integer;
in_string VARCHAR2(2000);
v_index NUMBER := 0;
v_data_array v_array;
o_index NUMBER := 0;
i NUMBER;
BEGIN
— Assign a value to the string for test purpose
in_string:=”;
WHILE 1 0
THEN
v_index := v_index + 1;
END IF;
IF SUBSTR (in_string, v_index, 1) = ‘,’ OR v_index = 0
THEN
/* We have to process one field here */
— Get data into the first array */
— Increment index of result array here
o_index := o_index + 1;
v_data_array (o_index) := ”;
WHILE 1 = LENGTH (in_string);
v_data_array (o_index) :=
v_data_array (o_index)
SUBSTR (in_string, v_index, 1);
— we will exit this loop when we find next , character or the end of input string
END LOOP; — for end of each field
— if it is not end of string we should reduce index by one otherwise we will miss next field !!!
IF v_index = LENGTH (in_string); — exit when last characte of the string is read
END LOOP; — for the string
END;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: