Automating Import From CSV

Hello,
I receive data files in CSV format. This happens fairly frequently and I am looking for an automated way to transfer the data to Oracle 9 tables. My first idea was to write a PL/SQL script to do this. Is this the best approach? Does anyone have CSV parsing functions for PL/SQL? Any other suggestions from your personal experience would be very helpful. Thanks.

You can either use external tables or use SQL*Loader to put them into the database.

SQL*Loader:
http://download-west.oracle.com/doc...art2.htm#436160

External tables example:
http://download-west.oracle.com/doc...73a.htm#2093662

I've used exernal tables.

Thanks for your suggestions. I decided to use external tables. But I am facing a problem. How do I store the Create Table statement in a stored procedure. I get the following error when I have placed it in the begin section:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:begin case declare exit for goto if loop mod null pragma...

Can anyone share with me some code. Thanks

Why create the external table in a stored proc?

I receive the CSV file every month. So I want to call the stored procedure to add the new csv file as an external table and then with a
insert into tablename select * from external table
to transfer the data in the table where i hold them. If there's a better way to do it can you show me? Thanks.

You don't need to re-create the table each time. Create it once, point it to the location of the CVS file, and then in you stored procedure you only need to do the INSERT. The SELECT will return the data from the current file.

Or do you receive a file with the date in the name, like extraction062005.csv?

Yes the name of the file is different.