Home » SQL & PL/SQL » SQL & PL/SQL » Reading Flat File with Header (2 Merged) (Oracle 11g, Windows xp)
Reading Flat File with Header (2 Merged) [message #559485] Tue, 03 July 2012 10:19 Go to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Hi,
I'm working on loading the data from flat file into table and below given is the
validation condition given.
I checked the UTL_FILE build in package but not able to figure out, how to identify the column header in flat file.

1. Skip the header, if any. The header is the first record, and starts with '000'
2. Skip the trailer, if any. The Trailer is the last record, and starts with '999'
3. Log an error, but continue if a line exceeds 512 characters
4. Log an error, but continue if a line is blank

Appreciate any help on this.

Regards,
Lokesh
Re: Reading Flat File with Header [message #559487 is a reply to message #559485] Tue, 03 July 2012 10:25 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
many examples exist if you do just a little research

http://www.lmgtfy.com/?q=oracle+utl_file+example
Re: Reading Flat File with Header (2 Merged) [message #559496 is a reply to message #559485] Tue, 03 July 2012 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59095
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A flat file has no header unless you define one.
How do you identify the header when you open the file in vi/notepad?
Do it in the same way in PL/SQL.
There is no difficulties in the specification you gave it is just basic PL/SQL you can do with the examples about UTL_fILE on the web and here.

Regards
Michel

[Updated on: Tue, 03 July 2012 10:52]

Report message to a moderator

Re: Reading Flat File with Header (2 Merged) [message #559523 is a reply to message #559485] Tue, 03 July 2012 17:23 Go to previous messageGo to next message
matthewmorris68
Messages: 211
Registered: May 2012
Location: Orlando, FL
Senior Member

If the file has consistent formatting, defining a spec to access it as an external table is likely easier than using UTL_FILE. At that point, you can use LOAD WHEN to remove header & footer or simply a WHERE clause when you access the 'table' via SQL.
Re: Reading Flat File with Header (2 Merged) [message #561041 is a reply to message #559485] Wed, 18 July 2012 07:24 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thank you all for your response.
Here is what I did,
1. Created new temp table with Sequence number
2. Loaded data from flat file to temp table and incremented sequence
3. Loaded data from temp table to actual table omitting header and footer.


SELECT SEQ.NEXTVAL AS Data_ID 
       , Text AS Target_Record
FROM   ( SELECT CASE WHEN (seqno = 1 AND text like '000%') THEN 'N'
		     WHEN (seqno = (SELECT COUNT(1) FROM LoadFile) AND text like '999%') THEN 'N'
		     ELSE 'Y'
		END AS SkipHdrFtr   -- This logic will skip the header & Footer of Flat file
		,SeqNo
		,Text
	  FROM   LoadFile) Tmp
WHERE SkipHdrFtr = 'Y';  


I'm not sure whether its an best option, but it is working for me Smile

Regards,
Lokesh
Re: Reading Flat File with Header (2 Merged) [message #561045 is a reply to message #561041] Wed, 18 July 2012 08:22 Go to previous message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

You can go with external table is better...
Previous Topic: Time Interval in Schedule Job
Next Topic: ORDER BY in a particular order
Goto Forum:
  


Current Time: Wed Sep 17 02:52:11 CDT 2014

Total time taken to generate the page: 1.05124 seconds