This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Able to create external table to query csv files as a table...

Gbenga Ajakaye
Gbenga Ajakaye IT ConsultantMember Posts: 3,422 Gold Trophy
edited Sep 12, 2016 4:18PM in Database Ideas - Ideas

I think it would be nice to be able to create a external that points to a .csv that I can then query like a regular table.

Each csv columns will represent a table column.

Gbenga AjakayeBPeaslandDBA
4 votes

Active · Last Updated

«1

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,317 Gold Crown

    This is what External Tables were designed to do.

    I believe that you can use SQL*Developer to create the necessary parameters.

    Now, if the CSV was a CLOB (or BLOB) in a table (eg APEX "File Browse..." Item), then you'll want to up-vote this:

    Other than that, you will need to explain what you want with more details.

    MK

  • Gbenga Ajakaye
    Gbenga Ajakaye IT Consultant Member Posts: 3,422 Gold Trophy

    This is what External Tables were designed to do.

    I believe that you can use SQL*Developer to create the necessary parameters.

    Now, if the CSV was a CLOB (or BLOB) in a table (eg APEX "File Browse..." Item), then you'll want to up-vote this:

    Other than that, you will need to explain what you want with more details.

    MK

    Thanks Mike. I want to be able to read the data in a .CSV without loading the data. I can see how your idea could come in handy, but it's different from what I'm looking for.

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    I'm going to vote Up. The reason is because this has already been implemented...many many versions ago. Here is the Oracle documentation on the subject:

    External Tables

    Either that, or I missed the point entirely.

    Now querying a .xlsx file directly from an external table...now there's an idea!

    Cheers,
    Brian

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    Thanks Mike. I want to be able to read the data in a .CSV without loading the data. I can see how your idea could come in handy, but it's different from what I'm looking for.

    External Tables do not load the data. They do use the SQL*Loader engine to read from the .csv file, but data is not loaded to the database.

  • Gbenga Ajakaye
    Gbenga Ajakaye IT Consultant Member Posts: 3,422 Gold Trophy

    External Tables do not load the data. They do use the SQL*Loader engine to read from the .csv file, but data is not loaded to the database.

    I know that @BPeaslandDBA.

  • Mike Kutz
    Mike Kutz Member Posts: 6,317 Gold Crown

    I'm going to vote Up. The reason is because this has already been implemented...many many versions ago. Here is the Oracle documentation on the subject:

    External Tables

    Either that, or I missed the point entirely.

    Now querying a .xlsx file directly from an external table...now there's an idea!

    Cheers,
    Brian

    BPeaslandDBA wrote:Now querying a .xlsx file directly from an external table...now there's an idea!Cheers,
    Brian

    Hmmm... can you pass a BFILE to the XMLType() that is part of an XMLTable() ???

    MK

  • Gbenga Ajakaye
    Gbenga Ajakaye IT Consultant Member Posts: 3,422 Gold Trophy

    I'm going to vote Up. The reason is because this has already been implemented...many many versions ago. Here is the Oracle documentation on the subject:

    External Tables

    Either that, or I missed the point entirely.

    Now querying a .xlsx file directly from an external table...now there's an idea!

    Cheers,
    Brian

    I'll like to see that @BPeaslandDBA. Though .xlsx can be rather problematic to query. It's not as straightforward.

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    .xlsx is a proprietary format.     Not only could it change in the next release of Excel, but it can contain objects other than just text.

    How would it copy with embedded objects, for example?

  • William Robertson
    William Robertson London, UKMember Posts: 9,579 Bronze Crown

    I know that @BPeaslandDBA.

    BCG14 wrote:I know that BPeaslandDBA. 

    Then why suggest something that you know already exists?

  • Gbenga Ajakaye
    Gbenga Ajakaye IT Consultant Member Posts: 3,422 Gold Trophy
    BCG14 wrote:I know that BPeaslandDBA. 

    Then why suggest something that you know already exists?

    My reply was that I know that external table is meant to ready data.