Code: Select all
-- The APEX_DATA_PARSER.PARSE function allows to "select" up to 300 columns (COL001 to COL300) from the
-- parsed file. This example uses the following parameters.
--
-- P_CONTENT the file content to be parsed as a BLOB
-- P_FILE_NAME the name of the file; only used to derive the file type.
-- P_ADD_HEADERS_ROW add the detected attribute names for XML or JSON files as the first row
-- P_XLSX_SHEET_NAME For XLSX workbooks. The name of the worksheet to parse. If omitted or NULL, the function will
-- use the first worksheet found.
--
select line_number, col001, col002, col003, col004, col005, col006, col007, col008, col009, col010
-- more columns (col011 to col300) can be selected here.
from apex_application_temp_files f,
table( apex_data_parser.parse(
p_content => f.blob_content,
p_add_headers_row => 'Y',
p_xlsx_sheet_name => :P31_XLSX_WORKSHEET,
p_max_rows => 500,
p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
p_file_name => f.filename ) ) p
where f.name = :P31_FILE
Code: Select all
select column_position, column_name, data_type, format_mask
from apex_collections c,
table( apex_data_parser.get_columns( p_profile => c.clob001 ) )
where c.collection_name = 'FILE_PARSER_COLLECTION'
and c.seq_id = 1
Code: Select all
lower( :P31_FILE ) like '%.xlsx' or
lower( :P31_FILE ) like '%.xml' or
lower( :P31_FILE ) like '%.csv' or
lower( :P31_FILE ) like '%.txt' or
lower( :P31_FILE ) like '%.js' or
lower( :P31_FILE ) like '%.json' or
lower( :P31_FILE ) like '%.geojson'
Code: Select all
INSERT INTO EBA_DEMO_LOAD_EMP (empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno)
(SELECT col001,
col002,
col003,
col004,
TO_DATE (col005, 'mm/dd/yyyy'),
col006,
col007,
col008
FROM apex_application_temp_files f, apex_data_parser.parse (
p_content => f.blob_content,
p_skip_rows => 1,
p_file_type => apex_data_parser.c_file_type_csv )
where f.name = :P31_FILE
)
Code: Select all
select sheet_display_name, sheet_file_name
from apex_application_temp_files f,
table( apex_data_parser.get_xlsx_worksheets( p_content => blob_content ) ) p
where f.name = :P31_FILE