Home » Infrastructure » Windows » OPTIONALLY ENCLOSED BY (Oracle 10g)
OPTIONALLY ENCLOSED BY [message #565835] Fri, 07 September 2012 03:48 Go to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Hi,
I'm having problem creating dynamic CTL File, Im using echo to create a CTL file, but it is not reading the syntax OPTIONALLY ENCLOSED BY '"'. Here's my code:
@echo off
for %%g in (C:\test\*.csv) Do (
echo Load Data> kf_nonair.ctl
echo INFILE '%%g'>> kf_nonair.ctl
echo append into table sample_table  >> kf_nonair.ctl
echo FIELDS TERMINATED BY ","   		>> kf_nonair.ctl
echo OPTIONALLY ENCLOSED BY '"'                 >> kf_nonair.ctl
echo ( membership_num              		>> kf_nonair.ctl
echo  , last_name   				>> kf_nonair.ctl
echo  , first_name       			>> kf_nonair.ctl
echo  , award_date				>> kf_nonair.ctl
echo  , miles_awarded "REPLACE(REPLACE(:miles_awarded,',',''),'.','')"	>> kf_nonair.ctl
echo  , award_desc				>> kf_nonair.ctl
echo  , ref_code   				>> kf_nonair.ctl
echo  , part_code				>> kf_nonair.ctl
echo  , trans_date SYSDATE			>> kf_nonair.ctl
echo  , file_name CONSTANT '%%~nxg'		>> kf_nonair.ctl
echo  ^)                                        >> kf_nonair.ctl
call kfnonair.bat
)


it was able to create CTL file but only this:
Load Data
INFILE 'C:\test\Sample Data.csv'
append into table sample_table  
FIELDS TERMINATED BY ","   				
( membership_num              				
 , last_name   							
 , first_name       						
 , award_date								
 , miles_awarded "REPLACE(REPLACE(:miles_awarded,',',''),'.','')"	
 , award_desc								
 , ref_code   								
 , part_code								
 , trans_date SYSDATE						
 , file_name CONSTANT 'Sample Data.csv'				
 )                                        


notice that OPTIONALLY ENCLOSED BY '"' was not included. And I need it because the input file has this values:
11111,LAST_NAME,NAME,07.05.2012,"4,000",KF,PPP,CCCCC,


hope you can help me with the syntax. thanks!
Re: OPTIONALLY ENCLOSED BY [message #565836 is a reply to message #565835] Fri, 07 September 2012 03:54 Go to previous messageGo to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Also, the log show error:
Record 1: Rejected - Error on table sample_table, column MILES_AWARDED.
ORA-01722: invalid number
Re: OPTIONALLY ENCLOSED BY [message #565839 is a reply to message #565836] Fri, 07 September 2012 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58532
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can't see any reason why it is not generated.
Anyway, this has nothing to do with Oracle, you should post your question in a Windows forum to know why there is not this line in your file.

The first I'd search if there is any unprintable character on the line.

Regards
Michel

[Edit: english]

[Updated on: Sun, 09 September 2012 23:44]

Report message to a moderator

Re: OPTIONALLY ENCLOSED BY [message #565945 is a reply to message #565839] Sun, 09 September 2012 23:05 Go to previous message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Hi Michel,
thanks for the reply! appreciate it. will ask to Windows forum too.
Previous Topic: ORA-29270: too many open HTTP requests
Next Topic: Taking perriodical backup of the database server
Goto Forum:
  


Current Time: Thu Jul 24 11:16:01 CDT 2014

Total time taken to generate the page: 0.35859 seconds