Home » SQL & PL/SQL » SQL & PL/SQL » Reading CSV file Using UTL_FILE
Reading CSV file Using UTL_FILE [message #427501] Thu, 22 October 2009 23:50 Go to next message
ramya29p
Messages: 100
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~~
Re: Reading CSV file Using UTL_FILE [message #427504 is a reply to message #427501] Fri, 23 October 2009 00:04 Go to previous messageGo to next message
Frank
Messages: 7880
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)
Re: Reading CSV file Using UTL_FILE [message #427515 is a reply to message #427504] Fri, 23 October 2009 01:00 Go to previous messageGo to next message
ramya29p
Messages: 100
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));
Re: Reading CSV file Using UTL_FILE [message #427517 is a reply to message #427515] Fri, 23 October 2009 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Reading CSV file Using UTL_FILE [message #427520 is a reply to message #427515] Fri, 23 October 2009 01:19 Go to previous messageGo to next message
Frank
Messages: 7880
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?
Re: Reading CSV file Using UTL_FILE [message #427521 is a reply to message #427520] Fri, 23 October 2009 01:24 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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

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


Current Time: Thu Dec 08 01:54:31 CST 2016

Total time taken to generate the page: 0.15193 seconds