Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:apex_datei_parser_laden

Oracle APEX 19.2 - APEX_DATA_PARSER für das Laden von Excel und CSV Datein einsetzen

Aufgabe:

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.

Lösung ab 19.1:

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.

APIhttps://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:

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
)  

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:

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;

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!

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

Bericht anlegen mit apex_data_parser.parse

SQL für den Report

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

Beispiel für eine reine CSV Datei:

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

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.

-- +====================================================+	
-- Error Log table
 
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'T_EXCLUDE_KDE');
END;
/

Daten laden mit apex_data_parser.parse

Prozess für den Übertrag in die Zieltabelle:

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;

Beispiel für das Laden einer reinen CSV Datei:

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;


Quellen

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
prog/apex_datei_parser_laden.txt · Zuletzt geändert: 2019/11/14 11:51 von gpipperr