Oracle data load - extract data from excel sheet.

anumoses
anumoses used Ask the Experts™
on
AIRCRAFT_REG      PCDTM_CODE      EXCHANGE_INDICATOR      STARTDATE
N158AN                       102                           N                                     20-Mar-18
N157AA                       102                           N                                     20-Mar-18
N165NN                       102                           N                                     20-Mar-18

Doing script for dataload

Select 'N158AN' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-177) STARTDATE  from dual union
Select  'N157AA' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-177) STARTDATE  from dual union
Select  'N165NN' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-177) STARTDATE  from dual union

=CONCATENATE("Select A2,"' ac_reg, ", B2, pcdtm_code,'N' ind,trunc(sysdate-177) stdt  from dual union")

I am getting error. Can someone hep me to fix this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
It is ALL about the quotes...
=CONCATENATE("Select '", A2,"' ac_reg, ", B2," pcdtm_code,'N' ind,trunc(sysdate-177) stdt  from dual union")

Personally, I would save the Excel file as a CSV and use SQL*Loader to load it into Oracle.

Author

Commented:
Yes I need help in formatting the complete code. I am missing " and , . Help is appreciated.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
I agree with slightwv, or I occasionally manually generate insert statements within the spreadsheet, paste into a document, remove tabs and run, if the amount of data is tiny.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes its excel. My work does not do sql loader. We use this kind of code to get in the script, Please see attached.

prompt Loading Tail records ...
SET serveroutput ON SIZE 1000000
DECLARE
   
CURSOR c1 
   IS
Select 'A321' profs_id, 'N158AN' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-82) STARTDATE  from dual union
Select 'A321' profs_id, 'N157AA' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-82) STARTDATE  from dual union
Select 'A321' profs_id, 'N165NN' AIRCRAFT_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-82) STARTDATE  from dual;
v_records_processed number;
v_profs_id          number;

Begin
  
v_records_processed := 0;

FOR c1rec IN c1
    LOOP
     
     Begin
      select profs.id 
        into v_profs_id
        from dss.profiles profs 
       where profs.profile = c1rec.PROFS_ID
        and profile_type = 'AC'; 
          
          exception when no_data_found then
            dbms_output.put_line('No Profile: ' ||  c1rec.AIRCRAFT_REG);
              v_profs_id := null; 
     End;
       
            Begin 
             insert into dss.agreement_term_tails 
                       (AGTERM_ID
                       ,PROFS_ID_AIRCRAFT
                       ,AIRCRAFT_REG
                       ,PCDTM_CODE
                       ,EXCHANGE_INDICATOR
                       ,STARTDATE
                       ,PCDTM_DIV_NO)
                values(  2795
                       , v_profs_id
                       , c1rec.AIRCRAFT_REG    
                       , c1rec.PCDTM_CODE
                       , c1rec.EXCHANGE_INDICATOR
                       , c1rec.STARTDATE
                       , 7);    
        
            End;
               v_records_processed := v_records_processed + 1;
          END LOOP;
 
    dbms_output.put_line('# of rows processed: '|| TO_CHAR(v_records_processed));

                       IF SQL%NOTFOUND
                        THEN
                           DBMS_OUTPUT.PUT_LINE('No Records Processed');
                       END IF;
   COMMIT;
End; 
/ 

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Take a look at what I posted.

What version of Oracle are you using?

On a side comment:  Just because they aren't using SQL*Loader doesn't mean they shouldn't.  Is there some policy stating you cannot or is it the "we've just never done it that way"?

Author

Commented:
=CONCATENATE("SELECT ","'",A2,"' ac_reg, ","",B2," pc, ","'",C2,"' ind, ",F2," FROM DUAL UNION")

This is what I expected. I am not sure what I need or how to close this question. None of the answered helped me.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
The concatenate you ended up with is nothing like the original one you provided.

The one I posted is more or less the same thing you ended up with.  You just changed what you were looking for.  

The actual answer is:  You just needed to clean up the quotes so you had valid syntax for CONCATENATE.

We had no way to know the exact statement.  We had to go by what you provided.
Commented:
=CONCATENATE("SELECT ","'",A2,"' ac_reg, ","",B2," pc, ","'",C2,"' ind, ",F2," FROM DUAL UNION")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial