Oracle data load - extract data from excel sheet.

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?
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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.
1
anumosesAuthor Commented:
Yes I need help in formatting the complete code. I am missing " and , . Help is appreciated.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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.
0
Determine the Perfect Price for Your IT Services

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

anumosesAuthor 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

0
slightwv (䄆 Netminder) 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"?
0
anumosesAuthor 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.
0
slightwv (䄆 Netminder) 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.
0
anumosesAuthor Commented:
=CONCATENATE("SELECT ","'",A2,"' ac_reg, ","",B2," pc, ","'",C2,"' ind, ",F2," FROM DUAL UNION")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.