CODEWITHSUNDEEP
oracle-databasecsvplsql
Tom
Tom

Reputation: 81

Oracle PL/SQL package/procedure for processing CSV from CLOB

I am allowing a user to upload a .csv file via OHS/mod_plsql. This file gets saved as a BLOB file in the database, which I then convert to CLOB. I want to then take the contents of this CLOB file and save its contents into a table. I already have some working code that splits the file based on line endings, then splits each resulting string along commas and inserts the records.

What I need is a way to handle the case where a string within the CSV is enclosed in double-quotes and contains a comma. For example:

col1,col2,col3,col4
some,text,more,text
this,text,has,"commas, semicolons, and periods"

My code will know how to process the second line, but not the third. Does anyone have some code that is smart enough to treat "commas, semicolons, and periods" as a single token? I could probably hack something together, but I don't trust my regular expression skills enough, and I figure someone else has probably already written something that does this same thing that would be willing to share.

Upvotes: 1

Views: 9740

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

There's a good CSV parser in the Alexandria PL/SQL Library - CSV_UTIL_PKG.

https://code.google.com/p/plsql-utils/

More info:

http://ora-00001.blogspot.com.au/2010/04/select-from-spreadsheet-or-how-to-parse.html

Upvotes: 2

Related Questions