Looking for info on importing a .CSV file into a database.

[snowcold]

I just received an inquery from a customer that has a gauge that produces a .csv file and they were wondering we could take this file and dump it into an oracle database.
After some research, I saw that I may need a sql loader tool to accomplish this.
I wondering if anyone could provide some insight on this topic.



thanks!

 

[SantaMufasa]

Snow,

If you have either Oracle client or server installed, then you should also have the executables for SQL*Loader. Given that, there is an even easier way to accomplish your objective: EXTERNAL TABLES. External table definitions allow you to access flat files (e.g., *.csv files) directly, without going through an explicit, separate SQL*Loader step. Although External tables use the SQL*Loader executables, that usage is transparent to you, the accessor.

Following is a proof of concept depicting the definition and direct access of a flat .csv file (for a Real Estate application) as an Oracle table:

create directory ALMFlatFiles as 'd:\dhunt\sqldba\ALM'
/
create table ALM2_Properties_DDP
	(OWNER_LST_NAM		varchar2(40)
	,OWNER_NAM		varchar2(40)
	,C_OWNERS		varchar2(100)
	,C_FMT_OWN		varchar2(25)
	,C_ADDRESS		varchar2(40)
	,C_CITYSTA		varchar2(50)
	,FIRST_MTG_AMT		number
	,FIRST_MTG_TYPE		varchar2(20)
	,FIRST_MTG_CLASS	varchar2(20)
	,FIRST_MTG_INT		number
	,LENDER_NAME		varchar2(40)
	,MAILING_ADDR		varchar2(40)
	,MAILING_CITY		varchar2(40)
	,MAILING_ZIP		varchar2(10)
	,MAILING_CODE		varchar2(10)
	,IRIS_LAND		varchar2(25)
	,APN			varchar2(25)
	,LOT_SIZE_ACRE		number
	,DOCUMENT_ID		varchar2(15)
	,RECORDING_DT		varchar2(10)
	,VALUE_TOT		number
	,YEAR_BUILT		number
	,CONDO_NAME		varchar2(40)
	,NO_OF_BUI		number
	,TOTAL_LIVING_SQFT	number
	)
organization external
(  type oracle_loader
   default directory ALMFlatFiles
   access parameters
   (records delimited by newline
    fields terminated by ','
   )
location ('DDPW_Properties.txt')
)
reject limit unlimited;

Table created.

SQL> desc ALM2_Properties_DDP
Name                    Null?    Type
----------------------- -------- -------------
OWNER_LST_NAM                    VARCHAR2(40)
OWNER_NAM                        VARCHAR2(40)
C_OWNERS                         VARCHAR2(100)
C_FMT_OWN                        VARCHAR2(25)
C_ADDRESS                        VARCHAR2(40)
C_CITYSTA                        VARCHAR2(50)
FIRST_MTG_AMT                    NUMBER
FIRST_MTG_TYPE                   VARCHAR2(20)
FIRST_MTG_CLASS                  VARCHAR2(20)
FIRST_MTG_INT                    NUMBER
LENDER_NAME                      VARCHAR2(40)
MAILING_ADDR                     VARCHAR2(40)
MAILING_CITY                     VARCHAR2(40)
MAILING_ZIP                      VARCHAR2(10)
MAILING_CODE                     VARCHAR2(10)
IRIS_LAND                        VARCHAR2(25)
APN                              VARCHAR2(25)
LOT_SIZE_ACRE                    NUMBER
DOCUMENT_ID                      VARCHAR2(15)
RECORDING_DT                     VARCHAR2(10)
VALUE_TOT                        NUMBER
YEAR_BUILT                       NUMBER
CONDO_NAME                       VARCHAR2(40)
NO_OF_BUI                        NUMBER
TOTAL_LIVING_SQFT                NUMBER

SQL> col c_owners format a30
SQL> col c_citysta format a15
C_OWNERS                       C_CITYSTA
------------------------------ ---------------
Hunt David L & Nancy L         Sandy Ut 84092

Let us know if this meets your satisfaction.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[snowcold]

THanks for the reply.

Let me play around with this and I'll post back.

Thanks again!