This discussion is archived
6 Replies Latest reply: Oct 8, 2012 5:26 AM by 6363 RSS

How to generate insert scripts by using excel data through Oracle procedure

kolipaka Newbie
Currently Being Moderated
Hi Guys,

I have one excel sheet file with some records. I need to generate insert scripts using this excel file data. Is this possible through Oracle procedure?

Can any one give idea on this?

Thanks in advance!

Regards,
KLR
  • 1. Re: How to generate insert scripts by using excel data through Oracle procedure
    Mr Lonely Newbie
    Currently Being Moderated
    May be saving it as csv and then loading the data using SqlLoader!
  • 2. Re: How to generate insert scripts by using excel data through Oracle procedure
    kolipaka Newbie
    Currently Being Moderated
    No...my requirement is generating insert scripts using excel data. Later we will execute them once validate.
  • 3. Re: How to generate insert scripts by using excel data through Oracle procedure
    jeneesh Guru
    Currently Being Moderated
    kolipaka wrote:
    No...my requirement is generating insert scripts using excel data. Later we will execute them once validate.
    You can do it through macros in excel..

    Or by using CONCATENATE function in excel..`
  • 4. Re: How to generate insert scripts by using excel data through Oracle procedure
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    hi kolipaka,

    in this case may have two options...

    1) you can upload the data into temp table by using sql loader. first you need to convert it into the csv file by opening it in excel and save as csv. After uploading data into temp table then you can use procedure for updating existing data or inserting data from temp table to original table.

    then you can create sql loader control file as below.....
    options (skip=3)
    load data 
    infile 'your_csv_file_path'
    APPEND 
    into table mis_crm_daily_pending
    fields terminated by ','
    optionally enclosed by '"'
    trailing nullcols
    ( your_column list)
    {code}
    
    and then on cammand prompt :=
    
    go to the fath of the folder in which your control file is resids.
    {code}
    sqllder userid=your userid@database_name control=your_control_file_name
    password**********;
    {code}
    
    
    Or 
    
    If you really need to have insert statment then you can use CONCATENATE Option of Excel liek 
    =concatenete(insert into ************************)
    
    Edited by: Chanchal Wankhade on Oct 7, 2012 11:06 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 5. Re: How to generate insert scripts by using excel data through Oracle procedure
    EdStevens Guru
    Currently Being Moderated
    kolipaka wrote:
    No...my requirement is generating insert scripts using excel data. Later we will execute them once validate.
    No, your requirement is to get data from point A to point B, where point A appears to be an excel file. "generating insert scripts using excel data " is just one pre-conceived method of meeting the requirement.

    Please describe your business requirement, and why you believe "generating insert scripts using excel data " is the best way to meet that requirement.
  • 6. Re: How to generate insert scripts by using excel data through Oracle procedure
    6363 Guru
    Currently Being Moderated
    kolipaka wrote:

    No...my requirement is generating insert scripts using excel data. Later we will execute them once validate.
    Why?

    Is the requirement to load the data as slowly as possible and slow everything else in the database too?

    Because you wouldn't be using bind variables would you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points