Home » SQL & PL/SQL » SQL & PL/SQL » how to perform query based on data in excel file
how to perform query based on data in excel file [message #207264] Mon, 04 December 2006 21:36 Go to next message
niva2004
Messages: 15
Registered: October 2006
Location: Cochin
Junior Member
hi all

I have some emp_code in an excel file and i have to perform a select query
based on the emp_codes in the excel file from an Oracle 9i table.I have created a table emp as

EMP
-----
emp_code varchar2(6);

But how can i load data into this table from the excel file..
Is there any way other than sql loader.I found from the forum that we have to create a control file for that.But i couldnt understand how to create it and use it..please help me..
Re: how to perform query based on data in excel file [message #207283 is a reply to message #207264] Mon, 04 December 2006 23:13 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

you have two option.
1. external tables
2. sqlloader.


how can create control file.

on sql prompt.
sql>edit path_file_name.ctl hit<enter>

eg:
sql>edit c:\test.ctl hit<enter>

it will open notepad and you can type ur controlfile statement.

some you can create .bat file, .csv file etc.

For pratical example
1.external table
http://www.adp-gmbh.ch/ora/misc/ext_table.html

2.sqlloader
http://www.adp-gmbh.ch/ora/misc/ext_table.html



hope this helps
Taj.

[Updated on: Mon, 04 December 2006 23:14]

Report message to a moderator

Re: how to perform query based on data in excel file [message #207325 is a reply to message #207283] Tue, 05 December 2006 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair, there's a 3rd way if you've not got more than a few thousand rows.

Copy then out of Excel into a decent text editor, like Crimson or Textpad, and then use the Search/Replace function to replace "/n" (newline) with "');/nINSERT INTO EMP (emp_code) VALUES ('"

This will wrap all your data in insert statements, and you can then run this in SQL*Plus.
Re: how to perform query based on data in excel file [message #207368 is a reply to message #207264] Tue, 05 December 2006 04:45 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If this is going to be a recurring process, just create a database link to the Excel file.
Previous Topic: Alternative to avoid ORA-01795 Error
Next Topic: How to form this query
Goto Forum:
  


Current Time: Sat Dec 10 05:06:49 CST 2016

Total time taken to generate the page: 0.08459 seconds