Re: Convert CSV data held in a CLOB into columns

On Dec 7, 2:05 pm, jeremy <jeremy0..._at_gmail.com> wrote:
> Oracle 10gR2
>
> Would like to be able to support upload of a CSV file and then populate
> a "staging" table with its contents.
>
> - use first line of file as column names
> - load lines 2 to end of file as rows into a table, populating only
> those columns identified in the header row
>
> The thought was we could load the file into a BLOB (I should mention
> we're using OHS / mod_plsql so this is standard file upload)), convert
> it to CLOB and then (somehow) parse the content.
>
> Antbody any pointers to examples where this has been done before?
>
> Other suggestions? Have thought about external tables - perhaps that is
> a better approach?
>
> --
> jeremy

Jeremy,
I have written a csv parser from a clob before. Basically iterating the clob by the appropriate eol/cr/lf character. In my case, I placed each line's data into an array of varchar data elements. The array was then dealt with in its context of the appropriate table or tables.

Things requiring more thought were whether data elements had text enclosing characters (such as ", as in: 123.00,"Example, comma in the middle of text", More text,...) and figuring out what the EOL character(s) were.

Vince Received on Mon Dec 10 2007 - 10:42:42 CST