Home » Infrastructure » Windows » SQL Load Multiple files to Single table (11)
SQL Load Multiple files to Single table [message #677063] Wed, 14 August 2019 11:06 Go to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi All,

Below is my control file and I'm trying to load multiple csv files in to single table.

OPTIONS (SKIP=1)
LOAD DATA  
CHARACTERSET WE8ISO8859P1
--infile '/home/applmgr/harish/Ringgold_Files/PROQ_*.csv'
--BADFILE '/home/applmgr/harish/Ringgold_Files/rg.bad'
--DISCARDFILE '/home/applmgr/harish/Ringgold_Files/rg.ds'
APPEND INTO TABLE XXPQ_RG_SUBS_FULL                                   
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS                                                 
(                  
   RG_NUMBER                        DECIMAL EXTERNAL NULLIF (RG_NUMBER=BLANKS)  
 , RG_NAME                          CHAR NULLIF (RG_NAME=BLANKS)     
 , PARENT_COUNTRY                   CHAR NULLIF (PARENT_COUNTRY=BLANKS)         
 , PARENT_TIER                      CHAR NULLIF (PARENT_TIER=BLANKS) 
 , TOP_LEVEL_RIN                    DECIMAL EXTERNAL NULLIF (TOP_LEVEL_RIN=BLANKS) 
 , TOP_LEVEL_NAME                   CHAR NULLIF (TOP_LEVEL_NAME=BLANKS)    
 , TOP_PARENT_COUNTRY               CHAR NULLIF (TOP_PARENT_COUNTRY=BLANKS)         
 , SUBSCRIBER_NUMBER                CHAR NULLIF (SUBSCRIBER_NUMBER=BLANKS)      
 , SUBSCRIBER_NAME                  CHAR NULLIF (SUBSCRIBER_NAME=BLANKS)   
 , CREATED_BY                       CONSTANT '1' 
 , CREATED_DATE                     SYSDATE        
 , LAST_UPDATED_BY                  CONSTANT '1'     
 , LAST_UPDATE_DATE                 SYSDATE  
)


Below is the batch file

for %f in /home/applmgr/harish/Ringgold_Files/*.csv do \
sqlldr xxx/xxx@ebsua' DATA=%f \
CONTROL=xxpq_rg_subs.ctl LOG=/home/applmgr/harish/Ringgold_Files/rg.log  \
BAD=/home/applmgr/harish/Ringgold_Files/rg.bad skip=1 

I keep encountering below error.COuld you please suggest what change needs to be done in the batch file.

[applmgr@ebs203 Ringgold_Files]$ rg.bat
./rg.bat: line 5: syntax error: unexpected end of file

[Updated on: Wed, 14 August 2019 11:10]

Report message to a moderator

Re: SQL Load Multiple files to Single table [message #677064 is a reply to message #677063] Wed, 14 August 2019 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As you NEVER fedback and thanked people for their time and help in your previous topics, you envisage to have no more help now.

Re: SQL Load Multiple files to Single table [message #677066 is a reply to message #677063] Wed, 14 August 2019 13:23 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
vharish006 wrote on Wed, 14 August 2019 11:06
Hi All,

Below is my control file and I'm trying to load multiple csv files in to single table.

OPTIONS (SKIP=1)
LOAD DATA  
CHARACTERSET WE8ISO8859P1
--infile '/home/applmgr/harish/Ringgold_Files/PROQ_*.csv'
--BADFILE '/home/applmgr/harish/Ringgold_Files/rg.bad'
--DISCARDFILE '/home/applmgr/harish/Ringgold_Files/rg.ds'
APPEND INTO TABLE XXPQ_RG_SUBS_FULL                                   
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS                                                 
(                  
   RG_NUMBER                        DECIMAL EXTERNAL NULLIF (RG_NUMBER=BLANKS)  
 , RG_NAME                          CHAR NULLIF (RG_NAME=BLANKS)     
 , PARENT_COUNTRY                   CHAR NULLIF (PARENT_COUNTRY=BLANKS)         
 , PARENT_TIER                      CHAR NULLIF (PARENT_TIER=BLANKS) 
 , TOP_LEVEL_RIN                    DECIMAL EXTERNAL NULLIF (TOP_LEVEL_RIN=BLANKS) 
 , TOP_LEVEL_NAME                   CHAR NULLIF (TOP_LEVEL_NAME=BLANKS)    
 , TOP_PARENT_COUNTRY               CHAR NULLIF (TOP_PARENT_COUNTRY=BLANKS)         
 , SUBSCRIBER_NUMBER                CHAR NULLIF (SUBSCRIBER_NUMBER=BLANKS)      
 , SUBSCRIBER_NAME                  CHAR NULLIF (SUBSCRIBER_NAME=BLANKS)   
 , CREATED_BY                       CONSTANT '1' 
 , CREATED_DATE                     SYSDATE        
 , LAST_UPDATED_BY                  CONSTANT '1'     
 , LAST_UPDATE_DATE                 SYSDATE  
)


Below is the batch file

for %f in /home/applmgr/harish/Ringgold_Files/*.csv do \
sqlldr xxx/xxx@ebsua' DATA=%f \
CONTROL=xxpq_rg_subs.ctl LOG=/home/applmgr/harish/Ringgold_Files/rg.log  \
BAD=/home/applmgr/harish/Ringgold_Files/rg.bad skip=1 

I keep encountering below error.COuld you please suggest what change needs to be done in the batch file.

[applmgr@ebs203 Ringgold_Files]$ rg.bat
./rg.bat: line 5: syntax error: unexpected end of file
Odd that you would name your executable '.bat' (normally thought of as a Windblows construct) in what is obviously a *nix environment.

That said, your shell processor is reporting an error on line 5, but you only show 4 lines of script. Also, since the error is being reported by the shell processor as a problem with the shell (bat) script, the sqlldr control file plays no part.

What is that single quote doing at the end of "sqlldr xxx/xxx@ebsua" ? the shell processor probably got to the end of the file while still looking for the closing quote.
Re: SQL Load Multiple files to Single table [message #677071 is a reply to message #677063] Thu, 15 August 2019 10:50 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Also posted at https://community.oracle.com/thread/4285928
Re: SQL Load Multiple files to Single table [message #677072 is a reply to message #677071] Thu, 15 August 2019 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And OP didn't bother to post the solution here... /forum/fa/4568/0/



Re: SQL Load Multiple files to Single table [message #677073 is a reply to message #677071] Thu, 15 August 2019 11:22 Go to previous messageGo to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi Stevens,

Yes I Did post in other Community forum as well and got a solution. Thanks all for your help.
Re: SQL Load Multiple files to Single table [message #677074 is a reply to message #677073] Thu, 15 August 2019 12:35 Go to previous message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why didn't you post the solution here?
And why didn't you feedback in your previous topics?

Previous Topic: Export .dmp files to a LAN location?
Next Topic: Network permissions when using Windows virtual account
Goto Forum:
  


Current Time: Thu Dec 12 20:28:28 CST 2019