Home » SQL & PL/SQL » SQL & PL/SQL » Validate CSV before Staging Table
icon4.gif  Validate CSV before Staging Table [message #227453] Wed, 28 March 2007 05:19 Go to next message
sheeba28
Messages: 4
Registered: September 2005
Location: Bangalore
Junior Member

Hi All,
Can anyone tell me how I can validate the CSV file before loading into Temporary/Staging Table.

The scenario is like this:

1. I get the flat file (.csv)
2. Do the formatting that the file should contains only 4 columns.
3. If fails write to the BAD file and email back this format is not supported.
4. If the file contains 4 columns, then proceed with null column, reserved key word check.
5. Then load to staging table.

Any help,,, please

Thanks in Advance,
Sheeba

Re: Validate CSV before Staging Table [message #227539 is a reply to message #227453] Wed, 28 March 2007 09:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This needs a few OS scripting to be involved.
I prefer shell/perl/awk and have very less knowledge with Windows scripting.
Post your OS information,create table statement, some sample data
Re: Validate CSV before Staging Table [message #227603 is a reply to message #227539] Wed, 28 March 2007 15:04 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
See these links.
http://www.akadia.com/services/ora_important_2000.html (search for example1.dat)
http://hajek.stat.ubc.ca/~harry/local/unixutil.html

Use awk to check the number of columns and then just make sure you get the number you are looking for...
dev>>cat cols.txt                           
COL1 COL2 COL3
col1 col2
Col1 Col2 Col3 Col4
col-1 col-2 col-3
dev>>awk '{print NF}' cols.txt              
3
2
4
3
dev>>awk '{print NF}' cols.txt | sort | uniq
2
3
4
dev>>
Re: Validate CSV before Staging Table [message #230909 is a reply to message #227453] Fri, 13 April 2007 05:21 Go to previous message
chetanojha
Messages: 2
Registered: June 2006
Location: Newcastle
Junior Member
You can also do this by external table.
Simply import the columns which you want and ignore the rest.
Previous Topic: download a pdf file from long raw
Next Topic: ORA error with object no longer exists
Goto Forum:
  


Current Time: Wed Dec 07 16:19:35 CST 2016

Total time taken to generate the page: 0.29459 seconds