Import csv 1

[ManjulaMadhu]

Is there any way to import a csv file into the Oracle 9i database without DBA studio or SQL Loader ?

 

[AOLEURO]

Hi

Yes - use CREATE TABLE xxxxx .......ORGANIZATION EXTERNAL.


It is of cause a READ ONLY table, but you can make select statements and Oracle read from flat file.

ex.

CREATE TABLE AOL_EURO_EXT ( Idd number,
                            text char(20)
                          )
ORGANIZATION EXTERNAL (  TYPE ORACLE_LOADER
                         DEFAULT DIRECTORY csv_dir
                         ACCESS PARAMETERS(  RECORD DELIMITED BY NEWLINE
                                             FIELDS TERMINATED BY ','
                                             BADFILE 'bad.txt'
                                             LOGFILE 'log.txt'
                                             MISSING FIELD ARE NULL)
                          LOCATION ('AOLEURO1.CSV')
REJECT LIMIT UNLIMITED;

select * from AOL_EURO_EXT;

Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[carp]

You can also create a script that uses utl_file to read in a record from the table, parse it into columns and insert the values. But sqlLoader would be a lot easier and more efficient.

 

[AOLEURO]

Hi Carp

No - table created as external you can select multi times from the file as a table insight Oracle - it's fast it's easy and gives you control over the file as a table.

Oracle have created this feature on 9i to avoid that you first load the data into a raw table with sql_loader and then query the table to build/insert data in other tables. So Oracle created external table as a answer to this "problem" - you now just select from the "table" and do you job on the other tables.

Performance from SQL_loader and external table is the same according til Oracle tests.

SQL_loader is used for one-time only jobs and external tables for repeated jobs.

The parameters on external tables def. is the same as for SQL_LOADER. Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[ManjulaMadhu]

Thanks AOLEURO and carp.

 

[carp]

Allan - everything you say is absolutely right - which is fine, provided you only want to select on the table (as you mentioned in your first post).

However, if you want to import the data to populate a table on which you will perform subsequent DML, an external table will be of limited use, won't it? In that case, you would need to do something else - right?

Since the original post did not mention a desire for a read-only table (nor did it exclude it), the poster now has two solutions to choose from.

 

[carp]

Another thought - while the performance between SQL*Loader and external tables is identical, the problem I see with an external table is that you have to load it every time you use it. If your file is static, I would think it better to just load it once (especially if you access it frequently).

I find the performance on external tables is good, but I find them to be of most use when accessing dynamic files (e.g., Apache logs, etc). If I had one file that didn't change and I referenced the data on any regular basis, I don't think I would bother with an external table.

This is not to denegrate external tables - I think they're great! - it's just to give the poster food for thought when trying to pick the right tool for the job.

 

[AOLEURO]

Hi Carp

Sorry the delay

I use EXTERNAL TABLE to read new data into databases.

Ex.

Every day we receive payment file from bank with payments from costumers. It is a flat csv file downloaded each morning automatic from central bank system.

The file is saved in an INBOX folder and with the same name every day.

A script running in JOB automatic reads the information from the file (external table) and we update customer tables with account information like this:

Insert into customer_payment( customeridd, payday, amount, currency, BankPayIdd )
  select Customeridd, day, amount_curr, currency, BankRef from Upload_BankPayFile;

ManjulaMadnu ask for something else than DBA studio and SQL loader. The only reasonable solution on Oracle 9i is CREATE TABLE …ORGANIZATION EXTERNAL because it comes nearest sql_loader and is far easier than UTL_FILE.

On older versions of oracle – it is UTL_FILE package just as you wrote in your first post. Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[carp]

We agree right down the line! If all that is being done is selects then an external table is a perfect answer. Some would wonder if running the equivilent of sql*Loader every time you want to run a query is inefficient. But Oracle's studies show that an external table compares very favorably with selecting from a table.