Re: Convert CSV data held in a CLOB into columns

In article <uzlwk5sum.fsf_at_rcn.com>, Galen Boyer says...
> On Sat, 8 Dec 2007, jeremy0505_at_gmail.com wrote:
>
> > Alternatively, might we take the CLOB content, write it out via
> > UTL_FILE then refer to it as an external table?
> >
> > As I said, I would certainly like to be able to do all the work
> > without any depdency on referencing files on the o/s.
>
> You most certainly could do this, but you would have to reference files
> on the OS, but maybe, you don't mind referencing files on the OS through
> PL/SQL?
>

If it's necessary then we'll do it. The app can control where the files are written, how they are named etc.

> But, it sounds as though the files are different, ie, you would need a
> dynamic process to get them in, get the first record, create an external
> table from it and then load the file, or something like that? If this
> is the case, I don't get the final mapping of the data. Lets say you
> dynamically create an external table to reference a file, each time you
> recieve a new one. Those columns would mean something? Where do you
> map those columns to your final data-model?
>
> I'd think you would have a set format for your CSV files, define
> external tables before hand, and the only thing you would do at runtime
> is accept a clob and write it out to a particular filename in the
> particular directory and then select from the external table.
>

This is along the lines I was thinking. The ability to make it "dynamic" in terms of the columns provided in the CSV is not such an issue - the CSV would be of a particular type - e.g. PERSON DATA - and sure we have a fixed data model for that in the application. We could allow the user to define a spreadsheet with the only the columns they wish to include and, as long as there is a corresponding column in the target table, then that column's data would be loaded. Of course we would consider data type, size etc. to ensure compliance before loading into final app table. We could also provide a log table of any data issues arising, columns that couldn't be mapped etc.

Fot total flexibility (say where we are loading data from a 3rd party system where we have no control over the column layout), we could allow a mapping table to transalte source columns to target columns.

It may sound as though we are trying to re-invent sqlloader - and in a very limited way I guess this is true. But it is important to have the complete process managed within the application. - no going off to the server to run sqlloader or ftp an excel file in.

-- 
jeremy