Oracle Apex Upload Shpreadsheet to Import Data
How to import Excel file information to a table using SQL / PL SQL command
Team,
Tin you please allow me know how to import the Excel file data using SQL / PL SQL command.
Thank you!
Kiran
Answers
-
mathguy Member Posts: ten,405 Blueish Diamond
Why "using SQL / PL SQL command"? Meliorate to utilize a front-end (user interface) that has import from Excel built in. SQL Developer is free and yous can use it to automate the job; don't reinvent the bicycle.
-
Thanks for your reply.
Yes I have the feature to import and Export in Toad for Oracle (11.6). Which is manual procedure.
The reason I asked is I am trying to come across if we can automate the lawmaking as below using PL SQL Code. If we cannot do it, that'southward fine. Only posted this question if nosotros have an option / acquire from the experts.
1) Creating a Table using Create Control
2) Import the data from Excel from a specific location
3) The new table created in footstep ane need to join with some other tabular array
4) Export the Data to an Excel file.
-
Read up on external tables.
-
-
2817738 wrote:Thanks for your reply.Yeah I take the feature to import and Export in Toad for Oracle (11.6). Which is manual process.The reason I asked is I am trying to see if we tin automate the lawmaking as below using PL SQL Code. If we cannot practise information technology, that's fine. Just posted this question if we have an option / learn from the experts.1) Creating a Tabular array using Create Command2) Import the data from Excel from a specific location3) The new table created in step 1 need to join with another table4) Export the Information to an Excel file.
i. Issuing any DDL from a pl/sql procedure is a red flag. Creating tables should be part of pattern and implementation, not an on-going application performance.
Without knowing whatsoever more details, it sounds like you might exist meliorate off by writing a macro for your Excel, to connect to the database (ODBC) and select from the 'some other tabular array', doing your joining and result all withing the macro. Perchance not, but worth considering. As it is, it sounds like your only tool is a hammer, so all your issues look like nails.
As mentioned by Cookiemonster, at least take a look at external tables.
-
Distressing I should accept been articulate and thank you for all your responses. I volition accept a look at External tables
I am basically looking for a SQL lawmaking which satisfies the below requirement. If we tin write the SQL Lawmaking to reach this then I no need to use my reporting tool Cognos.
1) Creating a Table using Create Command
ii) Import the data from Excel (Which will always be only i cavalcade) from a specific location
3) The new table created in step 1 need to join with some other table
four) Export the Data to an Excel file.
Thank you all again!
-
_Dylan_ Member Posts: 587 Bronze Bluecoat
Excel supports an XML file format. When I've needed to generate quick and dirty multi-spreadsheet workbooks, I've saved a template excel file as XML, practical some place-holder values, used PL/SQL to loop through my data and do replacements, and spit out the modified XML. This was also before I had more than exposure to the XML features of Oracle. Would be interesting to get back and wait at the XML Schema Definition and come across most writing a procedure that generates a workbook (for fun).
-
2817738 wrote:Sad I should have been clear and thank yous for all your responses. I will accept a expect at External tablesI am basically looking for a SQL code which satisfies the below requirement. If we can write the SQL Lawmaking to achieve this then I no need to use my reporting tool Cognos.1) Creating a Table using Create Command2) Import the data from Excel (Which will always be simply one column) from a specific location3) The new table created in step 1 demand to join with another table4) Export the Information to an Excel file.Thank you lot all once more!
No, even that is just a pre-conceived technical solution to some unknown (to us) business problem. When you lot ask the wrong question you get, at best, a sub-optimal solution.
Please describe the business trouble. Information technology appears that you have some data in an excel workbook, and some information in an oracle table. It appears that you have a need to perform a join on the two, then either ii) only study the result, or ii) load the result into another spreadsheet to permit the end user to perform some analysis.
Where does the data that starts in the spreadsheet (that you lot think yous desire to load in a 'temporary' tabular array) come up from? Where is it when you desire to employ it? Does it change from one iteration to the next?
-
L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,773 Silverish Crown
-
Paulzip Member Posts: viii,652 Blue Diamond
If it'south a CSV or Ascii fixed width Excel file or can be saved equally that, employ an External table.
If it's actual Excel file (.xlsx, .xlsm, .xlsb, .xls, .xml), or ODF spreadsheet file (.ods) then utilise Odie's superb ExcelTable. Nix even comes close it it in terms of speed and functionality.
Source: https://community.oracle.com/tech/developers/discussion/4273643/how-to-import-excel-file-data-to-a-table-using-sql-pl-sql-command
0 Response to "Oracle Apex Upload Shpreadsheet to Import Data"
Post a Comment