When I first started using Oracle many years ago one of the most frustrating things was the amount of code and complexity required to parse/query CSV data (or any delimited data). As time passed I was able to leverage new features and techniques to help but they all had their issues. APEX 5.1 introduced a new API that may help simplify a lot of the headaches. The following example highlights this.
Suppose I had the following CSV data:
First Name, Last Name, Dept
The first thing to do is break each of the rows of text into rows of a query. This can be done using the new APEX 5.1
:data is the CSV data above. Note I’m using a Mac and the EOL character is a
chr(10)). Windows users use
CR + LF (
chr(13) || chr(10)). More info on this here
Now that each line of data is on it’s own row we need to create columns. This can be done using regular expressions:
Update: originally I used
regexp_substr(column_value, '[^,]+', 1, 1) as the regexp which caused issues with
null values (ex
abc,,def). I’ve since updated the code samples to handle nulls.
To remove the header row change the query to:
select fname, lname, dept