Re: Reading from an Excel table into an Oracle table or list to use in a query
From: <Ronan.Merrick_at_cso.ie>
Date: Tue, 4 Mar 2014 09:57:08 +0000
Message-ID: <OF5F14D27A.A8CB0405-ON80257C91.00361387-80257C91.0036ACA5_at_cso.ie>
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 file 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 called 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
________________________________
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.
Date: Tue, 4 Mar 2014 09:57:08 +0000
Message-ID: <OF5F14D27A.A8CB0405-ON80257C91.00361387-80257C91.0036ACA5_at_cso.ie>
Hi,
+1 for SQL loader if you don't have access to a directory object on the server.
Somebody else suggested insert statements.
Depending on how many rows are in the file, you could construct the SQL in extra columns in the first row and then concatenate it all together in a final column. Then autofill all the rows and copy out the insert statements from your concatenated column.
SQL Loader would be a lot faster though.
Ronan Merrick
Oracle DBA
Census IT
Ph: (+3531) 8951309
William Threlfall <William.Threlfall@albertahealthservices.ca>
Sent by: oracle-l-bounce_at_freelists.org 03/03/2014 23:28
|
|
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 file 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 called 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
________________________________
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.
Subject to the disclaimer at http://www.cso.ie/en/emaildisclaimer/
Faoi réir ag an séanadh ag http://www.cso.ie/en/seanadhriomhphoist/
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 04 2014 - 10:57:08 CET
