Re: Reading from an Excel table into an Oracle table or list to use in a query
Date: Mon, 3 Mar 2014 23:28:19 -0300
Message-ID: <CAJ2dSGRBHYhuXw=p7SJ63EkNkT+HUb2D9aFBsBAYO+p+wcjJRQ_at_mail.gmail.com>
short answer? you can't, at least not easily. longish answer, of course you can :-P the question is how.
You could create a CSV off the excel file and then use it as an external
table. This requires DBA intervention.
You can create a CSV and then use sql*loader (which is included in the
standard client and has an instant client version). This requires no DBA
privileges, but does require that there's a table where the data is going
to be loaded.
You could also deploy (could you?) APEX, which has a facility to load a
table from an excel file. But this requires privileged access to the
database.
You could potentially use the excel function "CONCATENATE" to generate the
insert statements and then run that script from SQL*Plus/Developer. This is
by no means performant, but it's easy to do, especially if there is a
single table involved.
I suggest you add some indexes afterwards to speed up your queries. Basically, index the columns through which you will filter in the where clause.
a quick google search returned this. I have not tested it, but it looks plausible.
http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/
I hope this helps.
Alan.-
On Mon, Mar 3, 2014 at 8:27 PM, William Threlfall < William.Threlfall_at_albertahealthservices.ca> wrote:
> *Hi experts,*
>
> *I just spent 2 hours searching online for a way to do this, without
> success. Why is this difficult? This was simple to do in FoxPro!*
>
> *Anyway, I have been given an Excel f**ile containing a list of patient
> ID**'**s, and need to extract certain lab results for those specific
> patients.*
> *The obvious way to do this would be to read the patient id**'**s into a
> list **structure **(call it PATLIST) **and then **in the query use **WHERE
> **patient_info.pat_id** IN PATLIST* *to filter the results.**
> Alternatively, I can possibly create a table calle**d PATLIST and use
> WHERE patient_info.pat_id = PATLIST.pat_id to filter the results.*
> *However, I am stuck because I can**'**t find any information on how to
> read an Excel file into an Oracle table or list structure**.*
> *I can export the Excel file into a CSV text file if necessary, but **I**
> can**'**t find a way to read a text file into an Oracle table or list
> structure either.*
>
> *Will** someone please tell me how to do this **simply and **easily? *
> *I am using SQL Developer**, connecting remotely to the database**. I**
> don**'**t have access to **do anything on **the server, and **I **don**'**t
> have any ability to do anything **that would require DBA or SysAdmin
> access.*
>
> *Cheers, - Bill.*
>
> *_______________________________________*
> *Willliam J. (Bill) Threlfall, MSc*
> Clinical Informatics Coordinator - OTTR
> Transplant Services, University of Alberta Hospital
> Alberta Health Services
> Aberhart Centre, Room 9221
> 8440 112 Street
> Edmonton, AB T6G 2B7
>
> Telephone: 780-407-6175 FAX: 780-407-8981
>
> *Alberta Health Services*
> *www.albertahealhservices.ca* <http://www.albertahealhservices.ca>
>
>
>
>
>
> * ________________________________ *
> This message and any attached documents are only for the use of the
> intended recipient(s), are confidential and may contain privileged
> information. Any unauthorized review, use, retransmission, or other
> disclosure is strictly prohibited. If you have received this message in
> error, please notify the sender immediately, and then delete the original
> message. Thank you.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 04 2014 - 03:28:19 CET