1

I'm trying to find the best way to upload, parse and work with text file in Oracle APEX (current version 20.1). Bussiness case: I must upload text file, first line will be saved to table A. Rest lines contains some records (columns are pipe delimited) should be validated. After that correct recordes should be saved to table B or if there is some error it should be saved to table C (error log). I tried to do something with the Data Loading wizard but it doesn't fit to my requirements.

Right now I added a "File browse..." item to page, and after page submit I can find this file in APEX_APPLICATION_TEMP_FILES in blob_content.

Is there any other option to work with that file than working with blob_content from APEX_APPLICATION_TEMP_FILES. I find it difficoult to work with type of data.

Text file look something like that:

2020-06-05 info: header line 
2020-06-05|columnAValue|columnBValue|
2020-06-05|columnAValue||columnCValue
2020-06-05|columnAValue|columnBValue|columnCValue
Improve this question
1
  • I think the data loading wizzard gives you the most options, any other choice will be a lot more work. We have an app where a user can upload a json file. I use apex_application_temp files to store the data and then convert that to clob (google how to do that). Commented Jun 5, 2020 at 10:15

3 Answers 3

Reset to default
2

have a look into the APEX_DATA_PARSER.PARSE table function. It parses the CSV file and returns the values as rows and columns. It's described in more detail within this blog posting: https://blogs.oracle.com/apex/super-easy-csv-xlsx-json-or-xml-parsing-about-the-apex_data_parser-package

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That is perfect, but there is only one "small issue" this file is text file but have weird extension .exp,and APEX_DATA_PARSER.PARSE requires .CSV, .JSON . TXT is there a option to change extension of uploaded filed in blob before giving it as a parameter to APEX_DATA_PARSER.PARSE?
Maybe there is a possiblity to change mime-type of uploaded file?
1

Simply pass "file.csv" (literally) as the p_file_name argument. APEX_DATA_PARSER does not care about the "real" file name....

Improve this answer

Comments

0

The function uses the file extension only to differentiate between delimited, XLSX, XML or JSON files. So simply pass in a static file name like "file.csv". That should be enough.

Improve this answer

1 Comment

I need to avoid force users to change the file extension every time they will upload files. How can I upload file with different extension and force to use it as a .csv?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.