Example of Loading external, publicly available CSV to a table in Oracle Autonomous Database

[lucaboesch]

Hi René I know that might be asked too much but I was wondering if you had an example on how to achieve to load an publicly available external CSV (think for example https://www.web.statistik.zh.ch/ogd/daten/ressourcen/KTZH_00002063_00004104.csv) into a table that is inside a Oracle Autonomous Database. Searching for hours. Seems well covered with tutorials when it's about Oracle cloud storage, Amazon S3, Azure Blob or GitHub, but not when it's about a simple CSV in a publicly available URL, strangely. Best, Luca

[ReneNyffenegger]

Hi Luca

This is something I haven't done in an Autonomous DB - so I cannot really help you. I guess, you'll have to first import the CSV into an object store and then use DBMS_CLOUD.COPY_DATA to import the CSV into the table. Since I might come across sucha requirement myself, I'd appreciate a quick feedback if you find a workable solution

Best / Rene

[lucaboesch]

Dear Rene, I have some kind of progress which I describe here https://stackoverflow.com/a/76151972/17733194 but it triggers a follow up question.

[lucaboesch]

Dear @ReneNyffenegger by the help of a generous supporter I've learned that

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'TEST_EXT',
    credential_name => null,
    file_uri_list =>'https://www.web.statistik.zh.ch/ogd/daten/ressourcen/KTZH_00002063_00004104.csv',
    format => json_object('type' value 'csv', 'delimiter' value ',', 'header' value true),
    column_list => 'JAHR VARCHAR2(200), DATUM VARCHAR2(200), GEMEINDE_NUMMER VARCHAR2(200), GEMEINDE_NAME VARCHAR2(200), BEZIRK_NUMMER VARCHAR2(200), BEZIRK_NAME VARCHAR2(200), VERTEILZONE VARCHAR2(200), ANALYT VARCHAR2(200), KATEGORIE VARCHAR2(200), KATEGORIE_SORTIERUNG     VARCHAR2(200), EINHEIT VARCHAR2(200), QUELLE VARCHAR2(200)' );
END;
/

works. Now I'm enquiring why my own CSV which looks like

"CHANNEL ID","CHANNEL DESC","CHANNEL CLASS"
"S","Direct Sales","Direct"

woudn't.

[ReneNyffenegger]

@lucaboesch, thanks for the feedback, it is greatly appreciated!

Is it possible that your CSV does not work because the column names (as per header) contain spaces?

Best Rene

[lucaboesch]

Wow, they were supposed to have underscores instead of whitespace. Interesting idea but no, even when replacing the spaces

"CHANNELID","CHANNELDESC","CHANNELCLASS"
"S","Direct Sales","Direct"

it wouldn't work.