| Control file to search and skip rows [message #349202] |
Fri, 19 September 2008 05:25  |
joebunny
Messages: 18
Registered: September 2008
|
Junior Member |
|
|
Hi All,
I have written a control file that reads data from a text file and uploads it in a table in database.
But the problem I am facing is that the text file that I used to upload data contains queries also which I need to skip to be able to upload the data. My objective is to upload data in the database without having to open the text file and counting how many rows to skip.
Is there any way by which I could find out the occurence of the name of the first column and start uploading data from the next row into my table?I need to acheive this because the text file I am uploading the data from is quite large and its not possible to open it every time and count and skip rows.
Any help would be appreciated.
Thanks a lot in advance.
|
|
|
|
|
|
| Re: Control file to search and skip rows [message #349228 is a reply to message #349210] |
Fri, 19 September 2008 06:51  |
joebunny
Messages: 18
Registered: September 2008
|
Junior Member |
|
|
Hi Michel,
I saw the when clause but I guess it is ued for comparing the value of a column while loading the data,whereas what I am looking for is that I want to find out the occurence of the name of the first column in the text file and start uploading data from the subsequent rows.
In my case I want to skip all the rows that appear before the occurrence of the name of the first column but the number of rows that occur before my first column appears are variable ,otherwise I could have simply mentioned rows to skip in my control file.
Thanks and Regards.
[Updated on: Fri, 19 September 2008 06:53] Report message to a moderator |
|
|
|
| Re: Control file to search and skip rows [message #349274 is a reply to message #349228] |
Fri, 19 September 2008 10:39 |
 |
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Ideally it would be best to obtain a text file without the query that produced the data included. If the data was spooled from an Oracle source, then this can be accomplished by setting echo off before spooling. Similar things can be done in other languages. If you have no control over this and are stuck with it, then you need to identify something that can be used in the WHEN clause, so that Oracle can determine if this is a row to be loaded or not. For example if each line of your query begins with SELECT or FROM or WHERE or AND and so on and no such word would be valid as the first word of the first column of valid data, then you might be able to use something like "WHEN first_col <> 'SELECT' AND first_col <> 'FROM' ...". If any such rows would not meet the criteria for loading, then they would get rejected anyhow. If you still don't understand, then please post a sample data file and sample control file, and create table statement for the table that you want to load into.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
