[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1603
  • Last Modified:

Insert data into a temp table from file

Hi,
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?

Thanks

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

0
cutie_smily
Asked:
cutie_smily
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Om PrakashCommented:
0
 
cutie_smilyAuthor Commented:
Thank! I read that post before posting. As I said I do not have access to UNIX.
0
 
sdstuberCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
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 otn.oracle.com.
0
 
ajexpertCommented:
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?
0
 
cutie_smilyAuthor Commented:
yes I do have PL/SQL developer. Please let me know how to do it.
0
 
ajexpertCommented:
0
 
sdstuberCommented:
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

done
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now