SharonBernal
asked on
Oracle Sql Loader / PLSQL Data Validation
I receive a pipe delimited file that I load using SQL Loader. I have been asked to verify the pipe count in the file as a means to validate that all columns are accounted for.
Example: The file should be
col1|col2|col3|col4|col5
If I get a file like this
col1|col2|col3
it should not accept the file.
The pipe count should be five not two for each row.
My questions is two fold:
1. Will SQL Loader bomb on a file that does not have all of the columns defined, I'm receiving a variable length file.
2. Is there a way to do this with a stored procedure and can you give me ideas? I'm thinking of using UTL_File or should I load file in one big column and count the | in each row. Usually these files are large and I'm not sure about an approach.
Thanks.
Example: The file should be
col1|col2|col3|col4|col5
If I get a file like this
col1|col2|col3
it should not accept the file.
The pipe count should be five not two for each row.
My questions is two fold:
1. Will SQL Loader bomb on a file that does not have all of the columns defined, I'm receiving a variable length file.
2. Is there a way to do this with a stored procedure and can you give me ideas? I'm thinking of using UTL_File or should I load file in one big column and count the | in each row. Usually these files are large and I'm not sure about an approach.
Thanks.
awking00
On what kind of file system do these files reside (e.g. Windows, Unix, etc.)?
on a windows server use this scripts
and providing the records are 1 line based
it should give a list of the incorrect lines preceded by a line number
doing a line count on the file gives the count
and providing the records are 1 line based
findstr /V /N ".*|.*|.*|.*|.*" c:\data\your_input_file.txt > incorrect_lines.txtit should give a list of the incorrect lines preceded by a line number
doing a line count on the file gives the count
ASKER
It's a Unix system.
don't have unix but i'm guessing grep can do that
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Or business rule would be to ignore the entire file. This is what I needed to know. Thank you.