Hi Expert,
Im using APEX21.1, and I have to realize an application which allows to load data dynamically (via a configuration) from a csv or excel file, in a HCM database.
My design is composed of 2 parts:
1. The setup(configuration) part, with 3 tables:
- UPL_CUSTOMERS_TBL(CUSTOMER_ROWID, CUSTOMER_NAME, SEPARATOR, CHARSET): contains the list of customers
Example:[ 1, 'ORACLE' , ';' , 'UTF-8' ]
- UPL_CUSTOMERS_FILES_TBL(CUSTOMERS_FILES_ROWID, CUSTOMER_NAME, FILENAME, FILENAME_PATTERN, NB_FIELDS): Corresponds to the list
of files of a customer,
Example: [ 1, 'ORACLE' , 'PERSONAL_DATA' , '^[a-zA-Z]+_\[([0-9]+(-[0-9]+)+)\]\.[a-zA-Z]+$' , 5 ]
- UPL_CUSTOMERS_FILES_FIELDS_TBL(CUSTOMERS_FILES_FIELDS_ROWID, CUSTOMER_NAME, FILENAME, FIELD_NAME, FIELD_POSITION, FIELD_TYPE, FIELD_MANDATORY):
Allows you to define the header of the file to be loaded, specifying the list of columns of a customers file
Example: [ 1, 'ORACLE' , 'PERSONAL_DATA' , 'FIRST_NAME', 1, 'VARCHAR2', 'Y']
[ 2, 'ORACLE' , 'PERSONAL_DATA' , 'LAST_NAME' , 2, 'VARCHAR2', 'Y']
[ 3, 'ORACLE' , 'PERSONAL_DATA' , 'BIRTH_DATE', 3, 'DATE' , 'N']
[ 4, 'ORACLE' , 'PERSONAL_DATA' , 'HIRE_DATE' , 4, 'DATE' , 'Y']
[ 5, 'ORACLE' , 'PERSONAL_DATA' , 'ADDRESS' , 5, 'VARCHAR2', 'N']
2. The verification part, with 2 tables:
- UPL_LOADED_FILES_TBL(LOADED_FILES_ROWID, REQUEST_ROWID, CUSTOMER_NAME, FILENAME, LOADED_FILENAME, SEPARATOR, CHARSET, TOTAL_COLUMNS, COLUMN_NAME, COLUMN_POSITION, CLOB_CONTENT): Represents a temporary table that will contain the information of the file loaded by the user
Example: The header of csv file: PersonalOracle_[2021-07-01].csv
FIRST_NAME ; LAST_NAME ; BIRTH_DATE ; HIRE_DATE ; ADDRESS
[ REQ_1, 'ORACLE' , 'PERSONAL_DATA' , ';', 'UTF-8', 6, 'FIRST_NAME',1]
[ REQ_1, 'ORACLE' , 'PERSONAL_DATA' , ';', 'UTF-8', 6, 'LAST_NAME' ,2]
[ REQ_1, 'ORACLE' , 'PERSONAL_DATA' , ';', 'UTF-8', 6, 'BIRTH_DATE',3]
[ REQ_1, 'ORACLE' , 'PERSONAL_DATA' , ';', 'UTF-8', 6, 'HIRE_DATE' ,4]
[ REQ_1, 'ORACLE' , 'PERSONAL_DATA' , ';', 'UTF-8', 6, 'ADDRESS' ,5]
- UPL_DATA_TBL (REQUEST_ROWID, CUSTOMER_NAME, FILENAME,FIELD01, FIELD02, FIELD03 ... FIELD60): which will contain the data of the loaded file
Example: [ REQ_1, 'ORACLE' , 'PERSONAL_DATA' , 'Soukaina', 'IDRISSI', 07/07/1997 , 02/01/2020 , '25 STREET JULIETTE DODU, PARIS']
[ REQ_1, 'ORACLE' , 'PERSONAL_DATA' , 'Mahmoud' , 'RABIE' , 05/11/1980 , 01/09/2007 , '7 STREET SOFIA ZAGHLOUL, CAIRO']
[ REQ_1, 'ORACLE' , 'PERSONAL_DATA' , 'Carsten' , 'CZARSKI', 27/03/1986 , 01/05/2000 , '86 BRIENNER STRABE, MUNICH']
So, once the configuration is done, I need to load a file that responds to the configuration. And before each data loading,
I have to do some initial checks, such as:
- The name of the loaded file
- The separator and charset of the loaded file
- The number and name as well as the order of each column passed in the loaded file
My Issue:
As of now, I can get a clob with json content, once the file is loaded, but I can't parse the json into my temporary table LOADED_FILES_TBL.
I followed the article of Carsten Czarski
https://blogs.oracle.com/apex/super-easy-csv-xlsx-json-or-xml-parsing-about-the-apex_data_parser-package
but i cant get the seperator and charset with this method.
You can see demo: on apex.oracle.com with this credentials:
Workspace: ws_formation
Username: asoukaina
Password: azerty123
I used the Application 33599 - UploadDynamicData / Page 3000 - Data Transfer = 33599:3000
Thank you for help.