SQL Query Using External Table

SQL Query For Retrieving Data From Excel

Topic Introduction: Sometimes we need to show data from Excel or CSV files. which data is not stored or our database table. Today we will show SQL Query for Retrieving Data From Excel Using an external table. Or how can we read an Excel or CSV file by SQL Query?

Steps need to follow to retrieve data from an external file

1. Selected CSV or Excel files are kept in your computer Directory.
2. Create a Directory
3. Grant Directory to User
4. Create a table for external file
5. Query Execute for Check Data

CREATE DIRECTORY LANG_EXTERNAL AS 'D:\Loader';

 

GRANT READ, WRITE ON DIRECTORY LANG_EXTERNAL TO HR;

CREATE TABLE LANGUAGES(

    LANGUAGE_ID INT,

    LANGUAGE_NAME VARCHAR2(30)

)

ORGANIZATION EXTERNAL(

    TYPE ORACLE_LOADER

    DEFAULT DIRECTORY LANG_EXTERNAL

    ACCESS PARAMETERS 

    (FIELDS TERMINATED BY ',')

    LOCATION ('languages.csv')

);

SQL Query to get data from Excel.

SELECT LANGUAGE_ID, LANGUAGE_NAME 

FROM LANGUAGES

ORDER BY LANGUAGE_NAME;

                                                             CSV File Data Format

SQL Query From Excel File