Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:apex_datei_parser_laden

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
prog:apex_datei_parser_laden [2019/11/08 15:09] – [Bericht anlegen mit apex_data_parser.parse] gpipperrprog:apex_datei_parser_laden [2019/11/14 11:51] (aktuell) – [Daten laden mit apex_data_parser.parse] gpipperr
Zeile 1: Zeile 1:
 +===== Oracle APEX 19.2 - APEX_DATA_PARSER für das Laden von Excel und CSV Datein einsetzen =====
  
 +
 +<fc #800000>**Aufgabe:**</fc>
 +
 +Eine Datei, möglichst natives Excel, soll in eine Tabelle in der Datenbank geladen werden.
 +
 +Vor 19.1 war zwar schon ein Data Load Wizard in APEX implementiert, allerdings war dieser sehr schnell bei Dateien mit mehr als teilweise nur 1000 Zeilen komplett überfordert und konnte nur CSV Dateien verarbeiten. 
 +
 +In einer 5 APEX Umgebung daher mit eigenen Mitteln diese Aufgaben umständlich umgesetzt.
 +
 +  * Daten in die DB über APEX laden =>  [[prog:oracle_apex_5_file_handling|Oracle APEX 5 -  Dateien laden und parsen]]
 +
 +  * Daten wieder auslesen =>  [[prog:plsql_spool_csv_apex|Oracle PL/SQL - APEX - CSV Datei aus der DB mit DBMS_XMLGEN.CONVERT erzeugen]]
 + 
 +
 +<fc #008080>**Lösung ab 19.1:**</fc>
 +
 +
 +Ab 19.1 kann mit dem APEX APEX_DATA_PARSER das ganze viel einfacher und vor allem auch ohne Plugins für natives Excel umgesetzt werden.
 + 
 +
 +=> API => https://docs.oracle.com/en/database/oracle/application-express/19.2/aeapi/APEX_DATA_PARSER.html
 +
 +
 +
 +Hierzu nun ein paar Anmerkungen mit den wichtigsten SQL Statements für diese Aufgabe.
 +
 +----
 +
 +==== Ablauf ====
 +
 +
 +  * Formular um eine CSV / Excel Datei in eine Tabelle zu laden
 +  * Den Inhalt der Datei nach dem Laden auf der Seite anzeigen
 +  * Laden der Daten aus der Datei in die passende Datenbank Tabelle
 +  * Anzeige der erfolgreich geladenen Daten und der fehlerhaften Daten
 +
 +
 +
 +----
 +
 +==== Laden der Datei in eine eigene Stage Tabelle ====
 +
 +
 +Die Stage Tabelle ist in diesem Projekt fachlich notwendig um die Dateien gleichzeitig zu archivieren.
 +
 +Alternativ kann natürlich einfach die Datei per Standard APEX Verhalten in die APEX_APPLICATION_TEMP_FILES geladen und dann vor dort ausgelesen werden.
 +
 +
 +=== Stage Tabelle in der DB anlegen ===
 +
 +
 +Eine paassende Tabelle für den Datei Upload in der Datenbank anlegen, wie:
 +
 +<code sql>
 +
 +create table T_IMPORT_FILES 
 +  (  
 +    DDE_SK                 number(15) NOT NULL 
 +  , NAME                   VARCHAR2(400)  NOT NULL
 +  , FILENAME               VARCHAR2(400) 
 +  , MIME_TYPE              VARCHAR2(255) 
 +  , CREATED_ON             TIMESTAMP(6) default systimestamp          
 +  , BLOB_CONTENT           BLOB      
 +  --
 +  , SETTINGS_DEF_BY_USER   VARCHAR2(32 CHAR)      DEFAULT NVL(nvl(SYS_CONTEXT('APEX$SESSION','APP_USER'),SYS_CONTEXT('USERENV','PROXY_USER')),user)
 +  , CREATED_AT             TIMESTAMP(6)           DEFAULT systimestamp
 +  , SETTINGS_CHAN_BY_USER  VARCHAR2(32 CHAR)      DEFAULT NVL(nvl(SYS_CONTEXT('APEX$SESSION','APP_USER'),SYS_CONTEXT('USERENV','PROXY_USER')),user)
 +  , EDIT_AT                TIMESTAMP(6)           DEFAULT systimestamp
 +  , CONSTRAINT T_KDE_EXCLUDE_IMPORT_PK PRIMARY KEY (
 + DDE_SK 
 +   )
 +  ENABLE
 +)  
 +</code>
 +
 +
 +In APEX 
 +
 +
 +=== APEX Maske zum Laden der Daten ===
 +
 +
 +  * In APEX ein ITEM vom Typ "File Browser" anlegen
 +    * Es kann auch gleich die passende Tabelle hier angeben werden, dann kann ein eigener Prozess da für entfallen.
 +  * Button um den Lade Prozess zu starten
 +
 +Hier ein Beispiel für ein Prozess um Datei in eigene Temporäre Tabelle laden, falls mehr Logik notwendig ist als der APEX Default liefert:
 +
 +<code plsql>
 +
 +declare
 +
 +  v_file_count pls_integer:=0;
 +
 +  v_file blob;
 +
 +  v_message varchar2(4000):='Prozessing File Insert'||'<br/>';
 + 
 +  v_filename varchar2(512);
 +
 +  v_id number;
 +  
 +begin 
 +    if :P3400_IMPORT_FILE is not null then
 +    
 +    --- Put your own logic in here
 +   
 +     v_filename:=:P3400_IMPORT_FILE;
 +    
 +     v_message:= v_message||' Name of P3400_IMPORT_FILE is '||v_filename||'<br/>';
 +
 +        select count(ID) into v_file_count
 +            from apex_application_temp_files
 +            where name = v_filename;
 +
 +        if v_file_count > 0  then
 +              for rec in (select
 +                    id,
 +                    application_id,
 +                    name,
 +                    filename,
 +                    mime_type,
 +                    created_on,
 +                    blob_content
 +                from apex_application_temp_files where name = v_filename ) loop               
 +                   
 +                      v_message:= v_message||' Read File  with filename '||rec.filename||'<br/>'; 
 +                       insert into T_IMPORT_FILES (
 +                            DDE_SK       
 +                          , NAME         
 +                          , FILENAME     
 +                          , MIME_TYPE    
 +                          , CREATED_ON   
 +                          , BLOB_CONTENT 
 +                          , SETTINGS_DEF_BY_USER
 +                          , CREATED_AT
 +                       ) 
 +                        values (  T_IMPORT_FILE_seq.nextval -- DDE_SK
 +                                , rec.NAME                                -- NAME
 +                                , rec.FILENAME                            -- FILENAME
 +                                , rec.mime_type                           -- MIME_TYPE
 +                                , rec.CREATED_ON                          -- CREATED_ON                            
 +                                , rec.blob_content                        -- BLOB_CONTENT
 +                                , :APP_USER                               -- SETTINGS_DEF_BY_USER
 +                                , systimestamp)                           -- CREATED_AT
 +                        RETURNING DDE_SK INTO v_id;        
 +                end loop; 
 +                
 +                -- clean the tempfile
 +                begin
 +                    delete apex_application_temp_files  where name = v_filename;
 +                    commit;
 +                EXCEPTION    
 +                    when others then
 +                       v_message:=v_message||' clean tempfile '|| SQLCODE || ' - '||SQLERRM||'<br/>';    
 +                 end;
 +        end if;
 +    end if; 
 +    
 +    apex_application.g_print_success_message := '<span style="color:yellow">'|| v_message || '</span>';
 +end;
 +</code>
 +
 +
 +----
 +
 +
 +==== Daten mit dem Parser auslesen =====
 +
 +Eine Report auf der Seite anlegen und mit Hilfe von **apex_data_parser.parse** die Datei parsen und anzeigen.
 +
 +=== Das passende Sheet aus der Excel Datei auslesen mit apex_data_parser.get_xlsx_worksheets ===
 +
 +In Excel kann sogar das einzelne Sheet selektiert werden aus dem die Daten geladen werden sollen!
 +
 +<code sql>
 +select sheet_display_name, sheet_file_name
 +  from T_IMPORT_FILES f,
 +       table( apex_data_parser.get_xlsx_worksheets( p_content => blob_content ) ) p
 + where f.name = :P3400_IMPORT_FILE
 +</code>
 +
 +
 +=== Bericht anlegen mit apex_data_parser.parse ===
 +
 +SQL für den Report
 +
 +<code sql>
 +
 +select  apex_item.checkbox2(2,line_number) import  -- only if you like to implement a filter for the imported data
 +    ,  col001
 +    ,  col002
 +    ,  col003
 +    ,  col004
 +    , col005
 +    , col006
 +     -- .. up to 300 columns can be selected
 +    , col300
 + 
 +  from T_IMPORT_FILES f, 
 +       table( apex_data_parser.parse(
 +                  p_content                     => f.blob_content,
 +                  p_add_headers_row             => 'Y',
 +                  p_xlsx_sheet_name             => :P3400_XLSX_WORKSHEET,
 +                  p_max_rows                    => 1000,
 +                  p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
 +                  p_file_name                   => f.filename ) ) p
 + where f.name = nvl(:P3400_IMPORT_FILE,:P3400_LAST_IMPORTS)
 +and line_number > 1
 +</code>
 +
 +Beispiel für eine reine CSV Datei:
 +
 +<code sql>
 +SELECT col001
 +    ,  col002
 +    ,  col003
 +    ,  col004 
 +  FROM apex_application_temp_files f, 
 +       TABLE( apex_data_parser.parse(
 +                  p_content                     => f.blob_content,
 +                  p_add_headers_row             => 'Y',
 +                  p_csv_col_delimiter             => ' ',      -- hier tab!           
 +                  p_file_name                     => replace(f.filename,'.tab','.csv') -- muss csv heißen!
 +           )
 +       ) p
 + WHERE f.name = :P400_LOADED_FILE
 +
 +</code>
 +
 +Die Dateiendung ist wichtig für den Parser! 
 +
 +
 +----
 +
 +
 +=== Daten aus der Datei in die Zieltabelle laden ===
 +
 +
 +== Error Log ==
 +
 +Error Log zuvor auf der Ziel-Tabelle anlegen um Fehler mitschreiben zu können.
 +
 +<code sql>
 +-- +====================================================+
 +-- Error Log table
 + 
 +BEGIN
 +  DBMS_ERRLOG.create_error_log (dml_table_name => 'T_EXCLUDE_KDE');
 +END;
 +/
 +</code>
 +
 +=== Daten laden mit  apex_data_parser.parse ===
 +
 +Prozess für den Übertrag in die Zieltabelle:
 +<code sql>
 +
 +declare
 +    v_error_log_exists boolean := false;
 +    v_message varchar2(4000):='Prozessing Data submit to DDE '||'<br/>';
 +begin
 +  
 +    delete from ERR$_T_CSV_DETAILS where ORA_ERR_TAG$ like :APP_USER||'%';
 +    commit;    
 +    
 +    
 +    
 +    insert into T_CSV_DETAILS (
 +                DDE_SK                
 +              , KUNDENNUMMER           
 +              , KUNDENNUMMER_2
 +   , INSTITUTSZUORDNUNG   
 +   , CASENUMBER           
 +   , TRIGGER_TYPE  
 +              , INSERT_AT          
 +   , INSERT_REASON      
 +   , IS_ACTIVE         
 +    ) select  T_CSV_DETAILS_seq.nextval
 +            , col002 as KUNDENNUMMER
 +            , col003 as KUNDENNUMMER_2
 +            , col001 as INSTITUTSZUORDNUNG
 +            , col002as CASENUMBER
 +            , col006 as  TRIGGER_TYPE   
 +            , to_date(col005,'yyyy-mm-dd') as INSERT_AT
 +            , col007 as INSERT_REASON
 +            , 'Y'    as IS_ACTIVE
 +      from T_IMPORT_FILES f, 
 +           table( apex_data_parser.parse(
 +                      p_content                     => f.blob_content,
 +                      p_add_headers_row             => 'Y',
 +                      p_xlsx_sheet_name             => :P3400_XLSX_WORKSHEET,
 +                      p_max_rows                    => 1000,
 +                      p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
 +                      p_file_name                   => f.filename ) ) p
 +     where f.name = :P3400_IMPORTED_FILE
 +    and line_number > 1  -- not the first line of the dokument!
 +    log errors into ERR$_T_CSV_DETAILS ( :APP_USER|| ' - ' ||' von ' || to_char(sysdate,'dd.mm.yyyy hh24:mi') ||' - '||:P3400_LOADED_FILE )
 +    reject limit unlimited;
 +   
 +   :P3500_ROWS_LOADED := sql%rowcount;
 +   
 +   select count(*) 
 +     into :P3400_ROWS_FAILED
 +     from ERR$_T_CSV_DETAILS where ORA_ERR_TAG$ like :APP_USER||'%';
 +     
 +   v_message:='Load '||:P3400_ROWS_LOADED||' Records to T_CSV_DETAILS with '||:P3400_ROWS_FAILED||' Errors';
 +
 +   apex_application.g_print_success_message := '<span style="color:yellow">'|| v_message || '</span>';
 +  
 +end;
 +
 +
 +</code>
 +
 +Beispiel für das Laden einer reinen CSV Datei:
 +
 +<code sql>
 +declare
 +    v_message varchar2(4000):='Merge der Modbus Daten in BMA_MODBUS'||'<br/>';
 +begin
 +  
 +  delete from ERR$_BMA_MODBUS where ORA_ERR_TAG$ like :APP_USER||'%';
 +  commit;
 +           
 +  merge into BMA_MODBUS m 
 +         using ( select 
 +                 col001 as TYP
 +               , col002 as NUMMER
 +               , col003 as SUB_NUMMER
 +               , col004 as register
 +       FROM apex_application_temp_files f, 
 +         TABLE( apex_data_parser.parse(
 +                  p_content               => f.blob_content,
 +                  p_add_headers_row       => 'Y',
 +                  p_csv_col_delimiter     => ' ',                
 +                  p_file_name             =>  replace(f.filename,'.tab','.csv')  ) 
 +              ) p
 +         WHERE f.name = :P400_LOADED_FILE           
 +         )  i 
 +         on ( i.TYP=m.typ and i.NUMMER=m.NUMMER and nvl(i.SUB_NUMMER,-1)=nvl(m.SUB_NUMMER,-1)  )        
 +     WHEN matched
 +    THEN
 +        update set m.register=i.register
 +     WHEN NOT matched
 +    THEN
 +       insert ( BMA_MODBUS_ID
 +            , TYP
 +            , NUMMER
 +            , SUB_NUMMER
 +            , REGISTER 
 +        ) values ( 
 +            BMA_MODBUS_seq.nextval
 +          , i.TYP
 +          , i.NUMMER
 +          , i.SUB_NUMMER
 +          , i.register       
 +        )
 +    log errors into ERR$_BMA_MODBUS ( :APP_USER|| ' - ' ||' von ' || to_char(sysdate,'dd.mm.yyyy hh24:mi') ||' - '||:P400_LOADED_FILE )
 +    reject limit unlimited;
 +   
 +   :P400_ROWS_LOADED := sql%rowcount;
 +   
 +   commit;
 +   
 +   select count(*) 
 +     into :P400_ROWS_FAILED
 +     from ERR$_BMA_MODBUS where  ORA_ERR_TAG$ like :APP_USER||'%';
 +     
 +   v_message:=v_message||'Konnte '||:P400_ROWS_LOADED||' Zeilen in  BMA_MODBUS mit '||:P400_ROWS_FAILED||' Fehlern laden';
 +
 +   :P400_LOADED_FILE:=null;
 +
 +
 +   apex_application.g_print_success_message := '<span style="color:yellow">'|| v_message || '</span>';
 +  
 +   
 +end;
 +
 +
 +</code>
 +
 +----
 +
 +----
 +
 +
 +==== Quellen ====
 +
 +Carsten Czarski
 +
 +
 +  * https://blogs.oracle.com/apex/quick-and-easy-data-loading-with-apex-191
 +  * https://blogs.oracle.com/apex/super-easy-csv-xlsx-json-or-xml-parsing-about-the-apex_data_parser-package
 +
 +Web:
 +
 +
 +  * https://explorer.co.uk/loading-excel-into-apex-19-1/
 +  * https://www.jmjcloud.com/blog/apex-data-parser-data-load-made-easy
prog/apex_datei_parser_laden.txt · Zuletzt geändert: 2019/11/14 11:51 von gpipperr