OraFAQ Forum: SQL & PL/SQL » Reading CSV file Using UTL

Home » SQL & PL/SQL » SQL & PL/SQL » Reading CSV file Using UTL_FILE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reading CSV file Using UTL_FILE [message #427501]

Thu, 22 October 2009 23:50

ramya29p
Messages: 146
Registered: November 2007
Location: Chennai

Senior Member

Hi I have a CSV file with the Following Content. I want to Read 3rd & last line. Can any one tell me how to Acheive it?

USD,TWD,SBV,32.57,0,0,32.57,32.57,907,,REUTERS,,,A,17,1,~~END~~
USD,TWD,STANDARD,32.57,0.3,0.3,32.27,32.87,907,,REUTERS,,,A,18,1,~~END~~
USD,VND,CASH,15985,0,0,15985,15985,907,,REUTERS,,,A,19,1,~~END~~
USD,VND,LMREVRAT,16106,0,0,16106,16106,907,,REUTERS,,,A,20,1,~~END~~
USD,VND,SBV,16106,0,0,16106,16106,907,,REUTERS,,,A,21,1,~~END~~
USD,VND,STANDARD,15985,0,0,15985,15985,907,,REUTERS,,,A,22,1,~~END~~
~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~,~~END~~

Report message to a moderator

Re: Reading CSV file Using UTL_FILE [message #427504 is a reply to message #427501]

Fri, 23 October 2009 00:04

Frank
Messages: 7901
Registered: March 2000

Senior Member

There are several ways to read the contents of an ascii file into the database.
The two ways that are used the most are sqlloader and external tables. With sqlloader the data is typically loaded into staging tables first, after which processing takes place to get the data from those staging tables into the "actual" tables.
Using external tables skips the step of the loading of the staging tables; Oracle simply treats the file itself as a staging table.

So, as you can see, you can simply create a SQL query to get the rows you want. Now it is up to you to define what makes your desired rows stand out (aka, what should come in your where-clause)

Report message to a moderator

Re: Reading CSV file Using UTL_FILE [message #427515 is a reply to message #427504]

Fri, 23 October 2009 01:00

ramya29p
Messages: 146
Registered: November 2007
Location: Chennai

Senior Member

Actually I Want to Read 3rd & lastline from teh file using UTL_FILE.GET_LINE(file,l_file_buf(l_count));

Report message to a moderator

Re: Reading CSV file Using UTL_FILE [message #427517 is a reply to message #427515]

Fri, 23 October 2009 01:04

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to read all lines and keep only the ones you want.
How could Oracle knows it is the third or last line without reading them all?

Regards
Michel

Report message to a moderator

Re: Reading CSV file Using UTL_FILE [message #427520 is a reply to message #427515]

Fri, 23 October 2009 01:19

Frank
Messages: 7901
Registered: March 2000

Senior Member

ramya29p wrote on Fri, 23 October 2009 08:00

Actually I Want to Read 3rd & lastline from teh file using UTL_FILE.GET_LINE(file,l_file_buf(l_count));

Any special reason why you want to use UTL_FILE for this?

Report message to a moderator

Re: Reading CSV file Using UTL_FILE [message #427521 is a reply to message #427520]

Fri, 23 October 2009 01:24

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Frank wrote on Fri, 23 October 2009 08:19

ramya29p wrote on Fri, 23 October 2009 08:00
Actually I Want to Read 3rd & lastline from teh file using UTL_FILE.GET_LINE(file,l_file_buf(l_count));

Any special reason why you want to use UTL_FILE for this?

Because Boss said to use the slowest possible way to keep job for optimization.

Regards
Michel

Report message to a moderator

Previous Topic:	Problem with a select distinct
Next Topic:	Sequence Start with Value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 18:17:30 CST 2024