Insert data into a temp table from file

I have some records in excel and I want to load them into oracle temp table. My intention is to use this temp table as a look-up table to update some values in a table.

The values are in Excel so I can convert them to any delimited file.
1) I DO NOT have access to UNIX so I am not sure if SQL Loader is an option for me.
2) There are not more than 500 records. Is it a good idea to generate insert statements? If yes how?
3) Can we write a procedure to read the file and load into temp table?


Here is the some sample records.

7591XXX	 $1,302.74 	7591XXX	 $1,302.74 	7591XXX	 $1,061.69 
7590XXX	 $2,106.97 	7590XXX	 $2,106.97 	7590XXX	 $1,717.11 
7590XXX	 $936.01 	7590XXX	 $936.01 	7590XXX	 $762.82 
7590XXX	 $1,073.55 	7590XXX	 $1,073.55 	7590XXX	 $874.91 
7591XXX	 N/A 		7591XXX	 N/A 		7591XXX	 N/A

Open in new window

Who is Participating?
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.

Om PrakashCommented:
cutie_smilyAuthor Commented:
Thank! I read that post before posting. As I said I do not have access to UNIX.
if you know vba you can write a script to iterate through your rows.

if it's only a few hundred rows and it's a one time operation then generating insert statements is probably easiest.

add a new column A "insert into your_table (col1,col2,col3,col4) values ("

then add new columns between each of your other columns with a single comma ","  
if any data you are loading are strings then wrap them in single quotes
then put a semi colon on the end, copy it all to a test file and run it with sqlplus
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

also note, if your client has sql*loader you don't need to have access to the server, you can run the loader locally

if your client doesn't have it you can download the client from
If you have tools like PL/SQL developer or TOAD, this can be done very quickly.
Do you use any tools or SQL or PLSQL?
cutie_smilyAuthor Commented:
yes I do have PL/SQL developer. Please let me know how to do it.
in PL/SQL Developer

Tools -> ODBC Importer -> Data from ODBC tab

In User /System DSN choose "Excel Files"  no username or password then click connect
it will bring up a file dialog browse to find your excel file, under "Import Table" you'll see your worksheets, select which ever one has your data you should see data in the preview

then go to the  Data to Oracle tab
choose your owner and table.  i

f you need to create the table click on the "create table" icon at the top of the importer window it will create a table called "ODBC_IMPORT" by default and map the columns for you as best it can.  Adjust as necessary then go back to the Data to Oracle tab and select your new table

confirm the fields and columns are synched, if not, then change them.

click import


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.