RE: Reading from an Excel table into an Oracle table or list to use in a query

From: William Threlfall <William.Threlfall_at_albertahealthservices.ca>
Date: Wed, 12 Mar 2014 11:23:33 -0600
Message-ID: <36800EC4761690448F1B444A1AEF44BB6D2EBCA6_at_EXMBXC5.crha.bewell.ca>



Thanks for everyone's input on this, esp. Jeff. Maybe I will be able to make use of that method sometime.

It turned out that (a) I don't have permission to create a table in the database and (b) The DBA said they "don't" create user tables in the database (probably because they don't want to have a bunch of user tables cluttering up the database and have to deal with them during upgrades/refreshes).

So the only way I could do this quickly was to cut-and-paste the patient ID's from Excel into Notepad, then cut-and-paste into Word, then replace all the linefeeds (paragraph marks) with commas, then cut-and-paste into my query in SQL Developer and use WHERE PAT_ID IN (<long list of 342 patient ID's>).

It is clumsy and inefficient, but it worked.

Cheers, - Bill.

From: Jeff Smith [mailto:jeff.d.smith_at_oracle.com] Sent: Monday, March 03, 2014 9:13 PM
To: William Threlfall; Oracle-L_at_freelists.org Subject: RE: Reading from an Excel table into an Oracle table or list to use in a query

For SQL Developer, it's pretty straightforward http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/

Right click on a table, Import Data. Answer the questions. We'll suck the data in.

If it's a LOT of data, or if you're going to be doing this frequently, going the way of external tables or SQL*Loader is probably the way to go.

Jeff



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-l
Received on Wed Mar 12 2014 - 18:23:33 CET

Original text of this message